What will be the output of this query? SELECT department, AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) AS avg_high_salary FROM employees GROUP BY department;
AAverage salary of employees earning more than 50000 per department
BAverage salary of all employees per department
CAverage salary of employees earning less than or equal to 50000
DTotal salary of employees earning more than 50000 per department
Step-by-Step Solution
Solution:
Step 1: Understand CASE inside AVG
CASE returns salary if salary > 50000, else NULL, so AVG calculates average only for salaries above 50000.
Step 2: GROUP BY department aggregates per department
Each department's average high salary is computed separately.
Final Answer:
Average salary of employees earning more than 50000 per department -> Option A
Quick Check:
AVG with CASE filters values by condition [OK]
Quick Trick:AVG ignores NULL, so CASE filters values [OK]
Common Mistakes:
Assuming AVG includes all salaries
Confusing ELSE NULL with ELSE 0
Thinking it sums salaries
Master "CASE Expressions" in SQL
9 interactive learning modes - each teaches the same concept differently
