SQL from zero · Chapter 2 of 37
Finding people
By the end of this chapter you can filter with several conditions at once: lists of values, ranges, text patterns and missing values. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: First queries
- 5 tasks
- SQL
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
Why you need this
Meera, the product manager, is planning meetups in the south, and she needs lists: the members in Bengaluru, Chennai or Hyderabad, the posts that mention chai for the invite, and the members who never filled in a city, so she can nudge them.
With one exact condition, as in chapter 1, you would run one query per city and paste the results together. A search for 'chai' would miss a post that says "Chai". And asking for members whose city "equals nothing" returns no rows at all, without any error, so you would tell Meera that everyone has a city when two members do not.
The lesson
Comparing and combining
=, <> (not equal), <, >, <= and >= compare a column with a value. AND needs both sides to be true, OR needs either, and NOT flips a condition. Two shortcuts save typing: IN matches any value in a list, and BETWEEN matches a range with both ends included (likes_count BETWEEN 3 AND 6 means 3, 4, 5 or 6).
SELECT username, city FROM users
WHERE city IN ('Pune', 'Jaipur')
ORDER BY username;
username │ city
──────────┼────────
nikhil │ Jaipur
omar │ Pune
priya │ Pune
sneha │ Pune
yash │ Jaipur
(5 rows)
OR needs a full condition on both sides. WHERE city = 'Pune' OR 'Jaipur' fails with invalid input syntax for type boolean: "Jaipur".
AND binds tighter than OR, so mixing them needs brackets:
SELECT type, likes_count FROM posts
WHERE (type = 'image' OR type = 'video') AND likes_count > 5;
type │ likes_count
───────┼─────────────
image │ 7
image │ 7
video │ 6
(3 rows)
Without the brackets Postgres reads it as "image, or (video with more than 5 likes)" and returns 13 rows: every one of the 12 image posts, whatever its likes.
Patterns
LIKE matches a pattern: % stands for any run of characters and _ for exactly one. LIKE cares about capital letters; ILIKE does not:
SELECT username, bio FROM users WHERE bio ILIKE '%chai%';
username │ bio
──────────┼───────────────────────────────────────────────
anya │ Backend engineer. Postgres fan. Chai, always.
(1 row)
With LIKE '%chai%' the same query returns 0 rows, because Anya wrote "Chai".
NULL is not a value
A missing city is NULL, which means "unknown". Comparing anything with unknown gives unknown, and WHERE keeps a row only when its condition is true. Press Next to watch a member with no city fall out of a comparison that has nothing to do with her:
The same rule makes WHERE city = NULL find nobody:
minglesphere=> SELECT count(*) FROM users WHERE city = NULL;
count
───────
0
(1 row)
Ask with IS NULL (or IS NOT NULL) instead: WHERE city IS NULL finds the 2 members with no city.
One more NULL you will use all the time: members and posts are never removed. When a member leaves, their deleted_at gets a date. Active means deleted_at IS NULL.
Your turn
Your five tasks are at the top, one file each: four lists for Meera, then Vikram's interview question. Most of them want active rows only, so read each goal for that. For the interview, try city <> 'Mumbai' first, count the rows, and work out who went missing.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
no-city.sql: members with no city
Find the members who have not filled in a city: their city is NULL. Every member counts, including any who have left the site.
Return two columns: username and display_name. Any order.
- Task 2
chai-posts.sql: posts that mention chai
Find the posts that mention chai anywhere in their content, however it is written: chai, Chai and CHAI all count. Leave out deleted posts (they have a date in deleted_at).
Return two columns: content and created_at. Any order.
- Task 3
south-members.sql: active members in the south
Find the active members who live in Bengaluru, Chennai or Hyderabad. Leave out members who left (they have a date in deleted_at): one of them lived in Hyderabad.
Return two columns: username and city. Any order.
- Task 4
media-posts.sql: image and video posts with 3 to 6 likes
Find the active posts (no date in deleted_at) whose type is image or video and that have from 3 to 6 likes in likes_count, 3 and 6 included.
Return three columns: content, type and likes_count. Any order.
- Task 5 · interview question
Interview: not-mumbai.sql, active members outside Mumbai
Find every active member (no date in deleted_at) who does not live in Mumbai. A member with no city does not live in Mumbai either, so members with no city belong in the list too.
Return two columns: username and city. Any order.
What you will learn
- Comparing and combining: <>, <, >=, IN, BETWEEN, and AND and OR with brackets.
- Patterns: LIKE and ILIKE find text inside text.
- NULL: why a missing value never matches a comparison, and how IS NULL asks about it.
More in First queries
Build "Finding people" 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.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.