SQL · Question 29 · Hard

SQL question 29

Table Orders:
order_id | customer | city | amount
101 | Meera | Chennai | 1200
102 | Arjun | Bengaluru | 600
103 | Meera | Chennai | NULL
104 | Sana | Delhi | 1100
105 | Arjun | Bengaluru | 600
106 | Dev | Bhopal | 300
107 | Sana | Delhi | 400

What does this query return?

SELECT DISTINCT amount
FROM Orders o1
WHERE (SELECT COUNT(DISTINCT o2.amount)
       FROM Orders o2
       WHERE o2.amount > o1.amount) = 2;
  1. 600
  2. 1100
  3. 400
  4. 1200
Answer and explanation

Answer: A. 600

Explanation: The distinct non-NULL amounts are 1200, 1100, 600, 400 and 300. The query keeps an amount that has exactly 2 distinct larger amounts, which is the third highest: 600 (1200 and 1100 are larger). The NULL row compares as UNKNOWN and is never selected.

Previous: Table Orders: order_id | customer | city | amount 101 | Meera | Chennai | 1200… · Next: Table Orders: order_id | customer | city | amount 101 | Meera | Chennai | 1200…

Practise sql

Try a fresh set of sql questions with an explanation after every answer, free and without signing up: SQL practice. Or sit a full placement mock test.

More sql questions

More technical & programming practice

Operating Systems · Computer Networks · Software Engineering · Cloud & DevOps · GenAI Basics · Pseudo-code · C Programming · Java

LogicWiz Exams home · All practice topics · Placement mock tests · Daily 10 challenge