Tables learners(id int primary key, name text) and marks(id int primary key, learner_id int references learners(id), subject text, score int). The pass mark is 40.
Return id and name of learners who have at least one mark and no score below 40. Order by id.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: id, name.
Example
Input
create table learners (id int primary key, name text not null);
create table marks (id int primary key, learner_id int not null references learners(id), subject text not null, score int not null);
insert into learners values (1,'Aarav'),(2,'Diya'),(3,'Kabir');
insert into marks values (1,1,'DSA',72),(2,1,'OS',40),(3,2,'DSA',91),(4,2,'DBMS',35);Output
1|AaravAarav passed both (40 counts as a pass). Diya failed DBMS. Kabir has no marks.
Show hints
- "No failing mark" is a NOT EXISTS.
- Remember the "at least one mark" condition — NOT EXISTS alone also matches learners with no marks.
Show the explanation
Combine two correlated subqueries: EXISTS (select 1 from marks where learner_id = l.id) ensures the learner sat at least one exam, and NOT EXISTS (select 1 from marks where learner_id = l.id and score < 40) rules out any failure. The GROUP BY alternative is HAVING min(score) >= 40, which also handles both conditions because learners without marks never form a group.
Original UniCircle question. Last reviewed 22 Oct 2026.