GATE 2017 CS – Question 33
Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.
| EmpId | EmpName | DeptName |
|---|---|---|
| 1 | XYA | AA |
| 2 | XYB | AA |
| 3 | XYC | AA |
| 4 | XYD | AA |
| 5 | XYE | AB |
| 6 | XYF | AB |
| 7 | XYG | AB |
| 8 | XYH | AC |
| 9 | XYI | AC |
| 10 | XYJ | AC |
| 11 | XYK | AD |
| 12 | XYL | AD |
| 13 | XYM | AE |
SELECT AVG(EC.Num)
FROM EC
WHERE (DeptName, Num) IN
(SELECT DeptName, COUNT(EmpId) AS EC(DeptName, Num)
FROM EMP
GROUP BY DeptName)The output of executing the SQL query is ________.
Practise this question in The GATE Grind →
Show answer and explanation
Correct answer: 2.59 to 2.61
Explanation
The inner query counts the employees per department: AA has 4, AB has 3, AC has 3, AD has 2 and AE has 1. The outer query keeps every one of these rows and averages the counts: $\frac{4 + 3 + 3 + 2 + 1}{5} = \frac{13}{5} = 2.6$.