All sample questions / SQL

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

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

CREATE TABLE bookings (id INTEGER PRIMARY KEY, flight TEXT, seat TEXT);
INSERT INTO bookings VALUES (1, 'AF100', '12A'), (2, 'AF100', '12B');
INSERT INTO bookings (id, flight) VALUES (3, 'AF100');

SELECT * FROM bookings ORDER BY id

An airline books a passenger on a flight before a seat is picked, with no default defined on that column. The statements above run, then this query. What comes back?

  1. A. 1 | AF100 | 12A 2 | AF100 | 12B
  2. B. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 | NULL
  3. C. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 | 12A
  4. D. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 |
Show the answer and why each option is right or wrong

Answer, B. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 | NULL

A. 1 | AF100 | 12A 2 | AF100 | 12B
This drops booking 3 entirely, as if leaving seat out of the column list made the insert fail rather than leave that column empty.
B. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 | NULL
Correct. seat has no DEFAULT clause, so leaving it out of the column list stores NULL for booking 3, while id and flight are set as given.
C. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 | 12A
This copies a seat from an earlier booking, when nothing in the statement supplies a seat value for the third row.
D. 1 | AF100 | 12A 2 | AF100 | 12B 3 | AF100 |
This treats the missing seat as an empty string rather than NULL, but an omitted value is stored as NULL, not as text with no characters.

A column absent from the INSERT column list is set to NULL when the table gives it no DEFAULT, regardless of what values sit in other rows of that column. Nothing about the values already stored for other bookings carries over to a row that never mentions the column.

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.