Bookstore Project - Part 2: Search & Join Queries

Let's think this through for a moment

The table structure is done from Part 1, so in this Part 2 we'll use INNER JOIN and LEFT JOIN on those tables to write queries like a real app would need. On a bookstore website, when a user searches by book title, the author name needs to show up too, so we need to JOIN the Books table with the Authors table. We'll also need to JOIN Orders, Customers, and Books together to show each customer's order history — which books they bought. Combine WHERE, LIKE, and ORDER BY together and you'll end up with something close to a real search bar feature.

Let's build it for real

First, INNER JOIN Books with Authors and show books whose title contains 'Harry' along with the author name (LIKE '%Harry%') — sort by price from lowest to highest with ORDER BY. Second, find books where Stock = 0 with WHERE Stock = 0 and produce an 'Out of stock' list. Third, JOIN Orders, Customers, and Books together and produce an order history report with CustomerName, Title, Quantity, and OrderDate, sorted with ORDER BY OrderDate DESC. Finally, use LEFT JOIN to find books that have never been ordered (books nobody has bought yet).

• Wrote a search query combining book title and author name using INNER JOIN + LIKE
• Built an order history query combining Customers, Books, and Orders
• Used LEFT JOIN to find books that don't have any orders yet

Code example

-- Search books by title, show author name too
SELECT Books.Title, Authors.AuthorName, Books.Price
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE Books.Title LIKE '%Harry%'
ORDER BY Books.Price ASC;

-- Out of stock books
SELECT Title, Stock FROM Books WHERE Stock = 0;

-- Order history report
SELECT Customers.CustomerName, Books.Title, Orders.Quantity, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Books ON Orders.BookID = Books.BookID
ORDER BY Orders.OrderDate DESC;

-- Books that were never ordered
SELECT Books.Title
FROM Books
LEFT JOIN Orders ON Books.BookID = Orders.BookID
WHERE Orders.OrderID IS NULL;

The search query will return book and author names joined together, the order history report will show the latest orders first, and the LEFT JOIN result will surface books that don't have any orders yet (such as Fantastic Beasts).