LEARN WITH ABDOU / DATABASE TEAM LAB
Coffee: plan and verify a change
Preview a change and build an evidence-based validation plan.
Team: __________________________ Date: ______________
Dataset: Northshore Coffee · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/coffee-changes
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. Preview before changing rows
Return id, name, and price for Tea items priced below 5, ordered by id. This is the scope preview for a proposed price change. Which unchanged rows would you inspect afterward, and why is checking only the changed count insufficient?
SQL / evidence / explanation:
2. Design constraints and verify the schema
Return shop_id and employee_id for the first five orders ordered by id. Identify the two relationships a future import must preserve. What invalid row would you attempt in a disposable database to test a proposed constraint?
SQL / evidence / explanation:
3. Reconcile an import and plan recovery
Return shop_id and ROUND(SUM(total_amount), 2) AS total_sales, grouped and ordered by shop_id. Could an import preserve the row count but change total sales? Give a concrete example.
SQL / evidence / explanation:
4. Catch the scope error
A teammate proposes UPDATE menu_items SET price = price * 1.05 to raise Tea prices only. Explain the defect without executing it. Write a preview and the corrected statement in your notes.
SQL / evidence / explanation:
5. Prove more than a count
An import has the expected row count but one order is duplicated and another is missing. Propose checks that expose the problem. Run appropriate checks against the supplied original and explain what a baseline would record.
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.