All sample questions / SQL

What does SELECT name FROM catalog WHERE price BETWEEN 10 AND 50 ORDER BY name return? SQL Selecting, Filtering and Sorting

Block 1, Selecting, Filtering and Sorting, question 4 of the SQL sample

CREATE TABLE catalog (id INTEGER, name TEXT, price INTEGER);
INSERT INTO catalog VALUES
  (1, 'mug', 10),
  (2, 'plate', 50),
  (3, 'bowl', 9),
  (4, 'jug', 51);

SELECT name FROM catalog WHERE price BETWEEN 10 AND 50 ORDER BY name

What does this query return?

  1. A. bowl mug plate
  2. B. mug plate
  3. C. (no rows)
  4. D. jug mug plate
Show the answer and why each option is right or wrong

Answer, B. mug plate

A. bowl mug plate
This keeps every price at or below the upper bound and forgets to enforce the lower bound, letting bowl in even though its price is below 10. BETWEEN checks both limits together.
B. mug plate
Correct. BETWEEN includes both boundary values, so mug at 10 and plate at 50 both qualify, while bowl at 9 and jug at 51 fall outside the range.
C. (no rows)
This treats BETWEEN as excluding its two boundary values, dropping mug and plate because their price sits exactly on a limit. BETWEEN is inclusive on both ends, a value equal to either bound still counts as a match.
D. jug mug plate
This keeps every price at or above the lower bound and forgets to cap it at the upper bound, letting jug in even though its price is above 50. BETWEEN checks both limits together.

BETWEEN low AND high is shorthand for a column being greater than or equal to low and, at the same time, less than or equal to high, both boundary values are included in the match. Dropping either half of that combined check, or excluding the boundary values themselves, changes which rows pass.

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.