All sample questions / SQL

What does SELECT q FROM queries WHERE q LIKE '1__\% sure' ESCAPE '\' return? SQL Strings, Numbers and Type Conversion

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

CREATE TABLE queries (q TEXT);
INSERT INTO queries VALUES ('100% sure'), ('not sure'), ('50% maybe');

SELECT q FROM queries WHERE q LIKE '1__\% sure' ESCAPE '\'

What does this query return?

  1. A. 100% sure 50% maybe
  2. B. not sure
  3. C. 100% sure
  4. D. (no rows)
Show the answer and why each option is right or wrong

Answer, C. 100% sure

A. 100% sure 50% maybe
This includes '50% maybe' as well, but it starts with 5 rather than 1, and it ends in 'maybe' rather than 'sure', so it fails the pattern on both counts.
B. not sure
This picks 'not sure' instead, but that string does not start with the digit 1 followed by two more characters and a percent sign at all.
C. 100% sure
Correct. The two underscores each match one character, 0 and 0, the escaped percent matches the literal percent sign, and the trailing text matches exactly, so only '100% sure' fits the pattern.
D. (no rows)
This assumes no row matches, but '100% sure' fits the pattern exactly, one 1, two wildcard characters covering the remaining zeros, then a literal percent sign and the word sure.

Underscore in LIKE matches exactly one character each, so two consecutive underscores require exactly two characters there, while an escaped percent sign in the same pattern still means a literal percent, not a wildcard.

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.