Skip to content
aviral gupta
Get in touch

// B2.1 · ~30 min · Beginner

SELECT and WHERE

After this lesson you can answer questions about one table: pick and rename the columns you need, and keep only the rows that match a condition.

Lesson 1 of 5 in B2 Querying one table

Start of the module

You will be able to

  • Choose the columns of a result, rename them with AS, and remove duplicate rows with DISTINCT
  • Keep only matching rows with WHERE, the comparison operators, BETWEEN and IN
  • Combine conditions with AND, OR and NOT, and add parentheses where precedence would mislead

  1. Warm-up · Activity 1 of 7

    Warm-up: SELECT title FROM books WHERE year < 1900; which rows end up in the result?

  2. Predict · Activity 2 of 7

    Predict before you read on. The books are from 1815, 1937, 1965, 1979, 1984 and 1987. What does this return?

    SELECT count(*) FROM books WHERE year BETWEEN 1965 AND 1984;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the query lists the classics and the fantasy books.

    SELECT title FROM books WHERE genre ____ ('classic', 'fantasy') ORDER BY title;
    SELECT title FROM books WHERE genre ('classic', 'fantasy') ORDER BY title;

  4. Practice · Activity 4 of 7

    Match each condition to what it keeps.

  5. Practice · Activity 5 of 7

    Put the clauses of this query in the order SQL requires.

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

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The library has three sci-fi books (2, 1 and 1 copies) and one fantasy book (3 copies). What does this return?

    SELECT count(*) FROM books
WHERE genre = 'sci-fi' OR genre = 'fantasy' AND copies > 1;

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example, write two queries: the title and year of the books published before 1950 that are not classics, and the names of the members in Berlin who joined in 2025. Run them, then change one condition and predict the new result before you run again.

    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 questions to the library

setup.sql loads the books and members of the library you built in module B1. main.sql asks three questions: which sci-fi books are older than 1980, which genres exist, and which fantasy or sci-fi books appeared between 1900 and 1980. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Sci-fi books from before 1980, with the year renamed.
SELECT title, year AS published
FROM books
WHERE genre = 'sci-fi' AND year < 1980
ORDER BY year;

-- 2. Every genre, once.
SELECT DISTINCT genre FROM books ORDER BY genre;

-- 3. A range and a list in one condition.
SELECT title, genre, copies
FROM books
WHERE year BETWEEN 1900 AND 1980
  AND genre IN ('fantasy', 'sci-fi')
ORDER BY title;

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');

Run it with

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

Output

  title  | published
---------+-----------
 Dune    |      1965
 Kindred |      1979
(2 rows)

  genre
---------
 classic
 fantasy
 fiction
 sci-fi
(4 rows)

   title    |  genre  | copies
------------+---------+--------
 Dune       | sci-fi  |      2
 Kindred    | sci-fi  |      1
 The Hobbit | fantasy |      3
(3 rows)
  • AS published renames the column in the result; the table still calls it year.
  • Neuromancer is sci-fi too, but 1984 is not < 1980, so the first query leaves it out.
  • DISTINCT turns six rows into four: sci-fi appears once.
  • BETWEEN 1900 AND 1980 drops Emma (1815); IN drops Beloved (fiction).
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 2

Sci-fi before 1980, and the single-copy genres

Write two queries. First: the title and the author, labelled writer, of every sci-fi book published before 1980, oldest first. Second: each genre that has a book with exactly one copy, listed once, in alphabetical order.

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

    Two conditions joined by AND: genre = 'sci-fi' and year < 1980.

  2. Hint 2

    author AS writer renames the column in the result.

  3. Hint 3

    SELECT DISTINCT genre … WHERE copies = 1 ORDER BY genre; lists each genre once.

Show a solution

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

SELECT title, author AS writer
FROM books
WHERE genre = 'sci-fi' AND year < 1980
ORDER BY year;

SELECT DISTINCT genre
FROM books
WHERE copies = 1
ORDER BY genre;
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 and members.
SELECT * FROM books ORDER BY id;

test.sql

