Consider a database that has the relation schema EMP (EmpId, EmpName, and…
2017
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:
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 _____________ .
Attempted by 85 students.
Show answer & explanation
Correct answer: 2.6
Solution steps:
Count employees per department from the EMP table: AA → 4, AB → 3, AC → 3, AD → 2, AE → 1.
The subquery therefore produces the rows with Num values: 4, 3, 3, 2, 1.
The outer query computes the average of these Num values: (4 + 3 + 3 + 2 + 1) / 5 = 13 / 5 = 2.6.
Final output: 2.6
A video solution is available for this question — log in and enroll to watch it.