SQL from zero · Chapter 1 of 37

A first look at the data

By the end of this chapter you can ask MingleSphere's database for exactly the rows and columns you need, sorted the way you want. 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
  • Free chapter

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.

Why you need this

You answered Vikram's post "Hiring our first data engineer. DM me.", and today is your first day at MingleSphere, a small social network. Everything its 24 members do (posts, likes, comments, follows, messages) lives in one PostgreSQL database.

Rohan, the data analyst, has questions every day: who our members are, who lives in Bengaluru, who joined most recently. Until now he asked Anya, the lead backend engineer, every time. The other way is to export the whole members table to a spreadsheet, then delete columns, filter and sort by hand, and do it all again tomorrow when the data has changed. A query does that work in one line, and you can run it again any time.

The lesson

Meet the database

Type psql in the terminal. It opens your own copy of MingleSphere's database, and \dt lists its tables:

         List of relations
 Schema │   Name    │ Type  │ Owner
────────┼───────────┼───────┼───────
 public │ comments  │ table │ app
 public │ follows   │ table │ app
 public │ likes     │ table │ app
 public │ messages  │ table │ app
 public │ post_tags │ table │ app
 public │ posts     │ table │ app
 public │ reports   │ table │ app
 public │ tags      │ table │ app
 public │ users     │ table │ app
(9 rows)

\d users shows one table's columns (username, display_name, city, status, is_verified, created_at and more), and \q leaves psql. Your answers go in the .sql files in the editor; lab run members.sql runs one so you can see what it returns.

SELECT and FROM

A query names the columns it wants, then the table they come from:

SELECT name FROM tags;
    name
─────────────
 chai
 cricket
 music
 travel
 food
 startups
 databases
 photography
 gaming
 books
 fitness
 design
 poetry
(13 rows)

Separate several columns with commas: SELECT username, city FROM users. Spell each name exactly as the table does, or Postgres stops you:

minglesphere=> SELECT usename FROM users;
ERROR:  column "usename" does not exist
LINE 1: SELECT usename FROM users;
               ^
HINT:  Perhaps you meant to reference the column "users.username".

WHERE

WHERE keeps only the rows that match a condition:

SELECT username, city FROM users WHERE status = 'busy';
 username │   city
──────────┼───────────
 meera    │ Chennai
 dev      │ Delhi
 manav    │ Ahmedabad
(3 rows)

Text goes in single quotes. Double quotes mean a column name, so WHERE city = "Bengaluru" fails with column "Bengaluru" does not exist. The comparison is exact, too: WHERE city = 'bengaluru' returns 0 rows.

Join two conditions with AND (both must be true) or OR (either one). A true or false column such as is_verified is a condition already: WHERE is_verified keeps the verified members.

ORDER BY and LIMIT

Rows come back in no particular order unless you ask for one. ORDER BY sorts (A to Z, oldest first; add DESC to reverse it), and LIMIT keeps the first few. Press Next to watch a query find the three newest pending reports:

SELECT username, created_at FROM users
ORDER BY created_at
LIMIT 3;
 username  │        created_at
───────────┼───────────────────────────
 minglebot │ 2026-01-01 00:00:00+05:30
 anya      │ 2026-01-02 09:15:00+05:30
 rohan     │ 2026-01-02 10:40:00+05:30
(3 rows)

The order of the parts is fixed: LIMIT after ORDER BY. Swap them and you get syntax error at or near "ORDER".

Your turn

Your four tasks are at the top, one file each: Rohan's three questions, then Vikram's interview question. Read each goal carefully: "every member" means everyone, including the two who have left the site. Press Check when you are ready: it runs your files on fresh copies of the data, so nothing you try in psql can break it.

What you build: 4 tasks

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

  1. Task 1

    members.sql: every member

    List every member of MingleSphere with their username, display name and city. Everyone counts: members who have left the site still have a row, and members with no city show an empty city.

    Return three columns: username, display_name and city. Any order.

  2. Task 2

    bengaluru.sql: members in Bengaluru

    List the members whose city is Bengaluru.

    Return two columns: username and display_name. Any order.

  3. Task 3

    newest-members.sql: the 5 newest members

    Find the 5 members who joined most recently: created_at says when each member joined. Count everyone, including members who have left the site (one of the five newest has).

    Return two columns: username and created_at. Newest first.

  4. Task 4 · interview question

    Interview: online-verified.sql, verified members who are online

    Find the members who are verified (is_verified is true) and whose status is online right now.

    Return one column: username. Sort the usernames A to Z.

What you will learn

  • psql: opening the database and seeing what is in it.
  • SELECT and FROM: choosing the columns and the table.
  • WHERE: keeping only the rows that match.
  • ORDER BY and LIMIT: sorting the rows, and keeping the first few.

More in First queries

Build "A first look at the data" 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.