How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

5. Write a sub query using the ORDERITEMS table that will return

Customer Question

5. Write a sub query using the ORDERITEMS table that will return only the ISBN of the book that has been bought most often. This process will require you to obtain the maximum total number of books ordered (based on ISBN) and then compare that to total books ordered, grouped by ISBN. Remember that sub queries work from the bottom up so the process described here is starting with the second query, not the first (remember this exercise when doing #5 in the lab).
Submitted: 1 year ago.
Category: Programming
Expert:  tejas_speed replied 1 year ago.
Hi Sir, I would like to assist you. Could you please send me a table with little data. You can put fake data and send it to me.
Customer: replied 1 year ago.
select * from orderitems;
ORDER# ITEM# ***** QUANTITY
---------- ---------- ---------- ----------
1000 1(###) ###-#### 1
1001 1(###) ###-#### 1
1001 2(###) ###-#### 1
1002 1(###) ###-#### 2
1003 1(###) ###-#### 1
1003 2(###) ###-#### 1
1003 3(###) ###-#### 1
1004 1(###) ###-#### 2
1005 1(###) ###-#### 1
1006 1(###) ###-#### 1
1007 1(###) ###-#### 3
1007 2(###) ###-#### 1
1007 3(###) ###-#### 1
1007 4(###) ###-#### 1
1008 1(###) ###-#### 2
1009 1(###) ###-#### 1
1009 2(###) ###-#### 1
1010 1(###) ###-#### 4
1011 1(###) ###-#### 1
1012 1(###) ###-#### 1
1012 2(###) ###-#### 2
1012 3(###) ###-#### 1
1012 4(###) ###-#### 1
1013 1(###) ###-#### 1
1014 1(###) ###-#### 2
1015 1(###) ###-#### 1
1016 1(###) ###-#### 1
1017 1(###) ###-#### 2
1018 1(###) ###-#### 1
1018 2(###) ###-#### 1
1019 1(###) ###-#### 1
1020 1(###) ###-#### 1SQL> describe orderitems
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ORDER# NOT NULL NUMBER(4)
ITEM# NOT NULL NUMBER(2)
ISBN VARCHAR2(10)
QUANTITY NUMBER(3)
Customer: replied 1 year ago.
SQL> select count(distinct isbn) "Different Book Count",
2 sum(quantity) "Total Books Ordered" from orderitems;Different Book Count Total Books Ordered
-------------------- -------------------
11 43SQL> select isbn,
2 sum(quantity) "Sold Copies"
3 from orderitems
4 group by isbn;ISBN Sold Copies
---------- ----------- (###) ###-#### 6 (###) ###-#### 1 (###) ###-#### 1 (###) ###-#### 1 (###) ###-#### 3 (###) ###-#### 2 (###) ###-#### 8 (###) ###-#### 5 (###) ###-#### 4 (###) ###-#### 10 (###) ###-#### 211 rows selected.aaaand this is where I am stuck.
Expert:  tejas_speed replied 1 year ago.
You need the query to get the book sold maximum number of times? I mean in above case query should return(###) ###-#### ***** me if i am wrong.
Customer: replied 1 year ago.
That is correct. I need a query with a sub query...i have the query that is grouped by....but not the sub query that selects the one with max books sold...which is(###) ###-####
Expert:  tejas_speed replied 1 year ago.
will this work for you?
Expert:  tejas_speed replied 1 year ago.
Select isbn from(select top 1 * from(select isbn, sum(quantity) as [Sold Copies] from orderitemsgroup by isbn) as MaxBooks)as MaxBooksSold
Customer: replied 1 year ago.
That will work. Thank you
Expert:  tejas_speed replied 1 year ago.
great. Thank you too.
Expert:  tejas_speed replied 1 year ago.
Please accept the answer.
Expert:  tejas_speed replied 1 year ago.
Thank you.
Expert:  tejas_speed replied 1 year ago.
Please let me know if you have any issues in any query in future