You want to list all employees with their highest sale amount. Which query correctly uses a scalar subquery?

hard📝 Application Q8 of 15

PostgreSQL - Subqueries in PostgreSQL

ASELECT e.name, (SELECT MAX(amount) FROM sales WHERE employee_id = e.id) AS max_sale FROM employees e;

BSELECT e.name, MAX(amount) FROM sales WHERE employee_id = e.id FROM employees e;

CSELECT e.name, (SELECT amount FROM sales) AS max_sale FROM employees e;

DSELECT e.name, MAX(amount) FROM sales GROUP BY e.name;

Step-by-Step Solution

Solution:

Step 1: Understand the requirement
We want each employee's highest sale amount, so we need a scalar subquery per employee.
Step 2: Analyze options
SELECT e.name, (SELECT MAX(amount) FROM sales WHERE employee_id = e.id) AS max_sale FROM employees e; correctly uses a correlated scalar subquery to get MAX(amount) per employee.
Final Answer:
SELECT e.name, (SELECT MAX(amount) FROM sales WHERE employee_id = e.id) AS max_sale FROM employees e; -> Option A
Quick Check:
Correlated scalar subquery returns one value per employee [OK]

Quick Trick: Use correlated scalar subqueries to get per-row single values [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free