INTERACTIVE LECTURES
Understand database structure
Guided class activity · apply the ideas from the full SQL lessons.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Test one-to-many relationships
Step 2 of 3 · Database: Northshore Coffee
READ & UNDERSTAND
A shop can have many employees, while each employee row stores one shop_id. The foreign-key column belongs on the many side of this relationship.
The data can show that multiple employees share a shop today. It cannot by itself prove every business rule for future data. Distinguish an observed pattern from an enforced constraint.
Use a small count to challenge your proposed cardinality before writing a larger JOIN.
Worked example
SELECT shop_id, COUNT(*) AS employees FROM employees GROUP BY shop_id ORDER BY shop_id;
Grouping employees by shop_id counts the employee rows assigned to each shop represented in employees. It does not guarantee that shops with zero employees appear.
To include every shop, begin with shops and use a LEFT JOIN when you reach the relationships module. The table chosen as the starting point changes which entities can disappear.
Your question
Return shop_id and COUNT(*) AS order_count from orders, grouped and ordered by shop_id.
Explain your evidence
Why would storing every order as a new column on shops make the design harder to maintain?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.