GROUP BY with HAVING — OS, DBMS & Networks Aptitude Question
GROUP BY with HAVING: Table employee: | emp_id | dept | salary | |--------|-------|--------| | 1 | Sales | 30000 |… Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Medium
- Time target: 120 seconds
Question
Table employee:
| emp_id | dept | salary |
|---|---|---|
| 1 | Sales | 30000 |
| 2 | Sales | 50000 |
| 3 | HR | 40000 |
| 4 | HR | 60000 |
| 5 | IT | 45000 |
What does this query return?
SELECT dept
FROM employee
GROUP BY dept
HAVING AVG(salary) > 40000;Options
- HR and IT
- HR only
- Sales and HR
- Sales, HR and IT
Show the answer and the worked solution
Correct answer: A. HR and IT
Worked solution
- GROUP BY dept forms three groups: Sales with 30000 and 50000, HR with 40000 and 60000, and IT with 45000.
- The averages are Sales = 40000, HR = 50000 and IT = 45000.
- HAVING keeps only groups whose average is strictly greater than 40000, so Sales is dropped on the boundary and HR and IT remain.
Answer: HR and IT.
The idea
WHERE filters rows before grouping; HAVING filters groups after the aggregate is computed. Boundary values like Sales here are exactly what a HAVING question is testing.
Shortcut. Only Sales can be borderline: (30000 + 50000) ÷ 2 = 40000 exactly, which fails the strict > 40000, while HR (50000) and IT (45000) pass.
Common trap. "Sales, HR and IT" treats > as >=, keeping Sales although its average equals 40000 exactly.