SQL Quest › SQL Interview Questions › Subqueries & CTEs
A Subquery That Returns One Value
HR wants everyone paid above the company average.
Show name, department, salary for employees earning more than the average salary. Order by salary descending, then name.
The new idea: you can put a whole SELECT inside your WHERE. (SELECT AVG(salary) FROM employees) runs first, produces a single number, and your comparison then works against that number just as if you had typed it.
Why not compute the average yourself and paste it in? Because the moment someone gets a raise your number is wrong and the query still runs, cheerfully returning the wrong answer. The subquery recomputes every time.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
employees
| emp_id | name | department | position | salary | hire_date | manager_id | performance_rating |
|---|---|---|---|---|---|---|---|
| 1 | Alice Johnson | Engineering | Senior Developer | 95000 | 2019-03-15 | 5 | 4.5 |
| 2 | Bob Smith | Engineering | Developer | 75000 | 2020-06-01 | 1 | 3.8 |
| 3 | Carol Williams | Marketing | Marketing Manager | 85000 | 2018-09-20 | NULL | 4.2 |
Expected output: 22 rows — everyone above 72,760
Hint
SELECT, Subquery, WHERE, and open the hint there if you stall.Concepts
SELECT Subquery WHERE
Practise the topic: SQL practice questions · CTE practice
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test