Given the following schema, write the SQL statements for the following queries.

Question

Given the following schema, write the SQL statements for the following queries.

Query 1) Show me the book names and their respective quantities for open orders (the orders which have not been shipped) at midnight 10/13/14.

Query 2) Show the names of customers who have ordered multiple (more than 1) books and the corresponding quantities. List the result in the descending quantity.

Query 3) Show the names of customers who have ordered multiple (more than 1) books and their respective telephone numbers.

Explanation / Answer

Query 1) Show me the book names and their respective quantities for open orders (the orders which have not been shipped) at midnight 10/13/14.

SELECT B.TITLE, OD.QUANTITY FROM BOOKS B, ORDERDETAILS OD , ORDERS O WHERE B.BOOKID = OD.BOOKID AND OD.ORDERID = O.ORDERID AND O.ORDERID NOT IN ( SELECT OR.ORDERID FROM ORDERS OR, SHIPPERS S WHERE S.SHIPPERID = OR.SHIPPERID) AND O.ORDERDATE = '13-OCT-2014';

Query 2) Show the names of customers who have ordered multiple (more than 1) books and the corresponding quantities. List the result in the descending quantity.

SELECT C.FIRSTNAME, OD.QUANTITY FROM CUSTOMERS C, ORDERS O, ORDERDETAILS OD, (SELECT COUNT(ORDERID) CNT, CUSTOMERID CID FROM ORDERS GROUP BY CUSTOMERID HAVING COUNT(ORDERID) > 1) INN WHERE C.CUSTOMERID = INN.CID AND O.CUSTOMERID = C.CUSTOMERID AND O.ORDERID = OD.ORDERID ORDER BY OD.QUANTITY DESC;

Query 3) Show the names of customers who have ordered multiple (more than 1) books and their respective telephone numbers.

SELECT C.FIRSTNAME, C.PHONE FROM CUSTOMERS C, (SELECT COUNT(ORDERID) CNT, CUSTOMERID CID FROM ORDERS GROUP BY CUSTOMERID HAVING COUNT(ORDERID) > 1) INN WHERE C.CUSTOMERID = INN.CID;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.