Add the new arrivals – Databases Practice Problem

Change the data, then ask about it: the full cycle in one exercise.

What to build

Statements that add two books to the library and then list every book by year.

Requirements

Insert The Pragmatic Programmer by Hunt and Thomas from 1999, and Deep Work by Cal Newport from 2016.
Then return title and year for every book.
Sort by year, from oldest, and then by title.

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

The table may already hold any books. A book with no year counts as the smallest and comes first.

Example

When a test starts with:

INSERT INTO books VALUES (1, 'Clean Code', 'Robert Martin', 2008);

your SQL should print:

title                     year
------------------------  ----
The Pragmatic Programmer  1999
Clean Code                2008
Deep Work                 2016
(3 rows)

Why it works

The INSERT runs before the SELECT, so the query sees the new rows. SQLite sorts NULL before every value, so a book with no year comes first.

Examples

Example 1 · Input

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

Expected output

title                     year
------------------------  ----
The Pragmatic Programmer  1999
Clean Code                2008
Deep Work                 2016
(3 rows)

Example 2 · Input

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

Expected output

title                     year
------------------------  ----
The Pragmatic Programmer  1999
Deep Work                 2016
(2 rows)

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

Hint 1

Name the three columns in the INSERT and leave out `id`: SQLite gives the next number by itself.

Hint 2

One INSERT can add both books, with two bracketed rows separated by a comma.

Hint 3

Then sort by `year` and then by `title`.

Solution

Show one correct solution (try it yourself first)

INSERT INTO books (title, author, year) VALUES
  ('The Pragmatic Programmer', 'Hunt and Thomas', 1999),
  ('Deep Work', 'Cal Newport', 2016);

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

Lesson: Databases and tables →