GATE CS Mastery
Cheat sheets

GATE CS 2017 Set 1 · Databases

Question 23

NATPrinted as CS Q.231 markNo negative markingMedium (estimate)

Question

Consider a database that has the relation schema EMP(EmpId,EmpName,DeptName)\text{EMP}(\text{EmpId}, \text{EmpName}, \text{DeptName}). An instance of the schema EMP and a SQL query on it are given below.

EmpIdEmpNameDeptName
1XYAAA
2XYBAA
3XYCAA
4XYDAA
5XYEAB
6XYFAB
7XYGAB
8XYHAC
9XYIAC
10XYJAC
11XYKAD
12XYLAD
13XYMAE
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 ______.

Official answer and solution
Official answer
2.6
From
Official GATE 2017 answer key

Solution

Group the EMP table by DeptName, count the number of employees per department, and compute their arithmetic mean.

The inner query groups the EMP table by DeptName and counts the number of employees in each department:

SELECT DeptName, COUNT(EmpId) AS Num
FROM EMP
GROUP BY DeptName

Counting the rows for each distinct DeptName from the EMP relation:

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

This yields 5 department counts: 4, 3, 3, 2, 1.

The outer query computes the average of these department counts (AVG(EC.Num)):

Average=4+3+3+2+15=135=2.6\text{Average} = \frac{4 + 3 + 3 + 2 + 1}{5} = \frac{13}{5} = 2.6
Final answer

2.6

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper
GATE 2017 Computer Science and Information Technology (CS) Session 1
Question
23 of 65, printed as CS Q.23
Section
Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.