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 the titles of all the books which have more than 5 units in stock.

Query 2) Show the total price John Smith has paid for the books.

Query 3) Show me the name of the customer who has paid less than $20 in totals.

Suppliers (SupplierID, Company Name, Cont actLastName, ContactFirstName, Phone) Books (BookID. Title, ?nit-Price, Author, Unit-in-Stock, Supplier!D. . SubjectID) Subjects (SubjectID. CategoryName) OrderDetails (BookID. OrderID. Quantity) * * * Orders (OrderID, CustomerID. EmployeeID, OrderDate, ShippedDate, ShipperID) . Employees (EmploveeID, LastName, FirstName) * Shippers (ShipperID.ShpperName)

Explanation / Answer

1) Show the titles of all books which have more than 5 units in stock.

SELECT TITLE FROM BOOKS WHERE UNIT_IN_STOCK > '5';

2) Show the total price John Smith has paid for books.

Assuming John Smith is a Customer and his first name is John smith.

SELECT SUM(B.UNIT_PRICE) FROM CUSTOMERS C, ORDERS O, BOOKS B, ORDERDETAILS OD WHERE C.FIRSTNAME = 'John Smith' AND C.CUSTOMERID = O.CUSTOMERID AND O.ORDERID = OD.ORDERID AND OD.BOOKID = B.BOOKID;

3) Name of customer who has paid less than $20 in totals

SELECT CM.LASTNAME, CM.FIRSTNAME FROM CUSTOMERS CM , ( SELECT SUM(B.UNIT_PRICE) TOTAL, C.CUSTOMERID CUSTID FROM CUSTOMERS C, ORDERS O, BOOKS B, ORDERDETAILS OD WHERE C.CUSTOMERID = O.CUSTOMERID AND O.ORDERID = OD.ORDERID AND OD.BOOKID = B.BOOKID GROUP BY C.CUSTOMERID) INN WHERE INN.CUSTID = CM.CUSTOMERID AND INN.TOTAL < '20';

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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