- Setup sqlite3
- Task
Setup sqlite3
1.1 Download the database schema: packages.db
1.2 Open Terminal from you Mac (Command+Space and enter "Terminal")
1.3 Change the current directory to 'Downloads' on your terminal cd Downloads
1.4 Open packages.db using sqlite3 DBMS sqlite3 packages.db
# Open packages.db database using sqlite3
sqlite3 packages.db
SQLite version 3.51.0 2025-06-12 13:14:41
Enter ".help" for usage hints.
sqlite>
# .tables shows all the tables in the database
sqlite> .tables
addresses drivers packages scans
sqlite> select *
...> from addresses
...> limit 5;
id address type
----- ------------------------------ ---------------
1 7660 Sharon Street Residential
2 60 Drake Place Residential
3 88 City Point Court Residential
4 266 Dorchester Avenue Residential
5 2 Otis Place Residential
# exit the database
sqlite> .exit
If the result output doesn't look as pretty as shown above, run the following commands inside the databases to make the output look pretty:
sqlite> .headers on
sqlite> .mode column
2. Task
Task
You are a mail clerk for the city of Boston and, as such, you oversee the delivery of mail across the city. For the most part, all packages sent are eventually delivered. Except, every once in while, a mystery falls into your lap: a missing package! For each customer that comes to you with a report of a missing package, your job is to determine:
The current address (or location!) of their missing package
The type of address or location (e.g. residential, business, etc.)
The contents of the package
All you know is what the customers themselves will tell you. To solve each mystery, you’ll need to use the mail delivery service’s database, packages.db, which contains data on the transit of packages around the city. Using just the information in the database, your task is to help each customer find their missing package.
Schema
packages.db represents all recent package deliveries in the city of Boston. To do so, packages.db represents the following entities:
Drivers, who are the people that deliver packages
The packages themselves
Addresses, such as 1234 Main Street
Scans of packages, which represent confirmations a delivery driver picked up or dropped off a given package
Within packages.db, you’ll find several tables that implement the relationships described in the ER diagram above. Click the drop-downs below to learn more about the schema of each individual table.
addresses table
The addresses table contains the following columns:
id, which is the ID of the address
address, which is the street address itself (i.e., 7660 Sharon Street)
type, which is the type of address (i.e., residential, commercial, etc.)
drivers table
The drivers table contains the following columns:
id, which is the ID of the driver
name, which is the first name of the driver
packages table
The packages table contains the following columns:
id, which is the ID of the package
contents, which contains the contents of the package
from_address_id, which is the ID of the address from which the package was sent
to_address_id, which is the ID of the address to which the package was sent. It’s not necessarily where it ended up!
scans table
The scans table contains the following columns:
id, which is the ID of the scan
driver_id, which is the ID of the driver who created the scan
package_id, which is the ID of the package scanned
address_id, which is the ID of the address where the package was scanned
action, which indicates whether the package was picked up (“Pick”) or dropped off (“Drop”)
timestamp, which is the day and time at which the package was scanned
Task 1, Find the lost letter
Your first report of a missing package comes from Anneke. Anneke walks up to your counter and tells you the following:
Clerk, my name’s Anneke. I live over at 900 Somerville Avenue. Not long ago, I sent out a special letter. It’s meant for my friend Varsha. She’s starting a new chapter of her life at 2 Finnegan Street, uptown. (That address, let me tell you: it was a bit tricky to get right the first time.) The letter is a congratulatory note—a cheery little paper hug from me to her, to celebrate this big move of hers. Can you check if it’s made its way to her yet?
Task 2
Your second report of a missing package comes from a mysterious fellow from out of town. They walk up to your counter and tell you the following:
Good day to you, deliverer of the mail. You might remember that not too long ago I made my way over from the town of Fiftyville. I gave a certain box into your reliable hands and asked you to keep things low. My associate has been expecting the package for a while now. And yet, it appears to have grown wings and flown away. Ha! Any chance you could help clarify this mystery? Afraid there’s no “From” address. It’s the kind of parcel that would add a bit more… quack to someone’s bath times, if you catch my drift.
Task 3
Your third report of a missing package comes from a grandparent who lives down the street from the post office. They approach your counter and tell you the following:
Oh, excuse me, Clerk. I had sent a mystery gift, you see, to my wonderful granddaughter, off at 728 Maple Place. That was about two weeks ago. Now the delivery date has passed by seven whole days and I hear she still waits, her hands empty and heart filled with anticipation. I’m a bit worried wondering where my package has gone. I cannot for the life of me remember what’s inside, but I do know it’s filled to the brim with my love for her. Can we possibly track it down so it can fill her day with joy? I did send it from my home at 109 Tileston Street.
Hint:
These tasks can be solved using subqueries
Solutions
Task 1
Important info:
- The address the package was sent from: 900 Somerville Avenue
- The address the package need to be sent to: 2 Finnegan Street (Anneke said that it was hard to the address right, so we should use LIKE because the delivery address may not fully correct)
- Package content: congratulatory note
-- 1. Get 900 Somerville Avenue id
sqlite> select * from addresses
...> where address='900 Somerville Avenue';
id address type
--- --------------------- -----------
432 900 Somerville Avenue Residential
-- 2. Get 2 Finnigan Street id
sqlite> select * from addresses
...> where address LIKE '2 Finn%';
id address type
--- ----------------- -----------
854 2 Finnigan Street Residential
-- Notice she said FinnEgan, the correct address was FinnIgan
-- 3. Get package id that was sent from 432 to 854
sqlite> select * from packages
...> where from_address_id=432 and to_address_id=854;
id contents from_address_id to_address_id
--- --------------------- --------------- -------------
384 Congratulatory letter 432 854
-- 4. Check the package info
sqlite> select * from scans
...> where package_id=384;
id driver_id package_id address_id action timestamp
-- --------- ---------- ---------- ------ --------------------------
54 1 384 432 Pick 2023-07-11 19:33:55.241794
94 1 384 854 Drop 2023-07-11 23:07:04.432178
So the package was picked up from the correct address and delivered to the correct address.
Did it make its way to 2 Finnigan Street? Yes.
Solution using subqueries:
sqlite> select * from scans
...> where package_id=(
(x1...> select id from packages
(x1...> where from_address_id=(
(x2...> select id from addresses
(x2...> where address='900 Somerville Avenue'
(x2...> ) and to_address_id=(
(x2...> select id from addresses
(x2...> where address like '2 Finn%'
(x2...> )
(x1...> );
id driver_id package_id address_id action timestamp
-- --------- ---------- ---------- ------ --------------------------
54 1 384 432 Pick 2023-07-11 19:33:55.241794
94 1 384 854 Drop 2023-07-11 23:07:04.432178
Task 2
Important info:
- From address: unknown
- To addess: unknown, we should find out where the package ended up
- Content: ambigious, he says 'quack'...
From where the package was picked up?
Where did the mysterious package end up and what were the contents?
-- 1. Select packages where from address is absent.
sqlite> select * from packages
...> where from_address_id IS NULL;
id contents from_address_id to_address_id
---- ------------- --------------- -------------
5098 Duck debugger 50
-- 2. Select all info about this package_id
sqlite> select * from scans
...> where package_id=5098;
id driver_id package_id address_id action timestamp
----- --------- ---------- ---------- ------ --------------------------
30123 10 5098 50 Pick 2023-10-24 08:40:16.246648
30140 10 5098 348 Drop 2023-10-24 10:08:55.610754
-- 3. Select the address from where it was picked up
sqlite> select * from addresses
...> where id=50;
id address type
-- ----------------- -----------
50 123 Sesame Street Residential
-- 4. Select the address to where it was delivered
sqlite> select * from addresses
...> where id=348;
id address type
--- ---------------- --------------
348 7 Humboldt Place Police Station
-- 5. Who delivered the package?
sqlite> select * from drivers
...> where id=10;
id name
-- ---------
10 Josephine
So
- From address: 123 Sesame Street
- To address: 7 Humboldt Place Police Station
- Content: Duck debugger
- Driver: Josephine
Task 3
- From address: 109 Tileston Street
- To address: 728 Maple Place
- Content: unknown, we will find out
-- 1. Select From address
sqlite> select * from addresses
...> where address='109 Tileston Street';
id address type
---- ------------------- -----------
9873 109 Tileston Street Residential
-- 2. Select To address
sqlite> select * from addresses
...> where address='728 Maple Place';
id address type
---- --------------- -----------
4983 728 Maple Place Residential
-- 3. Select all packages that were sent from 109 Tileston Street to 728 Maple Place
sqlite> select * from packages
...> where from_address_id=9873 and to_address_id=4983;
id contents from_address_id to_address_id
---- -------- --------------- -------------
9523 Flowers 9873 4983
-- 4. Select scan info of the package 9523
sqlite> select * from scans
...> where package_id=9523;
id driver_id package_id address_id action timestamp
----- --------- ---------- ---------- ------ --------------------------
10432 11 9523 9873 Pick 2023-08-16 21:41:43.219831
10500 11 9523 7432 Drop 2023-08-17 03:31:36.856889
12432 17 9523 7432 Pick 2023-08-23 19:41:47.913410
From address id: 9873
To address id: 4983
Here on the scans table the package 9523 was picked up from the correct address, 9823. But was delivered to wrong address, 7432, instead of 4983.
We picked up the package from the wrong address and in a few days will deliver to the correct address.
Top comments (0)