Given the following Employee relational database: What is the result of the…
2023
Given the following Employee relational database:
What is the result of the following query?
SELECT MAX(Salary)
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);- A.
3000
- B.
4000
- C.
7000
- D.
8000
Attempted by 3 students.
Show answer & explanation
Correct answer: C
Concept
A subquery placed inside a WHERE clause is evaluated first, independently of the outer query, and its result is substituted into the outer condition as a single scalar value. When that condition is Salary < (SELECT MAX(Salary) FROM Employee), it discards only the row(s) holding the table's overall highest salary; applying MAX() to what remains therefore returns the second-highest distinct salary in the table — the classic 'second-highest value' idiom in SQL.
Applying to This Query
The Employee table holds these salaries:
ID | First Name | Salary |
|---|---|---|
1 | Ben | 8000 |
2 | Joe | 4000 |
3 | Mark | 7000 |
4 | Steve | 3000 |
5 | John | 7000 |
Inner subquery: SELECT MAX(Salary) FROM Employee scans all five salaries and returns 8000 (Ben's salary), the overall maximum.
Substitution: the outer query becomes SELECT MAX(Salary) FROM Employee WHERE Salary < 8000.
Filtering: only rows with Salary strictly less than 8000 remain — Joe (4000), Mark (7000), Steve (3000), and John (7000).
Aggregation: MAX() is applied to this filtered set {4000, 7000, 3000, 7000}, giving 7000.
Cross-Check
Sorting all five salaries in descending order gives 8000, 7000, 7000, 4000, 3000. Excluding the table's own maximum (8000) and taking the next distinct value confirms 7000 — consistent with the filtered-MAX() result above.