WorksheetsAggregate Functions group by having
Total questions: 21
Worksheet time: 11mins
Employee(emp_id, dept, salary, bonus)
• salary and bonus may contain NULLs
• dept includes some NULL department rows
Which query filters only departments where more than 3 employees exist?
WHERE COUNT() > 3
HAVING COUNT() > 3
WHERE emp_id > 3
FILTER COUNT(*) > 3
COUNT(*) vs COUNT(salary) difference?
COUNT(salary) ignores NULL
COUNT(*) counts all rows
Both A and B
They are always identical
Which returns departments with total bonus > total salary?
Valid
Invalid
Which filters employees before grouping?
WHERE
HAVING
GROUP WHERE
DISTINCT
Evaluate: SELECT dept, SUM(salary) FROM Employee GROUP BY dept HAVING SUM(salary) IS NULL;
Shows only depts where salary all NULL
Shows non-NULL salary depts
Shows error
Shows all rows
Which counts NULL salary rows per dept?
Correct
Wrong
HAVING always needs GROUP BY?
No
Yes
Which query shows only departments where avg bonus > 10000?
HAVING bonus > 10000
HAVING AVG(bonus) > 10000
WHERE AVG(bonus) > 10000
GROUP WHERE AVG(bonus) > 10000
NULL dept values in GROUP BY become:
A separate NULL group
Ignored
Merged into highest dept value
Returns error
Which is most accurate?
HAVING filters aggregated results
HAVING works same as WHERE
HAVING never uses aggregate functions
HAVING comes before GROUP BY
Which clause executes first?
HAVING
GROUP BY
WHERE
SELECT
If a dept has rows but salary all NULL: SUM(salary) returns?
NULL
0
Error
Full salary
What does AVG ignore?
NULL salaries
Zero salaries
Both A & B
Neither
Find departments where highest salary 5000?
HAVING COUNT(*) = 5 AND MAX(bonus) > 5000
WHERE COUNT(*) = 5 AND MAX(bonus) > 5000
HAVING COUNT(bonus) = 5
COUNT(*) = 5 AND HAVING MAX(bonus) > 5000
Distinct in aggregation: HAVING SUM(DISTINCT salary) > 50000 Meaning:
Ignore duplicate salary values in sum
COUNT only rows
Works same as SUM(salary)
Sums only highest salary
Which gives count per dept including NULL dept?
SELECT dept, COUNT(*) FROM Employee GROUP BY dept;
SELECT dept, COUNT(dept) FROM Employee GROUP BY dept;
HAVING with column alias is: SELECT dept, SUM(salary) AS total FROM Employee GROUP BY dept HAVING total > 50000;
May work in some DBs, not standard SQL
Always works everywhere
Always error
HAVING cannot reference SUM
Which finds depts where at least one employee missing bonus?
HAVING COUNT(bonus) = 50000? HAVING SUM(CASE WHEN salary>=50000 THEN 1 ELSE 0 END)=2
Correct
Incorrect
Which gives only dept where no salary recorded?
HAVING SUM(salary) = 0
HAVING SUM(salary) IS NULL
HAVING COUNT(*)=0
WHERE salary IS NULL
HAVING without any aggregate in GROUP BY query:
Can use normal column conditions
Equivalent to WHERE after grouping
Might be valid logic
All above
Which returns departments with more than 1 distinct salary?
HAVING COUNT(DISTINCT salary) > 1
HAVING COUNT(salary) > 1
WHERE DISTINCT salary > 1
HAVING DISTINCT salary > 1
