LEARN WITH ABDOU / DATABASE TEAM LAB
Board Game Café: rescue the launch report
Six short report-audit challenges: repair CASE rules, preserve real activity, and explain what the evidence supports. Use questions 1–3 as a 10–15 minute warm-up; all six take about 25–35 minutes.
Team: __________________________ Date: ______________
Dataset: Boreal Board Game Café · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/games-reporting
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. Name the row before writing the query
In the six-table schema, identify the three activity tables. What does one row mean in each? Which activity can belong to an anonymous walk-in?
P2. Predict the silent mistakes
Before running SQL, explain how a query can run successfully but assign the wrong label or lose a real repeated action. Give one boundary or duplicate case you would test.
After the investigation
Choose one repair and record the faulty result, corrected SQL, a counterexample and the business consequence. Export your team evidence. Continue the official Lab 9 questions, client transfer and individual reflection in your own report; submit in Canvas. This companion is practice, not the graded assignment.
Database schema
cafe_orders · 40 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| member_id | INTEGER | |
| order_date | TEXT | NOT NULL |
| total_amount | REAL | NOT NULL |
| payment_method | TEXT | NOT NULL |
member_id → members.id
events · 6 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
| event_date | TEXT | NOT NULL |
| capacity | INTEGER | NOT NULL |
| fee | REAL | NOT NULL |
games · 14 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| title | TEXT | NOT NULL |
| category | TEXT | NOT NULL |
| min_players | INTEGER | NOT NULL |
| max_players | INTEGER | NOT NULL |
| play_minutes | INTEGER | NOT NULL |
| shelf_price | REAL | NOT NULL |
| rating | REAL |
members · 14 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| first_name | TEXT | NOT NULL |
| last_name | TEXT | NOT NULL |
| TEXT | NOT NULL | |
| phone | TEXT | |
| tier | TEXT | NOT NULL |
| joined_date | TEXT | NOT NULL |
registrations · 20 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| event_id | INTEGER | NOT NULL |
| member_id | INTEGER | NOT NULL |
| registered_date | TEXT | NOT NULL |
| paid | INTEGER | NOT NULL |
member_id → members.id
event_id → events.id
sessions · 30 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| member_id | INTEGER | NOT NULL |
| game_id | INTEGER | NOT NULL |
| session_date | TEXT | NOT NULL |
| minutes_played | INTEGER | NOT NULL |
| score | INTEGER | |
| session_type | TEXT | NOT NULL |
game_id → games.id
member_id → members.id
One-month teaching snapshot. sessions records play; registrations records signups; cafe_orders records purchases. registrations is the actual table name. NULL cafe_orders.member_id means an anonymous walk-in; NULL games.rating means not yet rated; NULL sessions.score means cooperative play; NULL members.phone means not on file. registered_date is the signup day; event_date is the scheduled event day. Registrations are not attendance.
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. The long game gets a short appointment
The host needs id, title and slot for every game: Long slot at 90+ minutes, Standard slot at 45+ minutes, otherwise Short slot. Order by id. Repair the rule order and identify a game that proves the starting query is wrong. Explain the 45- and 90-minute boundaries.
SELECT id, title, CASE WHEN play_minutes >= 45 THEN 'Standard slot' WHEN play_minutes >= 90 THEN 'Long slot' ELSE 'Short slot' END AS slot FROM games ORDER BY id;
SQL / evidence / explanation:
2. Two new arrivals receive an unfair review
Return id, title and review_state for all games, ordered by id: Awaiting review for missing ratings, Recommend at 4.4+, otherwise Review notes. Repair the query. Identify the misclassified games and explain why replacing missing ratings with zero would also mislead.
SELECT id, title, CASE WHEN rating = NULL THEN 'Awaiting review' WHEN rating >= 4.4 THEN 'Recommend' ELSE 'Review notes' END AS review_state FROM games ORDER BY id;
SQL / evidence / explanation:
3. The count claims every session was long
Count sessions lasting at least 60 minutes as long_sessions and total their minutes as long_minutes, in one row. Repair the COUNT while checking the SUM. Explain why the same ELSE 0 is wrong in one aggregate and appropriate in the other.
SELECT COUNT(CASE WHEN minutes_played >= 60 THEN 1 ELSE 0 END) AS long_sessions, SUM(CASE WHEN minutes_played >= 60 THEN minutes_played ELSE 0 END) AS long_minutes FROM sessions;
SQL / evidence / explanation:
4. The directory puts a price where a name belongs
Make a six-row directory from games id <= 3 and events id <= 3: label, dollars, source (Game or Event), sorted by source and label. The query runs. Repair the column positions, then explain why a six-row count alone cannot approve the report.
SELECT title AS label, shelf_price AS dollars, 'Game' AS source FROM games WHERE id <= 3 UNION ALL SELECT fee, name, 'Event' FROM events WHERE id <= 3 ORDER BY source, label;
SQL / evidence / explanation:
5. A repeat customer loses two actions
For member 3 on 2026-09-12, preserve every recorded session and café order with member_id, activity_date and source (Session or Order). Sort by activity_date and source. Repair the query, reconcile both source counts, then inspect source IDs separately to prove that repeated display rows are real actions.
SELECT member_id, session_date AS activity_date, 'Session' AS source FROM sessions WHERE member_id = 3 AND session_date = '2026-09-12' UNION SELECT member_id, order_date, 'Order' FROM cafe_orders WHERE member_id = 3 AND order_date = '2026-09-12' ORDER BY activity_date, source;
SQL / evidence / explanation:
6. The invitation list asks the opposite question
Find members who placed a café order but have no session. Return id and first_name, ordered by id, using EXCEPT on member IDs. Repair the subtraction direction. Explain why a name-only set and a claim that these people dislike games would both be weak conclusions.
SELECT id, first_name FROM members WHERE id IN (SELECT member_id FROM sessions EXCEPT SELECT member_id FROM cafe_orders WHERE member_id IS NOT NULL) ORDER BY id;
SQL / evidence / explanation: