SQL Queries. a) Write a query that will list the publisher(s) with the maximum n
ID: 3843229 • Letter: S
Question
SQL Queries.
a) Write a query that will list the publisher(s) with the maximum number of books published.
b) Write a query that will list the customer(s) who had ordered the maximum number of items (2 copies of the same book will be counted as 2 items).
BOOK table:
PUBLISHER table:
CUSTOMER table:
BOOKORDER table:
Thank you!
BOOK PUBDATE BOOK PUBID BOOK COST BOOK RETAIL BOOK CATEGORY 30.95 FITNESS 1 1059831198 BODYBUILD IN 10 MINUTES A DAY 21/01/01 2 0401140733 REVENGE OF MICKEY 22 FAMILY LIFE 14.2 14/12/01 3 4981341710 BUILDING A CAR WITH TOOTHPICKS 18/03/02 37.8 59.95 CHILDREN 4 8843172113 DATABASE IMPLEMENTATION 04/06/99 31.4 55.95 COMPUTER 5 3437212490 COOKING WITH MUSHROOMS 12.5 28/02/00 19.95 COOKING 6 3957 136468 HOLY GRAIL OF ORACLE 47.25 75.95 COMPUTER 31/12/01 7 1915762492 D COMPUTERS 21/01/01 21 25 COMPUTER 8 9959789321 E-BUSINESS THE EASY WAY 37.9 54.5 COMPUTER 01/03/02 17/07/00 9 2491748320 PAINLESS CHILD-REARING 89.95 FAMILY LIFE 48 28.75 COOKING 10 0299282519 THE WOK WAY TO COOK 11/09/00 19 8.95 CHILDREN 11 8117949391 BIG BEAR AND LITTLE DOVE 5.32 08/11/01 12 0132149871 HOW TO GET FASTER PIZZA 17.85 11/11/02 29.95 SELF HELP 31.95 BUSINESS 13 9247381001 HOW TO MANAGE THE MANAGER 09/05/99 15.4 39.95 LITERATURE 14 2147428890 SHORTEST POEMS 01/05/01 21.85Explanation / Answer
a) Write a query that will list the publisher(s) with the maximum number of books published.
select P.PUB_NAME, max(count(B.BOOK_ISSN)) as total from BOOK B,PUBLISHER P where B.BOOK_PUBID=P.PUB_ID group by P.PUB_NAME;
b)Write a query that will list the customer(s) who had ordered the maximum number of items.
select C.CUST_FNAME, max(count(BO.BO_CUSTNUM)) as total from BOOKORDER BO,CUSTOMER C where BO.BO_CUSTNUM=C.CUSTNUM group by P.CUST_FNAME;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.