SQL from zero · Chapter 3 of 37
Shaping the output
By the end of this chapter you can return values you compute, not just raw columns: new text, filled-in gaps, labels and lists without repeats. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: First queries
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Meera has a town hall on Friday and one slide left: a members slide with proper names, handles like @anya, the cities MingleSphere is in, and whether posts are short or long.
So far a query returns columns exactly as they are stored. SELECT city FROM users gives 24 rows: Bengaluru three times and two empty ones. A display name is optional, so a member who never set one would show up blank. There is no handle column and no size column at all. You would be fixing all of it by hand on the slide.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
slide-names.sql: a name for every active member
For every active member (no date in deleted_at), give the name to show on the slide: their display_name, or their username when display_name is NULL.
Return two columns: username and name. Any order.
- Task 2
handles.sql: every member's handle and city code
For every member, including those who have left the site, build two values. handle is an @ followed by the username, like @anya. city_code is the first 3 letters of their city in capitals, like BEN for Bengaluru; it stays NULL when the member has no city (not an empty text).
Return two columns: handle and city_code. Any order.
- Task 3
cities.sql: the cities where active members live
List each city where at least one active member (no date in deleted_at) lives, each city once. Leave out the empty city of members who have none. A city whose only members have all left must not appear.
Return one column: city. Sort A to Z.
- Task 4
post-sizes.sql: a size for every active post
For every active post (no date in deleted_at), give a size from the number of characters in its content: short for 32 or fewer, medium for 33 to 45, long for 46 or more. A post of exactly 32 characters is short, and one of exactly 45 is medium.
Return two columns: content and size. Any order.
What you will learn
- Expressions and AS: compute a new value in the SELECT list and give it a name.
- COALESCE: use this value, or else that one.
- CASE: choose a label by conditions.
- DISTINCT: return each row only once.
More in First queries
Build "Shaping the output" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.