Chapter 03 · 7 min · Basic
First look at a table
You have been given access to a table called deliveries. Nobody has explained it. There is no documentation, the person who built it left, and you have been asked how many parcels were delivered last week.
This is the normal case, not the unlucky one. So it is worth having a fixed routine — five short queries you run on anything you have never seen, in the same order, every time.
They take about two minutes and they are not a full audit. They will not tell you whether the data is right. What they tell you is what the table is shaped like, and above all the one thing that decides every query you write afterwards: what one row means.
The dataset
A parcel-tracking table named deliveries, fifteen rows. The name is a lie, or at least a half-truth — working out exactly how is the whole point of this chapter. Resist reading all fifteen rows for now; run the queries first, the way you would have to on a table with fifteen million.
Schema
| scan_id | int |
| tracking_no | text |
| courier | text |
| city | text |
| status | text |
| scanned_on | date |
Example data
One: look at a handful of rows
Always first, always with a limit. On a real table, select * with no limit is how you lock up your laptop for four minutes and learn nothing.
Six rows is enough to see the column names in their natural habitat — what the values actually look like, as opposed to what the column names promise. status holds picked_up, in_transit, delivered and so on; those underscores and that lower case matter, because 'Delivered' will match nothing.
And the thing to notice immediately: MY1001 appears on three consecutive rows. Park that; it is the answer to the question this chapter is really about.
Before you run it: are you expecting one row per parcel?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to actually do with a dataset once somebody hands you one, from the first look to a number you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Two: the columns and their types
- 03Three: how big, and how old
- 04Four: what does one row mean?
- 05Five: show me the repeats
- 06So how many parcels were delivered?