The GATE Grind

GATE 2026 DA – Question 51

Database Management and Warehousing · Relational algebra, tuple calculus and SQL · 2 marks · Multiple select

Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.

We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.

Employee: EmpIDTeamID
18
28
38
47
57
69
Output: EmpIDTeamSize
13
23
33
42
52
61

Which of the following is/are correct?

  1. SELECT E.EmpID, B.TeamSize FROM Employee AS E, (SELECT TeamID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B WHERE E.TeamID = B.TeamID
  2. SELECT A.EmpID, COUNT(B.TeamID) AS TeamSize FROM Employee AS A, Employee AS B WHERE A.TeamID = B.TeamID AND A.EmpID = B.EmpID GROUP BY A.EmpID
  3. SELECT B.EmpID, B.TeamSize FROM (SELECT EmpID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY EmpID) AS B
  4. SELECT A.EmpID, B.TeamSize FROM Employee AS A, (SELECT COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B WHERE A.TeamID = B.TeamID

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (A) SELECT E.EmpID, B.TeamSize FROM Employee AS E, (SELECT TeamID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B WHERE E.TeamID = B.TeamID

Explanation

Query A first counts the members of each team in a subquery and then joins it back to each employee by TeamID, which gives the team size for every employee. In B the condition $A.EmpID = B.EmpID$ matches each employee only with their own row, so every count is 1. In C the grouping is by EmpID, which is unique, so every count is 1 again. In D the subquery returns the counts without the TeamID column, so the condition $A.TeamID = B.TeamID$ refers to a column that does not exist and the query is invalid.