Project 1: A Report Which Shows Stores With Excess Inventory:

SELECT 

s.street AS store,
f.name AS flavor,
count

FROM inventory AS i

LEFT JOIN flavors AS f ON i.flavorID = f.id,
stores AS s ON i.storeID = s.id

WHERE i.count > 10

 

Project 2: A Report of Which State Returns the Most Profit:

SELECT s.state,
SUM(s.profit) AS total_profit
FROM inventory AS i

LEFT JOIN flavors AS f ON i.flavorID = f.id,
stores AS s ON i.storeID = s.id

GROUP BY state
ORDER BY total_profit DESC

LIMIT 1

Project 3: A Report of How Much It Would Cost Each Store to Fully Stock Up:

SELECT s.street AS store,
--f.name,
--SUM(i.count) AS buckets_in_store,
SUM(15 - i.count) AS buckets_needed,
--f.cost,
SUM((15 - i.count) * f.cost) AS subtotal


FROM inventory AS i

LEFT JOIN flavors AS f ON i.flavorID = f.id,
stores AS s ON i.storeID = s.id

WHERE count < 15

GROUP BY i.storeID