Skip to content
aviral gupta
Get in touch

// B2.5 · ~37 min · Beginner

Build: answer ten questions about the library

After this build you can turn a question in plain words into a one-table query, and check that the answer it gives is really the answer to the question.

Lesson 5 of 5 in B2 Querying one table

End of the module

You will be able to

  • Turn the conditions of a question into a WHERE clause, including patterns with LIKE and missing values with IS NULL
  • Answer "the first", "the newest three" and "page 2" questions with ORDER BY, LIMIT and OFFSET
  • Shape an answer with expressions, string functions and CASE, and check it with count(*)

  1. Warm-up · Activity 1 of 7

    Warm-up: the question is "Which are the three oldest books?". What decides which three rows come back?

  2. Predict · Activity 2 of 7

    Predict before you read on. The newest books are Beloved (1987), Neuromancer (1984) and Kindred (1979). What does this return?

    SELECT title FROM books ORDER BY year DESC LIMIT 1 OFFSET 1;

  3. Practice · Activity 3 of 7

    Fill in the operator so that the query lists the authors whose name contains a full stop.

    SELECT author FROM books WHERE author ____ '%.%' ORDER BY author;
    SELECT author FROM books WHERE author '%.%' ORDER BY author;

  4. Practice · Activity 4 of 7

    Match each part of a question to the part of the query that answers it.

  5. Practice · Activity 5 of 7

    Put the lines of the answer to "Which is the newest sci-fi book?" in the order SQL requires.

    1. 1.FROM books
    2. 2.WHERE genre = 'sci-fi'
    3. 3.LIMIT 1;
    4. 4.ORDER BY year DESC
    5. 5.SELECT title, year

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The library has The Hobbit. What does this return?

    SELECT count(*) FROM books WHERE title LIKE 'the%';

  7. Apply · Activity 7 of 7

    Mini-task. Ask the library two questions of your own and answer each with one query, for example: "Which members live in Berlin, by name?" and "Which book has the longest title?". Before you run each query, write down the answer you expect from the data; then compare.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Three sample questions, answered step by step

setup.sql loads the library of module B1: books, members and loans. main.sql answers three sample questions, each with the question as a comment above its query; the ten questions of the exercises work the same way. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- A. Which books have exactly one copy, A to Z?
SELECT title, copies
FROM books
WHERE copies = 1
ORDER BY title;

-- B. Which is the newest book?
SELECT title, year
FROM books
ORDER BY year DESC
LIMIT 1;

-- C. How many members live in Berlin?
SELECT count(*) AS berliners
FROM members
WHERE city = 'Berlin';

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

    title    | copies
-------------+--------
 Beloved     |      1
 Emma        |      1
 Kindred     |      1
 Neuromancer |      1
(4 rows)

  title  | year
---------+------
 Beloved | 1987
(1 row)

 berliners
-----------
         2
(1 row)
  • "Exactly one copy" becomes WHERE copies = 1; "A to Z" becomes ORDER BY title.
  • "The newest" is the first row after ORDER BY year DESC; LIMIT 1 keeps only that row.
  • "How many" is count(*), which returns one row; AS berliners names its column.
  • Check each result against the data: Ada and Linus live in Berlin, so 2 is right.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 3

Questions 1 to 4: filter, sort and page the books

Answer four questions, one query each, in this order. Q1: which are the three oldest books (title, year, oldest first)? Q2: which sci-fi books appeared after 1970 (title, year, newest first)? Q3: which books have more than one copy (title, copies, most copies first)? Q4: the catalogue lists the titles A to Z, two per page: which titles are on page 2 (title only)?

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Q1 and Q4 need ORDER BY before LIMIT; Q4 also needs OFFSET.

  2. Hint 2

    Page 2 with two titles per page skips the first two titles: OFFSET 2.

  3. Hint 3

    Q2 has two conditions: genre = 'sci-fi' AND year > 1970, then ORDER BY year DESC.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Q1. The three oldest books.
