Skip to content
GATETECH
Official PYQ · 2017Set 1 · Q23Tuple Calculus and SQLNAT1 mark

GATE CS 2017 Set 1 Q23 — Tuple Calculus and SQL

Explain with AI

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:

EmpIdEmpNameDeptName1XYAAA2XYBAA3XYCAA4XYDAA5XYEAB6XYFAB7XYGAB8XYHAC9XYIAC10XYJAC11XYKAD12XYLAD13XYMAEEMP\small \overset{\text{EMP}}{\begin{array}{|c|c|c|}\hline \textbf{EmpId}& \textbf{EmpName}& \textbf{DeptName}\\ \hline 1& \text{XYA}& \text{AA} \\ \hline 2& \text{XYB}& \text{AA} \\ \hline 3& \text{XYC}& \text{AA} \\ \hline 4& \text{XYD}& \text{AA} \\ \hline 5& \text{XYE}& \text{AB} \\ \hline 6& \text{XYF}& \text{AB} \\ \hline 7& \text{XYG}& \text{AB} \\ \hline 8& \text{XYH}& \text{AC} \\ \hline 9& \text{XYI}& \text{AC} \\ \hline 10& \text{XYJ}& \text{AC} \\ \hline 11&\text{XYK} & \text{AD} \\ \hline 12 & \text{XYL}& \text{AD} \\ \hline 13 & \text{XYM} & \text{AE} \\ \hline\end{array}}

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 _____________ .

View worked solution

Answer: 2.6

The subquery groups the EMP table by DeptName and counts the number of employees in each department. This temporary relation is aliased as EC(DeptName, Num).

Grouping the given EMP instance by DeptName yields the following counts (Num) for each department:

  • AA: 4 employees (EmpId 1, 2, 3, 4)
  • AB: 3 employees (EmpId 5, 6, 7)
  • AC: 3 employees (EmpId 8, 9, 10)
  • AD: 2 employees (EmpId 11, 12)
  • AE: 1 employee (EmpId 13)

The relation EC contains these 5 rows. The WHERE (DeptName, Num) IN (...) clause checks if each row of EC is present in the subquery's result. Since the subquery generates exactly the rows of EC, all 5 rows satisfy the condition.

Finally, the outer query computes the average of the Num column:

AVG(EC.Num)=4+3+3+2+15=135=2.6\text{AVG(EC.Num)} = \frac{4 + 3 + 3 + 2 + 1}{5} = \frac{13}{5} = 2.6

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions