You need to calculate the average monthly salary for each department, but you also want to see the individual employee's monthly salary alongside this departmental average, without collapsing the rows. Which of the following SQL queries correctly uses a window function to achieve this?

Question

BlackTom AI · Accepted Answer

Window functions with PARTITION BY compute an aggregate for each group while preserving every input row. Using AVG(MTHSAL) OVER (PARTITION BY DEPTNO) returns the departmental average on every employee row in that department without collapsing rows.

1) Relevant concepts:
- Window function: aggregate applied across a set of rows (window) but does not reduce row count.
- PARTITION BY: defines the window grouping (here, DEPTNO).
- ORDER BY inside OVER defines ordering (for running totals/frames), not grouping.
- GROUP BY aggregates rows and collapses results; not desired when you want individual rows plus an aggregate.

2) Step-by-step reasoning:
- We want each employee’s DEPTNO, EMPNO, MTHSAL and also the department average next to it.
- A window AVG partitioned by DEPTNO computes the average MTHSAL per department and returns that same value for every row in that partition.
- Therefore SELECT DEPTNO, EMPNO, MTHSAL, AVG(MTHSAL) OVER (PARTITION BY DEPTNO) AS avg_dept_salary FROM employees; does exactly that.

3) Intermediate/evaluation steps (conceptual):
- For each row r with DEPTNO = d, the database computes AVG(MTHSAL) across all rows where DEPTNO = d.
- It then outputs the row’s DEPTNO, EMPNO, MTHSAL and the computed avg_dept_salary for that partition.

4) Why the correct answer is correct:
- It uses AVG(...) OVER (PARTITION BY DEPTNO) — the correct window definition to get a per-department average while keeping every employee row.

5) Why other options are incorrect:
- Option with OVER (ORDER BY DEPTNO): ORDER BY creates a running/window frame across ordered rows, not a per-department aggregate; it won’t yield a single department average for all rows in that department.
- Option that uses SUM(... ) OVER (PARTITION BY DEPTNO): computes department total, not average, so wrong measure.
- Option that adds GROUP BY DEPTNO, EMPNO, MTHSAL: unnecessary and potentially misleading — GROUP BY collapses rows in typical aggregation queries and is not needed when using a window function to preserve rows.

Conclusion: The correct query is SELECT DEPTNO, EMPNO, MTHSAL, AVG(MTHSAL) OVER (PARTITION BY DEPTNO) AS avg_dept_salary FROM employees; which matches the provided answer.

类似问题

Why use a window function instead of GROUP BY for a running total?

Given the following query: Which of the following is true about the dept_rank column?

Question at position 42 Window functions can be a part of a calculation within a SELECT statement.TrueFalse

Question at position 31 Which of the following is a valid window function?COUNT()ROW_NUMBER()SUM()All of these

Question at position 1 What is the effect of PARTITION BY in a window function?Combines window frames from multiple tablesDefines groups within the result set for window calculationsSorts rows globallyFilters rows before calculating the window function

Question at position 45 Window functions can be a part of a calculation within a SELECT statement.TrueFalse

Question at position 43 Window functions can be a part of a calculation within a SELECT statement.TrueFalse

What was the world's first widely adopted biodiversity policy?

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单