Books from 2000 onwards

Ask a first real question: which books are recent?

What to build

A query that lists the books published in 2000 or later.

Requirements

• Return the columns title and year.
• Keep only books whose year is 2000 or later.
• Sort from the newest year down, and by title when years are equal.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER
);

Output

Columns title and year. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

year may be NULL, and a book with no year is not recent.

Example

When a test starts with:

INSERT INTO books VALUES (1, 'Clean Code', 'Robert Martin', 2008), (2, 'Atomic Habits', 'James Clear', 2018), (3, 'Dune', 'Frank Herbert', 1965);

your SQL should print:

title          year
-------------  ----
Atomic Habits  2018
Clean Code     2008
(2 rows)

Why it works

WHERE keeps a row only when its condition is true. For a NULL year the comparison is unknown, not true, so that book is left out.

Examples

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER
);
INSERT INTO books VALUES (1, 'Clean Code', 'Robert Martin', 2008), (2, 'Atomic Habits', 'James Clear', 2018), (3, 'Dune', 'Frank Herbert', 1965);

title          year
-------------  ----
Atomic Habits  2018
Clean Code     2008
(2 rows)

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER
);
INSERT INTO books VALUES (1, 'A', 'x', 2000), (2, 'B', 'y', 1999);

title  year
-----  ----
A      2000
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT title, year
FROM books
WHERE year >= 2000
ORDER BY year DESC, title;

Lesson: Databases and tables →