INTERACTIVE LECTURES
SQL functions & delivery evidence
Guided class activity · apply the ideas from the full SQL lessons.
Preparing for Lab 4 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Trace IDs and defend the last observed state
Step 7 of 7 · Database: North Shore Deliveries
READ & UNDERSTAND
For this lab, investigate across tables using a sequence of single-table queries. First identify a package in packages. Record its id and intended to_address_id. Next filter scans by that package_id and sort by timestamp, then id. Finally look up the relevant address id in addresses.
The handout’s model says a final Drop places the package at the recorded address, while a final Pick puts it on a driver’s truck. Describe this as the last observed state in the snapshot. The intended destination and the last scanned location are different facts.
Keep each query and its output together. A count of scans cannot identify a location; an address id alone does not establish delivery. If latest timestamps tie with conflicting actions, investigate the ambiguity instead of claiming the id sort proves physical order.
Worked example
SELECT id, contents, to_address_id FROM packages WHERE id = 8;
The package record gives the intended destination, not evidence of arrival. Its id becomes the filter in the scan query.
Look up an observed address with SELECT id, address, type FROM addresses WHERE id = the recorded number. Carry the value from your output; do not guess it.
Your question
Return id, action, address_id, and timestamp for every scan of package 8, ordered by timestamp and then id. After checking, use the playground to inspect its latest address in a separate query.
Explain your evidence
Which two facts would you compare to distinguish reaching the intended destination from merely having a Drop scan?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.