Ask a table for everything it holds, in a fixed order.
What to build
A query that shows every column of every book.
Requirements
- Return all four columns, in the table's own order.
- Sort the rows by
id.
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 id, title, author and year, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
The table may be empty, and year may be NULL.
Example
When a test starts with:
INSERT INTO books VALUES (1, 'Clean Code', 'Robert Martin', 2008), (2, 'Atomic Habits', 'James Clear', 2018);
your SQL should print:
id title author year
-- ------------- ------------- ----
1 Clean Code Robert Martin 2008
2 Atomic Habits James Clear 2018
(2 rows)
Why it works
* stands for every column, and ORDER BY id fixes the order. Without ORDER BY, a DBMS may return rows in any order it likes.
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);
id title author year -- ------------- ------------- ---- 1 Clean Code Robert Martin 2008 2 Atomic Habits James Clear 2018 (2 rows)
CREATE TABLE books ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, year INTEGER ); INSERT INTO books VALUES (3, 'Dune', 'Frank Herbert', 1965), (1, 'Emma', 'Jane Austen', 1815);
id title author year -- ----- ------------- ---- 1 Emma Jane Austen 1815 3 Dune Frank Herbert 1965 (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
`SELECT *` returns every column.
Hint 2
`ORDER BY id` puts the rows in order of their id.
Solution
Show one correct solution (try it yourself first)
SELECT * FROM books ORDER BY id;