How do you select a customer's total orders using a scalar subquery in the SELECT list?

• A. SELECT c.id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders FROM customers c;
• B. SELECT c.id, (SELECT SUM(order_amount) FROM orders o WHERE o.customer_id = c.id) AS total_orders FROM customers c;
• C. SELECT c.id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers;
• D. SELECT c.id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders FROM customers;

Answer: (A)

A scalar correlated subquery in the SELECT list lets you compute a single value for each row of the outer query by referring to that row’s columns. In this case, you want the number of orders for each customer, so you count the rows in orders that belong to the current customer.

The best choice selects each customer id from the customers table (properly aliased) and, for that customer, runs a subquery that counts how many orders have the same customer_id. The correlation comes from referencing the outer table’s id (c.id) inside the subquery, so the count is computed per customer. The result is labeled total_orders, producing a per-customer total.

Other options fail for specific reasons: one would sum order amounts instead of counting orders, giving total value rather than total count. Another omits aliasing the outer table, which makes the reference to c.id invalid. The remaining option is missing the alias for the computed column, which is a presentation issue rather than a functional one, but it still doesn’t match the stated pattern.