SQL · Question 27 · Hard

SQL question 27

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

Table Customers:
name | tier
Meera | Gold
Arjun | Silver
Sana | Gold
Kabir | Silver

How many rows does this query return?

SELECT c.name, o.amount
FROM Customers c
LEFT JOIN Orders o
  ON c.name = o.customer AND o.amount > 500;
  1. 5
  2. 4
  3. 7
  4. 3
Answer and explanation

Answer: A. 5

Explanation: Because the amount test is in the ON clause, it only limits which orders match; every customer is still kept. Meera matches 1200, Arjun matches 600 twice, Sana matches 1100, and Kabir has no match, so he appears once with NULL. 1 + 2 + 1 + 1 = 5. Putting the test in WHERE instead would drop Kabir and give 4.

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