All sample questions / SQL

What does SELECT * FROM parts ORDER BY sku return? SQL Data Modification and Constraints

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

CREATE TABLE parts (sku TEXT PRIMARY KEY, bin TEXT, qty INTEGER);
INSERT INTO parts VALUES ('A1', 'R1', 5), ('A2', 'R1', 8);
INSERT INTO parts VALUES ('A3', 'R2', 3), ('A4', 'R2', 6), ('A5', 'R3', 9);

SELECT * FROM parts ORDER BY sku

A warehouse restocks several bins in one pass. The statements above run, then this query. What comes back?

  1. A. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3
  2. B. A5 | R3 | 9 A4 | R2 | 6 A3 | R2 | 3 A2 | R1 | 8 A1 | R1 | 5
  3. C. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3 A4 | R2 | 6 A5 | R3 | 9
  4. D. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3 A4 | R2 | 6
Show the answer and why each option is right or wrong

Answer, C. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3 A4 | R2 | 6 A5 | R3 | 9

A. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3
This keeps only the first row of the second, three row INSERT statement, as if a multi row INSERT only added the first pair of values listed.
B. A5 | R3 | 9 A4 | R2 | 6 A3 | R2 | 3 A2 | R1 | 8 A1 | R1 | 5
This has all five rows but sorted from Z to A instead of the ascending order ORDER BY sku gives by default.
C. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3 A4 | R2 | 6 A5 | R3 | 9
Correct. A single INSERT statement can list several rows separated by commas, and all three rows in the second statement are added along with the two from the first, for five parts sorted by sku.
D. A1 | R1 | 5 A2 | R1 | 8 A3 | R2 | 3 A4 | R2 | 6
This keeps only the first two rows of the second INSERT, dropping A5 as though the statement had stopped short.

INSERT INTO table VALUES (...), (...), (...) adds every row listed, not just the first one, in one statement. The comma separates complete rows, each in parentheses, and every one of them is inserted before the statement finishes.

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.