sqlintermediate10 minutes

Predict the Output of an SQL Query with Subqueries and Joins

Analyze the given SQL query involving JOINs and a correlated subquery, and predict the final output based on a provided sample database schema and data.

Challenge prompt

Given the following schema and data for two tables, `employees` and `departments`, analyze the accompanying SQL query and predict its output. Tables: employees: | id | name | department_id | salary | |----|---------|---------------|--------| | 1 | Alice | 10 | 70000 | | 2 | Bob | 20 | 48000 | | 3 | Charlie | 10 | 55000 | | 4 | Diana | 30 | 62000 | | 5 | Edward | 20 | 49000 | departments: | id | name | |----|----------------| | 10 | Sales | | 20 | Engineering | | 30 | Human Resources| SQL Query: SELECT d.name AS department, e.name AS employee, e.salary FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id ) ORDER BY d.name, e.salary DESC;

Guidance

• Understand how the JOIN operation combines employee and department data.
• Focus on the subquery which calculates the average salary per employee's department.
• Identify which employees' salaries are higher than their department average.
• Pay attention to the ORDER BY clause for the final sorted output.

Hints

• The subquery is correlated — it references the outer query's department_id.
• Calculate the average salary per department first to compare each employee's salary to it.
• Check each employee against their department average salary individually.

Starter code

/* Tables and sample data are described above */

SELECT d.name AS department, e.name AS employee, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
)
ORDER BY d.name, e.salary DESC;

Expected output

| department | employee | salary | |---------------|----------|--------| | Engineering | Edward | 49000 | | Human Resources| Diana | 62000 | | Sales | Alice | 70000 |

Core concepts

Correlated subqueriesJOIN operationsAggregation functionsOrdering results

Challenge a Friend

Send this duel to someone else and see if they can solve it.