LEARN WITH ABDOU / DATABASE TEAM LAB
Northshore Coffee: SELECT & WHERE
A companion practice sheet for the fictional Week 2 coffee database.
Team: __________________________ Date: ______________
Dataset: Northshore Coffee · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/coffee-select-where
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. Know the row
Inspect the schema. What does one row represent in the table you will query? Which column identifies it?
P2. Read before you run
Read the matching lecture. Explain the difference between selecting columns and filtering rows. For a JOIN lab, also predict the number of matches.
P3. Bring a question
Run a small preview. Record one result you predicted and one point you want to discuss in class.
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. Investigate 1
Return name and city for all shops, ordered by id.
SQL / evidence / explanation:
2. Investigate 2
Return name and city for shops with a drive-through (has_drive_thru = 1), ordered by name.
SQL / evidence / explanation:
3. Investigate 3
Return name and price for menu items costing less than $5, ordered by price then id.
SQL / evidence / explanation:
4. Investigate 4
Return name, category, and calories for Tea items with fewer than 150 calories, ordered by name. How are NULL calories treated?
SQL / evidence / explanation:
5. Investigate 5
Return first_name, last_name, and hourly_wage for employees earning at least $20 per hour, ordered by hourly_wage descending then id.
SQL / evidence / explanation:
6. Investigate 6
Return names of menu items with missing calorie values, ordered by name. Explain why = NULL is not the right test.
SQL / evidence / explanation:
7. Find the logic bug
The request is Tea or Smoothie items below $5. Does this SQL implement that request? Find a counterexample and repair the condition.
SELECT name, category, price FROM menu_items WHERE category = 'Tea' OR category = 'Smoothie' AND price < 5 ORDER BY id;
SQL / evidence / explanation:
8. Missing is not zero
Explain why this returns no unknown calorie values. Repair it, then explain why substituting zero would change the meaning.
SELECT name, calories FROM menu_items WHERE calories = NULL;
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.