LEARN WITH ABDOU / DATABASE TEAM LAB
Deliveries: the dispatch desk challenge
A 35–45 minute team practice companion for Lab 4. Repair misleading queries and defend a package timeline using the assigned Deliveries database.
Team: __________________________ Date: ______________
Dataset: North Shore Deliveries · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/deliveries-functions
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 your evidence
Open Quick schema. What does one row in packages mean, and how does that differ from one row in scans? Which field carries the package ID between them?
P2. Predict before running
Read the functions sequence. Predict 7 / 2 versus 7 * 1.0 / 2, and explain why an empty string and NULL need different tests.
After the investigation
Choose one query that ran successfully but answered the wrong question. Explain your correction with evidence. Record one claim your package investigation cannot establish. Export your team notes and SQL. Return to official Lab 4 for its numbered questions, personal reflections and Canvas report; this companion does not submit or replace that work.
Database schema
addresses · 55 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| address | TEXT | NOT NULL |
| type | TEXT | NOT NULL |
drivers · 20 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
packages · 40 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| contents | TEXT | NOT NULL |
| from_address_id | INTEGER | |
| to_address_id | INTEGER | NOT NULL |
to_address_id → addresses.id
from_address_id → addresses.id
scans · 99 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| driver_id | INTEGER | NOT NULL |
| package_id | INTEGER | NOT NULL |
| address_id | INTEGER | NOT NULL |
| action | TEXT | NOT NULL |
| timestamp | TEXT | NOT NULL |
address_id → addresses.id
package_id → packages.id
driver_id → drivers.id
Reading the evidence: one package can have several scans. Carry package_id into scans, then address_id into addresses using separate queries. Under the lab’s model, the final Drop means at that address; the final Pick means on a truck. A NULL from_address_id means no sender on file. Timestamps use YYYY-MM-DD HH:MM:SS. Describe the last observed state, not a live location.
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. The suspicious filter
Dispatch wants every warehouse location regardless of letter case or ordinary outer spaces. Run the starting query, then revise it to return id, address, and type using LOWER(TRIM(type)) = 'warehouse', ordered by id. Compare the two sets. Does normalization prove the locations are duplicates?
SELECT id, address, type FROM addresses WHERE type = 'warehouse' ORDER BY id;
SQL / evidence / explanation:
2. The badge printer is off by one
This code should return the first two uppercase letters of each driver's name. Explain the error and fix it. Could the repaired code replace the driver's primary key? Show a collision or explain what check would be needed.
SELECT id, name, UPPER(SUBSTR(name, 0, 2)) AS short_code FROM drivers ORDER BY id;
SQL / evidence / explanation:
3. The day that disappeared
A teammate claims there were no scans on October 1. Test this query, repair it to return id, package_id, and timestamp for that day, ordered by timestamp and id, and explain why successful execution was insufficient evidence.
SELECT id, package_id, timestamp FROM scans WHERE timestamp = '2024-10-01' ORDER BY timestamp, id;
SQL / evidence / explanation:
4. A dashboard rounds the wrong number
The snapshot has 40 packages. Compare the starting mean with a version that preserves fractional scans per package and rounds to 1 decimal. Explain where the fraction was lost. Why can neither result be called a delivery success percentage?
SELECT ROUND(COUNT(*) / 40, 1) AS mean_scans FROM scans;
SQL / evidence / explanation:
5. A label quietly loses packages
For packages with id >= 34, show id, the raw from_address_id, and a readable sender_label that says Sender unknown for missing IDs. Explain the starting output. Would COALESCE(from_address_id, 0) be a safe address to look up? Test COALESCE('', 'Unknown') separately and explain how blank differs from NULL.
SELECT id, from_address_id, 'Sender #' || from_address_id AS sender_label FROM packages WHERE id >= 34 ORDER BY id;
SQL / evidence / explanation:
6. The package 8 evidence board
Use separate queries to find package 8's contents and intended destination, list its scans in timestamp/id order, and look up the last observed address. Calculate observed hours between its first and last scans, rounded to 2 decimals. Explain its last observed state using the handout's Pick/Drop rule. Which evidence supports your conclusion, and what does the snapshot leave unknown?
SELECT id, contents, to_address_id FROM packages WHERE id = 8;
SQL / evidence / explanation: