Sometimes you need to ask whether a related row exists rather than return the row. EXISTS is true if its subquery returns at least one row. The selected value does not matter, so SELECT 1 makes the intent clear. Correlate the subquery to the current outer row through the candidate key.
The requirement here has two parts: at least one evaluation exists, and no evaluation is missing or below a supplied threshold. "All are acceptable" alone is true for an empty set, so the separate EXISTS guard matters.
Follow the data
Translate "every evaluation is acceptable" into "there is no unacceptable evaluation". A failing evaluation has NULL score or score < :minimum. NOT EXISTS around that condition checks the universal requirement without duplicating candidate rows. Including e.candidate_id = c.id ties the evidence to the current candidate.
NOT IN is easy to misuse with nullable subqueries. If the list contains NULL, a comparison against a nonmatching value may be unknown rather than true. NOT EXISTS lets you state the matching relationship and missing-value policy explicitly. A NULL candidate_id does not equal any actual customer id and therefore belongs to no candidate.
The build uses the named parameter :minimum. Binding passes a value separately from SQL syntax. Do not put quotes around the placeholder: ':minimum' is literal text. Do not concatenate untrusted input into the query. A malicious-looking string bound as a value remains data and cannot introduce an OR clause or another statement.
Binding protects values, not SQL identifiers. A table name, column name or sort direction usually cannot be supplied as a value parameter. Applications need a fixed query shape or an explicit allowlist for those choices. This lesson's schema and ordering are fixed, so only the numeric threshold varies.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE candidates(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE evaluations(id INTEGER PRIMARY KEY, candidate_id INTEGER REFERENCES candidates(id), score INTEGER);
INSERT INTO candidates VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D');
INSERT INTO evaluations VALUES (1,1,80),(2,1,90),(3,2,79),(4,3,NULL),(5,NULL,100);
SELECT c.id, c.name
FROM candidates AS c
WHERE EXISTS (SELECT 1 FROM evaluations AS e WHERE e.candidate_id = c.id)
AND NOT EXISTS (
SELECT 1 FROM evaluations AS e
WHERE e.candidate_id = c.id AND (e.score IS NULL OR e.score < 80)
)
ORDER BY c.id;Diagnose the common failure
An uncorrelated EXISTS answers whether anyone has an evaluation. It can admit candidates with none of their own. Forgetting IS NULL in the bad-score condition admits missing measurements because NULL < minimum is unknown. Hardcoding 80 passes the first public sample and fails fixtures using another threshold.
Try a change before building
Change the sample threshold to 90 and inspect the accepted candidates. Add a candidate whose evaluations are exactly equal to the threshold. Then add a NULL score for that same candidate and explain why they must disappear.
Move into practice
Express existence and universal conditions without NULL-sensitive NOT IN or duplicated rows. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.