I am using Oracle SQL*Plus and trying to do an assignment. I am new to this and this is my first time I am doing anything with SQL.
I am using a BOOK table with **Book code, Title, Publisher Code, Type, Price, and Paperback (Y/N). **I need help in the following functions:
List something only once without repeats.
for example, using a book table, I need to list the book types only ONCE. I do Select type, from book; and it returns me all those 33 books with FIC, HOR, MYS types repeated. Can I just list those three types only once instead of 33 times?
For each type of book, list the type and average price.
Well, I am able to find the average price by using AVG(Price) but when I try to say Select type, AVG(Price), it gives me an error saying it’s not a “single-group group function”.
How many books are paperback type?
How do I find that out? I know in java you can simply say paperback = true; but what about in this thing? I’ve tried to set papertype to true but it doesn’t work. Since the paperback column includes Yes/No (T/F) only, how do I list those books where paperback happens to be true?
You guys are great and thanks for the quick replies :k:
However, I still can’t get that true/false thing right. I’ve tried to set PAPERBACK equal to 1, y, Y, ‘Y’, ‘y’ but none of them is working.
For reference, here’s a descripion of the table book:
Name Type
---------------------------------------
BOOK_CODE CHAR(4)
TITLE CHAR(40)
PUBLISHER_CODE CHAR(3)
TYPE CHAR(3)
PRICE NUMBER(4,2)
PAPERBACK CHAR(1)
This is the exact question and code that I am using:
Question: For each type of book that is PAPERBACK, list the type and the average price.
**I am typing: **
select DISTINCT (TYPE), AVG (PRICE)
from book
where PAPERBACK = 1; (and have tried Y and y as well)
Error
SQL> select DISTINCT (TYPE), AVG(PRICE)
2 from book
3 where PAPERBACK = ‘y’;
select DISTINCT TYPE, AVG(PRICE)
*
ERROR at line 1:
ORA-00937: not a single-group group function
oye shikre, thu computer classes chor de yaar.
U had 3 problems, i answered 2, and then u go mix them up. distinc and paperback=yes was 2 different questions. Why u combining them?
(shhhhh, faizy ne sun liya tho FF kar ke peeche bhage ga. )
select AVG(price), type from book WHERE PAPERBACK = 'Y' group by type
Word of advice...if you are going to be playing around with SQL alot, then make sure to understand all these different basic clauses (distinct, group etc etc) All this crap might seem frustrating in the beginning, but don't loose faith and keep on trying.
I agree with Tofi and Badoosta. You gotta make sure that you understand the basics of SQL correctly. Then it's all easy. I have written SQL-queries in ORACLE that were several pages long. It's not really difficult if you know what you're doing.
Regarding your questions (I will use Oracle standard syntax here):
List something only once without repeats:
SELECT DISTINCT type
FROM book;
For each type of book, list the type and average price:
SELECT type,
AVG(price)
FROM book
GROUP BY type;
For each type of book that is PAPERBACK, list the type and the average price:
SELECT type,
AVG(price)
FROM book
WHERE paperback = 'Y'
GROUP BY type;
According to your table description PAPERBACK is a CHAR(1) column. So I guess that the value would be 'Y' if it is a paperback and 'N' or NULL if it is not a paperback.
You might want to check the actual values of the column first, by using the following query:
hey shikra here is a really nice book for sql i dont know if you have seen it or not its kind of expensive but solves all the problems.
OCA/OCP Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
also the problem that you were have that these guys solved for you was that you were calling a group function and returning a single row and you cant do that. I am also a student in SQL so dont know much at the time
Babey Yaar professor gave several question and every question has something added to it. For example, the first question is:
For each type of book, list the type and the average price
Repeat 1 but consider only paperback books
Repeat 1 and 2 but consider only paperback books for which the avg. price > 10.
and so on
Bandoostaa Thanks. It solved the problem.
Umer bhai Thanks a lot yaar. I guess the only thing I was missing was Group By.
Bandoostaa & Umer bhai Thanks for the suggestions. Although I have to use SQL only for this class, but it’s an interesting language and I plan to learn it just for the heck for it. I did read about all those basic functions and now I seem to understand it. It’ll take a lot of practice before I come anywhere near the amount of expertise you all have in it.
Mansoor Thanks for your suggestion bro
I’ve solved all of the problems except this last one and I am NOT going to get help on it. I will answer this sucker myself and I don’t care how long it takes me
Question 1: For each type of book, list the type and average price.
Simple. I got this one. Select type, avg(price) from book group by type.
Real Question Repeat question 1 but consider only paperback books for those types for which the average price is more than $ 10.
My answer:
SQL> select type, avg(price)
2 from book
3 where paperback = 'y'
4 having avg(price) > 10
5 group by type;
no rows selected
Although it displays no results, but I guess the command is right and it works. It's just that there are no books that are paperback with an average price above $ 10?
Is this correct or is it supposed to be something else?
shikra, you are sql don't go together. Damn man your mixing up and baba's comments are making me laugh. Please leave this job for poor ppl like us(me+if someone else is database developer). Its the easiest thing ever. Neways, good luck buddy, oh yeah last one is rite, why don't you actually try it on the application. and find out if it works, we use to play around with it. (memories sighhhhh)!
PD What do you think I am just playing here typing nonsense? I AM actually working and getting the answers and I know it’s working If you are THAT good on it and find it easy, why don’t you help me then? PD ziada hanso nahi, I see your threads here ALL the times
Edit I ran the last command at home and now it gives me three books
I'm in a hurry right now. So I just skim through your problem.
What I would do first is remove the average price query and just test the results of the ones with papertype. If the results are satisfactory then pass in average price . that way youwill know where you are going wrong...
Change the dataype of papertype to a BIT(0,1) also considered as ( false or true). Set the default value to 0 which means by default the book is not a paper type.
You can say ..... where papertype = 1
I cant really test it here.. can you give some examples of raw data( looking for book types in particular)
DS bhai Yaar first time in my life I had to use SQL and I solved 20 out of 23 problems and you are still not proud of me? So what if I asked for help in 3 problems
)
I hope they were not repeating it, that would suck eh.