SELECT title, year
FROM books
ORDER BY year
LIMIT 3;

-- Q2. Sci-fi after 1970, newest first.
SELECT title, year
FROM books
WHERE genre = 'sci-fi' AND year > 1970
ORDER BY year DESC;

-- Q3. More than one copy, most copies first.
SELECT title, copies
FROM books
WHERE copies > 1
ORDER BY copies DESC;

-- Q4. Page 2 of the catalogue, two titles per page.
SELECT title
FROM books
ORDER BY title
LIMIT 2 OFFSET 2;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
-- Replace this query with the answers to Q1 to Q4.
SELECT * FROM books ORDER BY id;

test.sql

-- test: The four answers appear in order: oldest three, recent sci-fi, several copies, page 2
-- output:
--    title    | year
-- ------------+------
--  Emma       | 1815
--  The Hobbit | 1937
--  Dune       | 1965
-- (3 rows)
--
--     title    | year
-- -------------+------
--  Neuromancer | 1984
--  Kindred     | 1979
-- (2 rows)
--
--    title    | copies
-- ------------+--------
--  The Hobbit |      3
--  Dune       |      2
-- (2 rows)
--
--   title
-- ---------
--  Emma
--  Kindred
-- (2 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 3

Questions 5 to 7: patterns, text and eras

Answer three questions, one query each, in this order. Q5: which authors write their name with an initial, that is with a full stop (author, A to Z)? Q6: one catalogue line per book, oldest first, in a column entry: the title in capitals and the year in brackets, such as EMMA (1815). Q7: the era of each book, oldest first (title, era): 'old' before 1900, 'mid-century' before 1970, otherwise 'modern'.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Q5: LIKE '%.%' matches a full stop anywhere in the name.

  2. Hint 2

    Q6: upper(title) || ' (' || year || ')' joins the pieces; name the column with AS entry.

  3. Hint 3

    Q7: CASE checks its WHEN branches from the top and takes the first that is true, so test year < 1900 before year < 1970.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Q5. Authors who write with an initial.
SELECT author
FROM books
WHERE author LIKE '%.%'
ORDER BY author;

-- Q6. One catalogue line per book, oldest first.
SELECT upper(title) || ' (' || year || ')' AS entry
FROM books
ORDER BY year;

-- Q7. The era of each book, oldest first.
SELECT title,
       CASE
         WHEN year < 1900 THEN 'old'
         WHEN year < 1970 THEN 'mid-century'
         ELSE 'modern'
       END AS era
FROM books
ORDER BY year;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
-- Replace this query with the answers to Q5 to Q7.
SELECT * FROM books ORDER BY id;

test.sql

-- test: The three answers appear in order: authors with an initial, catalogue lines, eras
-- output:
--       author
-- -------------------
--  J. R. R. Tolkien
--  Octavia E. Butler
-- (2 rows)
--
--        entry
-- --------------------
--  EMMA (1815)
--  THE HOBBIT (1937)
--  DUNE (1965)
--  KINDRED (1979)
--  NEUROMANCER (1984)
--  BELOVED (1987)
-- (6 rows)
--
--     title    |     era
-- -------------+-------------
--  Emma        | old
--  The Hobbit  | mid-century
--  Dune        | mid-century
--  Kindred     | modern
--  Neuromancer | modern
--  Beloved     | modern
-- (6 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 3 of 3

Questions 8 to 10: members, gaps and open loans

Answer three questions, one query each, in this order. Q8: which member has no email (name)? Q9: who joined in 2026 or later, and where do they live (name, and city shown as unknown when it is missing; earliest joiner first)? Q10: how many loans are still open, that is not yet returned (one number in a column open_loans)?

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Q8 and Q10 ask about missing values: use IS NULL, never = NULL.

  2. Hint 2

    Q9: joined >= '2026-01-01', and coalesce(city, 'unknown') AS city for Margaret.

  3. Hint 3

    Q10: SELECT count(*) AS open_loans FROM loans WHERE …

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Q8. Who has no email?
SELECT name
FROM members
WHERE email IS NULL;

-- Q9. Who joined in 2026, and where do they live?
SELECT name, coalesce(city, 'unknown') AS city
FROM members
WHERE joined >= '2026-01-01'
ORDER BY joined;

-- Q10. How many loans are still open?
SELECT count(*) AS open_loans
FROM loans
WHERE returned_on IS NULL;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
-- Replace this query with the answers to Q8 to Q10.
SELECT * FROM members ORDER BY id;

test.sql

-- test: The three answers appear in order: Linus, the 2026 members, three open loans
-- output:
--  name
-- -------
--  Linus
-- (1 row)
--
--    name   |  city
-- ----------+---------
--  Linus    | Berlin
--  Margaret | unknown
-- (2 rows)
--
--  open_loans
-- ------------
--           3
-- (1 row)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

LIMIT before ORDER BY

SELECT title, year
FROM books
LIMIT 3
ORDER BY year;

What psql prints

ERROR:  syntax error at or near "ORDER"

Why, and the fix

The clauses have a fixed order: SELECT, FROM, WHERE, ORDER BY, LIMIT, OFFSET. PostgreSQL reads LIMIT 3 and does not expect ORDER BY after it. Move ORDER BY year above LIMIT 3; the sort then happens first, and LIMIT keeps the three oldest.

Filtering on a CASE alias

SELECT title,
       CASE WHEN year < 1900 THEN 'old' ELSE 'new' END AS era
FROM books
WHERE era = 'old';

What psql prints

ERROR:  column "era" does not exist

Why, and the fix

era is a name in the result, and WHERE works on the table's rows before the select list is computed. Filter on the column the CASE reads: WHERE year < 1900. ORDER BY era would work, since ORDER BY runs after the select list.

A count next to an ordinary column

SELECT name, count(*)
FROM members
WHERE email IS NULL;

What psql prints

ERROR:  column "members.name" must appear in the GROUP BY clause or be used in an aggregate function

Why, and the fix

count(*) turns all the rows into one number, and PostgreSQL cannot tell which name should stand next to it. Ask two questions instead: SELECT name … for the list, and SELECT count(*) … for the number. Module B3 shows how GROUP BY combines the two.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Take the question apart before you write

A question in plain words hides the parts of a query. "Which sci-fi books appeared after 1970, newest first?" names the table (books), the rows (genre = 'sci-fi' AND year > 1970), the columns you need to show (title, year) and the order (ORDER BY year DESC). "How many" means count(*). "Has no email" means IS NULL. "Contains a full stop" means LIKE '%.%'. Write the parts in the order SQL needs: SELECT, FROM, WHERE, ORDER BY, LIMIT. Answer one question per query, run it, and only then go on to the next.

"The first three" needs an order

LIMIT 3 keeps three rows, but which three depends on the order, and without ORDER BY the order is not promised. "The three oldest books" is ORDER BY year LIMIT 3; "the newest" is ORDER BY year DESC LIMIT 1. For pages, OFFSET skips rows first: with two titles per page, page 2 is ORDER BY title LIMIT 2 OFFSET 2, and page n skips (n - 1) × 2. When two rows tie on the sort key, add a second key, such as title, so the answer does not change from run to run. Remember that NULL sorts as larger than any value: it comes last in ascending order and first with DESC.

Check the answer, not only that it runs

A query that runs can still answer another question. The library is small, so compare the result with the data: which rows should come back, and why? Missing values are where answers go wrong: Linus has no email and Margaret no city, so a condition such as city <> 'Munich' quietly leaves Margaret out. count(*) checks a size: it returns one row with one number. It cannot be mixed with ordinary columns, as in SELECT name, count(*); that needs GROUP BY, which comes in module B3. Until then, list the rows in one query and count them in another.

Sources

Last reviewed September 30, 2026