All sample questions / SQL

What does SELECT city FROM temps WHERE ABS(change) > 1 ORDER BY city return? SQL Strings, Numbers and Type Conversion

Block 7, Strings, Numbers and Type Conversion, question 3 of the SQL sample

CREATE TABLE temps (city TEXT, change REAL);
INSERT INTO temps VALUES ('Oslo', -6.0), ('Cairo', 2.0), ('Quito', -0.5);

SELECT city FROM temps WHERE ABS(change) > 1 ORDER BY city

What does this query return?

  1. A. Cairo Oslo Quito
  2. B. Oslo
  3. C. Cairo Oslo
  4. D. Quito
Show the answer and why each option is right or wrong

Answer, C. Cairo Oslo

A. Cairo Oslo Quito
This includes Quito, but its change is only 0.5 in absolute value, which does not exceed 1.
B. Oslo
This keeps only Oslo, but Cairo's change of 2.0 also has an absolute value above 1 and belongs in the result too.
C. Cairo Oslo
Correct. ABS(change) is 6.0 for Oslo and 2.0 for Cairo, both above 1, while Quito's 0.5 is not, so Oslo and Cairo qualify, sorted alphabetically.
D. Quito
This keeps only Quito, but its small change is exactly the one that fails the threshold, while the two larger changes pass it.

ABS lets a single comparison catch large changes in either direction, a threshold on ABS(column) treats a big negative change the same as a big positive one.

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.