All sample questions / SQL

What does SELECT * FROM books ORDER BY id return? SQL Data Modification and Constraints

Block 8, Data Modification and Constraints, question 17 of the SQL sample

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price INTEGER);
INSERT INTO books VALUES (1, 'Atlas', 20), (2, 'Comet', 15);
INSERT INTO books (id, title) VALUES (3, 'Delta');

SELECT * FROM books ORDER BY id

A clerk lists a new title before a price has been set, with no default defined on that column. The statements above run, then this query. What comes back?

  1. A. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | NULL
  2. B. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | 0
  3. C. 1 | Atlas | 20 2 | Comet | 15
  4. D. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | 20
Show the answer and why each option is right or wrong

Answer, A. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | NULL

A. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | NULL
Correct. price has no DEFAULT clause, so leaving it out of the column list for Delta stores NULL there, while the title and id are set as given.
B. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | 0
This assumes an omitted numeric column becomes 0, but with no DEFAULT clause on price, the column is left as NULL instead.
C. 1 | Atlas | 20 2 | Comet | 15
This drops the Delta row entirely, as if leaving price out of the column list made the insert fail rather than simply leave that column empty.
D. 1 | Atlas | 20 2 | Comet | 15 3 | Delta | 20
This copies the price from the row above it, when nothing in the statement supplies a value for Delta price.

A column left out of the INSERT column list becomes NULL unless the table defines a DEFAULT for it, there is no other fallback. price here has no default, so Delta simply has no price recorded, rather than a zero or a copied value.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.