[Solved] Given tables orders(order_id, customer_id, amount) and customers(customer_id, name), what does this query return?SELECT c.name FROM customers c, orders o WHERE o.customer_id = c.customer_id AND o.... | SQL

SQL - Subqueries

Given tables orders(order_id, customer_id, amount) and customers(customer_id, name), what does this query return?

SELECT c.name FROM customers c, orders o WHERE o.customer_id = c.customer_id AND o.amount > (SELECT AVG(amount) FROM orders o2 WHERE o2.customer_id = c.customer_id);

ACustomers with orders above the average order amount of their own orders

BCustomers whose order amount is greater than the average order amount of all customers

CCustomers whose order amount is greater than their own average order amount

DCustomers with orders above the average order amount of all orders

Step-by-Step Solution

Solution:

Step 1: Analyze the correlated subquery
The subquery calculates the average order amount for each customer by matching o2.customer_id = c.customer_id.
Step 2: Understand the WHERE condition
The outer query selects customers (via join with orders) where the order amount (o.amount) is greater than their own average order amount.
Final Answer:
Customers with orders above the average order amount of their own orders -> Option A
Quick Check:
Correlated subquery filters per customer average [OK]

Quick Trick: Correlated subquery filters per outer row value [OK]

Common Mistakes:

MISTAKES

Confusing overall average with per-customer average
Ignoring correlation condition
Assuming subquery runs once

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given tables orders(order_id, customer_id, amount) and customers(customer_id, name), what does this query return?

Step 1: Analyze the correlated subquery

Step 2: Understand the WHERE condition

Final Answer:

Quick Check:

Want More Practice?