Question: Design an inventory tracking system using database tables. Customers, inventory, and orders are tracked.

Then, write a query to find an order when you only have a lastname

Solution: ) Customer(CustId, FName, LName)
2) Inventory(InvId, Item)
3) Orders(OrderId, CustId)
4) Order_Items(OrderId, InvId)

SELECT *
FROM customer c JOIN orders o ON c.custId=o.custId
JOIN order_items oi ON oi.OrderId=o.OrderId
JOIN inventory i ON oi.invId=i.invId
WHERE c.LName=:lname;

**NB: Breaking Order_items into a separate table to prevent a many-to-many relationship. For interview, this is probably not necessary