All sample questions / SQL

What does SELECT machines.tag, thresholds.label FROM machines JOIN thresholds ON ... return? SQL Joins

Block 2, Joins, question 11 of the SQL sample

CREATE TABLE machines (id INTEGER, tag TEXT, hours_run INTEGER);
INSERT INTO machines VALUES
  (1, 'M1', 120),
  (2, 'M2', 300),
  (3, 'M3', 480);
CREATE TABLE thresholds (label TEXT, max_hours INTEGER);
INSERT INTO thresholds VALUES
  ('Green', 200),
  ('Yellow', 400),
  ('Red', 600);

SELECT machines.tag, thresholds.label FROM machines JOIN thresholds ON machines.hours_run <= thresholds.max_hours ORDER BY machines.tag, thresholds.label

What does this query return?

  1. A. M1 | Green M1 | Red M1 | Yellow M2 | Red M2 | Yellow M3 | Red
  2. B. M2 | Green M3 | Green M3 | Yellow
  3. C. (no rows)
  4. D. M1 | Green M2 | Yellow M3 | Red
Show the answer and why each option is right or wrong

Answer, A. M1 | Green M1 | Red M1 | Yellow M2 | Red M2 | Yellow M3 | Red

A. M1 | Green M1 | Red M1 | Yellow M2 | Red M2 | Yellow M3 | Red
Correct. M1 has run the fewest hours so it fits under all three thresholds, M2 fits under Yellow and Red, and M3, the most used, only fits under Red.
B. M2 | Green M3 | Green M3 | Yellow
This flips the inequality direction, matching thresholds whose max_hours is at or below the machine's hours_run instead of at or above, the opposite of a maintenance lookup.
C. (no rows)
This assumes hours_run must exactly equal max_hours, but the condition is an inequality, and none of the machines have run for exactly a threshold value.
D. M1 | Green M2 | Yellow M3 | Red
This assumes each machine can only match the tightest threshold it fits into, but the ON condition is satisfied by every threshold whose max_hours is at least the machine's hours_run, not only the lowest one.

The less a machine has been used, the more maintenance thresholds it satisfies under a <= comparison, since a small hours_run clears more of the max_hours limits. This is the same shape of inequality join as matching a score against a set of grade cutoffs.

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.