SQL from zero · Chapter 10 of 37

Putting names to numbers

By the end of this chapter you can join tables: put each post next to its author's name, chain three tables, and count per member by name. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Joins
  • 5 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

Rohan asked for the oldest posts and got this:

                               content                                │ author_id
──────────────────────────────────────────────────────────────────────┼───────────
 Weekly digest: top posts, new members and what is trending. (03 Jan) │        24
 Hot take: most apps need Postgres and nothing else.                  │         1
(2 rows)

He needs names, not numbers: who wrote what, who commented where, who liked which post. A post does not store its author's name. It keeps author_id, a pointer to a row in users, where the name lives.

With what you know, you would look up each id by hand (WHERE id = 24, then WHERE id = 1...) and copy the names across: 118 lookups for the live posts. And the ids are different in another copy of the data, so your list would be wrong there.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    post-authors.sql: posts with their authors

    List every post that is not deleted, with the username of the member who wrote it. A post by a member who left still counts: only the post's deleted_at matters.

    Return three columns: username, content and created_at (when the post was written). Any order.

  2. Task 2

    anya-comments.sql: anya's comments with their posts

    List every comment that anya wrote, next to the content of the post it is on. Every one of her comments counts.

    Return three columns: post (the post's content), comment (the comment's content) and created_at (when the comment was written). Any order.

  3. Task 3

    posts-per-author.sql: posts per author

    For each member who has at least one post that is not deleted, their username and how many such posts they have. Members who left still count if they have posts; members with no live posts do not appear.

    Return two columns: username and posts. Any order.

  4. Task 4

    mumbai-posts.sql: posts by members in Mumbai

    List the posts that are not deleted, written by members whose city is Mumbai.

    Return three columns: username, content and likes_count. Any order.

  5. Task 5 · interview question

    Interview: chai-likers.sql, members who liked a chai post

    List the usernames of the members who liked at least one post that is not deleted and whose content mentions chai in any spelling (chai, Chai, CHAI). Each member appears once, even if they liked several chai posts.

    Return one column: username. A to Z.

What you will learn

  • JOIN ... ON: put two tables side by side, pairing the rows that belong together.
  • Aliases and column names: say which table each column comes from, and rename columns that clash.
  • Chains and filters: join three tables, then filter and group on any of their columns.

More in Joins

Build "Putting names to numbers" 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.