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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.