LECTURES / MODULE 3
SQL functions & delivery evidence
Prepare for Lab 4: clean labels, read timestamps, handle missing senders, and trace package evidence with single-table SQL.
1 / 7
Clean text without changing the evidence
One addresses row is one recorded location; one scans row is one tracking event. A scalar function transforms a value for each row. An aggregate summarizes rows. LOWER(name) does not mean the same kind of operation as COUNT(name), even though both use parentheses.
TRIM removes ordinary spaces from the ends; LOWER and UPPER standardize letter case. Nest functions from the inside out: LOWER(TRIM(type)) trims before changing case. These SELECT expressions change the report, not the stored value. Keep the original beside the cleaned output so another analyst can check what changed.
Case cleanup is not identity matching: two addresses with the same normalized label can still be different locations. SQLite’s built-in LOWER/UPPER cover ASCII letters; do not assume this is complete international name normalization.
SELECT id, type, LOWER(TRIM(type)) AS clean_type FROM addresses ORDER BY id LIMIT 5;
The first five address records keep their identifiers and raw types. A different clean_type reveals a display transformation you can inspect.
Re-run SELECT id, type FROM addresses to inspect the stored values. Cleaning for a report and editing source evidence are separate decisions.
Explore this concept →Explain it in your own words
What evidence would show that this SELECT did not overwrite a driver’s name? Why should a login identifier need more checks than lowercasing?
2 / 7
Slice, replace, and build a readable label
SUBSTR starts at position 1. SUBSTR(name, 1, 2) keeps the first two characters; a negative start counts from the end. LENGTH counts characters, including spaces. Test a short literal before applying a position rule to an entire table.
REPLACE substitutes matching text wherever it occurs and is case-sensitive. It does not know which words are street suffixes. Compare the original with the shortened text to catch unintended changes.
The || operator concatenates text. Put separators such as spaces or dashes inside single-quoted literals. The + operator performs numeric addition. A plausible-looking numeric result can therefore be a logic error in a label query.
SELECT id, address, REPLACE(address, 'Road', 'Rd') AS compact_address FROM addresses WHERE address LIKE '%Road%' ORDER BY id;
The WHERE clause selects address strings containing Road. REPLACE changes matching spelling, so examine the two columns rather than assuming every match was a suffix.
For the exercise, calculate the inside expression first. Short codes are display labels; id remains the primary key.
Explore this concept →Explain it in your own words
Can two drivers share this code? What additional evidence would you need before calling it a unique identifier?
3 / 7
Separate calendar dates from clock times
The scans timestamp is ISO-style text: YYYY-MM-DD HH:MM:SS. DATE(timestamp) extracts a calendar date; TIME(timestamp) extracts the clock. A date-only string is not equal to a timestamp containing a time, even on the same day.
strftime('%H', timestamp) returns a two-character hour as text, and '%w' returns a weekday code from '0' (Sunday) to '6' (Saturday). Compare consistent formats. For this fixed teaching snapshot, use a fixed date from its data rather than 'now', which changes the question whenever you run it.
Date functions are not general-purpose format guessers. If a value returns NULL, inspect the original string. These timestamps have no zone suffix: the exercise does not establish their timezone or support a real-world timezone conversion.
SELECT id, DATE(timestamp) AS scan_day, TIME(timestamp) AS scan_clock, strftime('%H', timestamp) AS hour_text FROM scans ORDER BY timestamp, id LIMIT 5;The date and clock are two views of the same event timestamp. The result still has one row per scan.
The final id key makes equal timestamps deterministic. It does not establish which of two simultaneous physical events happened first.
Explore this concept →Explain it in your own words
Why could WHERE timestamp = '2024-10-01' return no rows even when scans occurred that day?
4 / 7
Measure elapsed time with explicit units
julianday converts a supported timestamp into a day number. Subtract the earlier value from the later value to get elapsed days. Multiply by 24 for hours or 1440 for minutes. Subtracting the original text strings does not calculate elapsed time.
ROUND is a presentation choice applied after the calculation. ABS removes the sign: it is useful when you want a magnitude, but can conceal that start and end were reversed. Preserve direction until you have checked the event sequence.
MIN(timestamp) and MAX(timestamp) identify the observed bounds in this consistently formatted snapshot. Their difference describes the scan window. It is not automatically total shipping time: events before the first scan or after the last scan are not observed.
SELECT ROUND((julianday('2024-10-02 10:30:00') - julianday('2024-10-01 09:00:00')) * 24, 1) AS elapsed_hours;The literal example crosses midnight, so subtracting clock times alone would give the wrong interval. The date is part of the calculation.
In the practice, show both endpoints next to the duration. This makes the units and the source window reviewable.
Explore this concept →Explain it in your own words
What can you conclude from the observed hours, and what would be an overclaim about delivery speed?
5 / 7
Repair integer division before rounding
When both operands are integers, SQLite division truncates toward zero. ROUND(7 / 2, 1) rounds an already truncated 3; it cannot recover the missing fraction. Put a real-valued operand into the division first, such as 7 * 1.0 / 2.
A rate needs a named numerator and denominator. A mean number of scans per package is different from the percentage of packages delivered. Neither a decimal place nor an attractive alias establishes the business meaning.
NULLIF(denominator, 0) changes a zero denominator into NULL. This leaves an undefined ratio missing rather than labeling it zero. Explain why the denominator is valid before interpreting the result.
SELECT 7 / 2 AS integer_result, ROUND(7 * 1.0 / 2, 1) AS real_result, 7.0 / NULLIF(0, 0) AS undefined_ratio;
The first expression demonstrates truncation; the second preserves the fractional result. A missing ratio communicates an undefined operation, not a successful zero rate.
The fixed denominator is justified only by this snapshot. Recheck SELECT COUNT(*) FROM packages before reusing the calculation with new data.
Explore this concept →Explain it in your own words
Why is this mean not the percentage delivered? What would have to change if another package were added?
6 / 7
Keep missing values honest
In packages, a NULL from_address_id means no sender is on file. It does not mean address 0, the destination, or proof that the sender deliberately hid their identity. Use IS NULL to select these records; equality to NULL does not return true.
COALESCE returns the first non-NULL value; IFNULL is the two-argument alternative. For a readable report, 'Sender #' || from_address_id becomes NULL when the sender is missing. Wrap the entire label expression in COALESCE to provide a readable fallback without changing the original ID.
An empty string and an all-space string are not NULL. COALESCE('', 'Unknown') keeps the empty string. For a text-import cleanup rule, NULLIF(TRIM(value), '') can turn an empty cleaned string into NULL before applying COALESCE. Use that rule only when a blank really means missing in your data contract.
SELECT COALESCE('', 'Unknown') AS empty_kept, COALESCE(NULLIF(TRIM(' '), ''), 'Unknown') AS blank_label, IFNULL(NULL, 'Unknown') AS null_label;The three expressions distinguish an empty string, spaces transformed into NULL, and an existing NULL. They need different tests.
The practice leaves the relationship column intact. A readable fallback must not invent a relationship to address 0.
Explore this concept →Explain it in your own words
Why should Sender unknown remain a report label instead of being written into an integer address field?
7 / 7
Trace IDs and defend the last observed state
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.
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.
Explore this concept →Explain it in your own words
Which two facts would you compare to distinguish reaching the intended destination from merely having a Drop scan?
SQL reference & course materials
Use these reference chapters alongside the detailed lessons above. Each example identifies its database.
SQLite references: scalar functions, date and time functions, and expressions. Examples here are tested on the classroom engine; newer functions in the documentation may require a newer SQLite version.
Put it to work in class
Lab 4: The Deliveries Investigation →
Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.