You are given the following SQL statement.

SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary (
SELECT AVG(s.salary)
FROM employees s
WHERE s.department_id = e.department_id
);

Which of the following queries correctly rewrites this logic using an uncorrelated approach, such as a join or uncorrelated subquery or subquery factoring?

Question

You are given the following SQL statement.

SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(s.salary)
  FROM employees s
  WHERE s.department_id = e.department_id
);

Which of the following queries correctly rewrites this logic using an uncorrelated approach, such as a join or uncorrelated subquery or subquery factoring?

BlackTom AI · Accepted Answer

Correlated subqueries reference the outer query (here AVG over employees s WHERE s.department_id = e.department_id). To make this uncorrelated you must precompute the per-department average salaries and then join that result to the employees table.

Relevant concepts:
- Correlated vs uncorrelated subqueries: correlated queries depend on the outer row; uncorrelated ones do not.
- Aggregation with GROUP BY: to compute an average per department you group by department_id and apply AVG(salary).
- Joining the precomputed aggregates back to the main table lets you compare each employee’s salary to their department’s average.

Step-by-step reasoning and mapping to the original logic:
1. Compute department averages:
   SELECT department_id, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id
   This produces one row per department with the average salary.
2. Join that result to the employees table on department_id:
   FROM employees e
   JOIN dept_avg d ON e.department_id = d.department_id
3. Apply the comparison originally done in the correlated subquery:
   WHERE e.salary > d.avg_salary
This reproduces the condition e.salary > (AVG of salaries in e.department_id) without referencing the outer row inside the aggregate, so the subquery is uncorrelated.

Why the given option is correct:
- The WITH (CTE) computes department-level averages independently (uncorrelated).
- The JOIN brings each employee together with their department average.
- The WHERE clause enforces the same comparison as the correlated version.

Why the other options are incorrect:
- Option A: Using AVG(s.salary) in a WHERE with a simple JOIN is invalid because AVG is an aggregate and requires GROUP BY (and the placement is syntactically/semantically wrong).
- Option C: The HAVING clause compares AVG(salary) to salary from the outer query inside the subquery — that outer reference is not available there and the logic is inverted/invalid.
- Option D: Uses MAX(salary) instead of AVG(salary), which changes the metric and is therefore incorrect.

The correct uncorrelated rewrite is the WITH ... dept_avg ... JOIN ... WHERE e.salary > d.avg_salary query, matching the provided answer.

类似问题

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

You are tasked with identifying all ORDER# from the ORDERITEMS table where the total (quantity * paideach) for that order is greater than the total amount due for every order placed by customers in the 'FL' state. Which of the following queries correctly uses a multiple-row subquery to achieve this?

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title, pubid, cost, retail 2 FROM books 3 WHERE (pubid, cost) = 4 (SELECT pubid, MIN(cost) 5 FROM books 6 GROUP BY pubid); BOOKS table:

Question at position 55 Subqueries can only be used in the WHERE clause.TrueFalse

Question at position 54 When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned.TrueFalse

Question at position 54 When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned.TrueFalse

Question at position 43 Subqueries can only be used in the WHERE clause.TrueFalse

Question at position 31 Which of the following operator CAN NOT be used with a multiple-row subquery?NOT EXISTANYNOT INBETWEEN

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单