Learn with Abdou

INTERACTIVE LECTURES

SQL functions & delivery evidence

Guided class activity · apply the ideas from the full SQL lessons.

Module overview & notes →All interactive lectures

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.

Slice, replace, and build a readable label

Step 2 of 7 · Database: North Shore Deliveries

READ & UNDERSTAND

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.

Worked example
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.

Your question

For drivers with id <= 5, return id and 'D-' followed by UPPER(SUBSTR(name, 1, 2)) AS dispatch_code. Order by id.

Explain your evidence

Can two drivers share this code? What additional evidence would you need before calling it a unique identifier?

Try a query to unlock the worked-answer discussion.

Loading…
Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

CURRENT DATABASE

Quick schema · North Shore Deliveries

Press Esc or click outside to return to your query. PK = primary key · FK = declared foreign key.

0 of 0 tables

This database has no tables yet. Create a table, then reopen the schema to see its columns.

Control Space also opens names. Enter adds a new line. Control or Command Enter runs your query.

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.