-- test: The two queries print the sci-fi books with their writers, then the single-copy genres
-- output:
--   title  |      writer
-- ---------+-------------------
--  Dune    | Frank Herbert
--  Kindred | Octavia E. Butler
-- (2 rows)
--
--   genre
-- ---------
--  classic
--  fiction
--  sci-fi
-- (3 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');

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 2

Fix the invitation list

The starter should list the members in Berlin or Munich who joined on or after 1 January 2026, but it also lists Ada, who joined in 2025. Find out why and fix the condition, keeping the columns name, city and joined and the order by id. Try both repairs: parentheses, and IN.

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

    AND binds before OR. Which part of the condition does joined >= … belong to?

  2. Hint 2

    Put the two cities in brackets: (city = 'Berlin' OR city = 'Munich') AND …

  3. Hint 3

    Or replace the two comparisons with city IN ('Berlin', 'Munich').

Show a solution

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

-- Members in Berlin or Munich who joined in 2026 or later.
SELECT name, city, joined
FROM members
WHERE city IN ('Berlin', 'Munich') AND joined >= '2026-01-01'
ORDER BY id;
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

-- Members in Berlin or Munich who joined in 2026 or later.
SELECT name, city, joined
FROM members
WHERE city = 'Berlin' OR city = 'Munich' AND joined >= '2026-01-01'
ORDER BY id;

test.sql

-- test: Only Linus is listed: Berlin, joined 2026-02-10
-- output:
--  name  |  city  |   joined
-- -------+--------+------------
--  Linus | Berlin | 2026-02-10
-- (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');

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

Text without quotes

SELECT title FROM books WHERE genre = sci-fi;

What psql prints

ERROR:  column "sci" does not exist

Why, and the fix

Without quotes, sci-fi is read as an expression: a column called sci minus a column called fi. There is no column sci, so that is what the error names. Text values always go in single quotes: WHERE genre = 'sci-fi'.

Using a column alias in WHERE

SELECT title, year AS published
FROM books
WHERE published > 1980;

What psql prints

ERROR:  column "published" does not exist

Why, and the fix

AS names a column of the result, and the result is built from the rows that WHERE has already kept. WHERE looks at the table, where the column is still called year. Filter on the column itself: WHERE year > 1980. ORDER BY, which runs last, may use the alias.

== from another language

SELECT title FROM books WHERE genre == 'sci-fi';

What psql prints

ERROR:  operator does not exist: text == unknown

Why, and the fix

In SQL, equality is a single =. PostgreSQL has no == operator for text, and says so: it looked for an operator called == between a text column and a quoted value of type unknown, and found none. Write genre = 'sci-fi'. Not equal is <> (or !=).

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

The select list decides the columns

SELECT title, year FROM books returns two columns, in that order; SELECT * returns all of them. Each item may also be an expression, such as copies * 2. AS gives a result column a new name: SELECT year AS published. The name belongs to the result only, so WHERE, which looks at the table’s rows, cannot use it: WHERE published > 1980 fails with column "published" does not exist. SELECT DISTINCT genre removes duplicate rows from the result, after the select list is built: two rows count as duplicates only if every selected column is equal. DISTINCT does not sort; add ORDER BY when the order matters.

WHERE keeps the rows for which the condition is true

WHERE checks every row and keeps it only when its condition is true. The comparison operators are = (equal), <> or != (not equal), <, >, <= and >=. They work on numbers, on text (compared exactly, so 'Sci-Fi' is not 'sci-fi') and on dates ('2026-01-01' in quotes). year BETWEEN 1937 AND 1979 is short for year >= 1937 AND year <= 1979: both ends are included. genre IN ('sci-fi', 'fantasy') is short for genre = 'sci-fi' OR genre = 'fantasy', and NOT IN keeps the rest. A chain such as 1900 < year < 2000 is not allowed; write year > 1900 AND year < 2000, or BETWEEN.

AND binds before OR: use parentheses

AND, OR and NOT combine conditions. Like × before + in arithmetic, NOT binds first, then AND, then OR. So city = 'Berlin' OR city = 'Munich' AND joined >= '2026-01-01' means Berlin (any date) or (Munich and 2026), which is rarely what the question asked. Parentheses make the grouping explicit: (city = 'Berlin' OR city = 'Munich') AND joined >= '2026-01-01'. IN often removes the need for them: city IN ('Berlin', 'Munich') AND joined >= '2026-01-01'. When a condition mixes AND and OR, write the parentheses even where they change nothing: the next reader does not have to remember the precedence table.

Sources

Last reviewed September 30, 2026