LEARN WITH ABDOU / DATABASE TEAM LAB
Coffee: defend the summary
Compare menu categories and explain the population behind every number.
Team: __________________________ Date: ______________
Dataset: Northshore Coffee · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/coffee-summary
Practice companion. Confirm official assignment and submission instructions in Canvas. Rotate query writer, evidence checker, and explainer. Record SQL, evidence, and reasoning.
Before class
P1. Read and predict
Read the matching module. Name one concept you will use and predict the output grain of its example.
P2. Inspect the database
Open Quick schema and preview the relevant table. Record its key and a field whose meaning needs explanation.
P3. Prepare your check
Write one small query that could expose an incorrect answer to this lab. Explain what you would look for.
Database schema
employees · 25 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| first_name | TEXT | NOT NULL |
| last_name | TEXT | NOT NULL |
| TEXT | ||
| phone | TEXT | |
| shop_id | INTEGER | |
| position | TEXT | |
| hire_date | TEXT | |
| hourly_wage | REAL |
shop_id → shops.id
menu_items · 45 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
| category | TEXT | NOT NULL |
| size | TEXT | |
| price | REAL | NOT NULL |
| calories | INTEGER | |
| is_seasonal | INTEGER | |
| description | TEXT |
orders · 204 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| shop_id | INTEGER | |
| employee_id | INTEGER | |
| order_date | TEXT | |
| total_amount | REAL | |
| payment_method | TEXT | |
| tip_amount | REAL |
employee_id → employees.id
shop_id → shops.id
shops · 8 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
| city | TEXT | NOT NULL |
| address | TEXT | |
| phone | TEXT | |
| open_date | TEXT | |
| has_drive_thru | INTEGER |
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. Count rows and known values
Return COUNT(*) AS item_count and COUNT(DISTINCT category) AS category_count from menu_items. Why are item_count and category_count answering different questions?
SQL / evidence / explanation:
2. Transform values without losing their meaning
Return name and ROUND(price * 1.05, 2) AS proposed_price for all menu items ordered by id. Does this query change the menu prices? What would you verify before applying an actual update?
SQL / evidence / explanation:
3. Choose the unit of your summary
Return category and COUNT(*) AS item_count for each category, ordered by category. If you add size to GROUP BY, why might the number of result rows increase?
SQL / evidence / explanation:
4. Filter rows before groups, groups after aggregation
Return category and COUNT(*) AS item_count for categories with at least three menu items. Include all price levels; order by category. Why would WHERE price >= 3 answer a different question?
SQL / evidence / explanation:
5. Challenge an average
Compare AVG(calories) with AVG(COALESCE(calories, 0)). Explain the denominator and the factual assumption made by each.
SELECT AVG(calories) AS known_average, AVG(COALESCE(calories, 0)) AS zero_filled_average FROM menu_items;
SQL / evidence / explanation:
6. Repair a grouping claim
The author says name identifies the item that explains each category average. Why is that unsupported? Propose a result whose labels match its grain.
SELECT category, name, AVG(price) AS average_price FROM menu_items GROUP BY category;
SQL / evidence / explanation:
Reflect and submit
Choose your strongest answer and one revision. Explain the evidence behind each. Export your SQL and notes, then follow Canvas for the assigned report, grading, and submission requirements.