Question

Which of the following SQL statements will display the names of all customers who have purchased a copy of E-BUSINESS THE EASY WAY?

a) SELECT CustomerName FROM CUSTOMERS WHERE CustomerID IN (SELECT CustomerID FROM ORDERS WHERE OrderID IN (SELECT OrderID FROM ORDERITEMS WHERE BookID = (SELECT BookID FROM BOOKS WHERE Title = 'E-BUSINESS THE EASY WAY')))
b) SELECT CustomerName FROM CUSTOMERS JOIN ORDERS USING (CustomerID) JOIN ORDERITEMS USING (OrderID) JOIN BOOKS USING (BookID) WHERE Title = 'E-BUSINESS THE EASY WAY'
c) SELECT CustomerName FROM CUSTOMERS WHERE CustomerID IN (SELECT CustomerID FROM ORDERS WHERE OrderID IN (SELECT OrderID FROM ORDERITEMS WHERE BookID IN (SELECT BookID FROM BOOKS WHERE Title = 'E-BUSINESS THE EASY WAY')))
d) SELECT CustomerName FROM CUSTOMERS JOIN ORDERS USING (CustomerID) JOIN ORDERITEMS USING (OrderID) WHERE BookID = (SELECT BookID FROM BOOKS WHERE Title = 'E-BUSINESS THE EASY WAY')

Answer 1

The correct SQL query to display customer names who purchased 'E-BUSINESS THE EASY WAY' utilizes nested subqueries to filter the CustomerName based on the associated BookID for that title.

Step-by-step explanation:

To display the names of customers who have purchased a copy of E-BUSINESS THE EASY WAY, several SQL queries are proposed. The correct statement among them should link the customers to their orders containing the specific book title, and ultimately retrieve the customer names.

The correct SQL statement is:

c) SELECT CustomerName FROM CUSTOMERS WHERE CustomerID IN (SELECT CustomerID FROM ORDERS WHERE OrderID IN (SELECT OrderID FROM ORDERITEMS WHERE BookID IN (SELECT BookID FROM BOOKS WHERE Title = 'E-BUSINESS THE EASY WAY')))

This query correctly uses subqueries to filter down the customers who have an order containing the desired book by its title. It starts by identifying the BookID corresponding to the given title, then finds the OrderIDs associated with that BookID, and finally selects the CustomerIDs associated with those orders, which is then used to filter the CustomerNames from the CUSTOMERS table.