IAE Academy

GATE 2026 DA Q.51: solution and answer

GATE 2026 · DAQ.51 · 2 marks · MSQ

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.

Which of the following is/are correct?

Employee table (EmpID, TeamID): (1, 8), (2, 8), (3, 8), (4, 7), (5, 7), (6, 9).
Output table (EmpID, TeamSize): (1, 3), (2, 3), (3, 3), (4, 2), (5, 2), (6, 1).

GATE 2026 DA question 51: Two tables side by side.Tap to open full size
Figure from the GATE 2026 DA paper. It is part of the question.
Figure description

Two tables side by side. Employee(EmpID, TeamID) has rows (1,8), (2,8), (3,8), (4,7), (5,7), (6,9). Output(EmpID, TeamSize) has rows (1,3), (2,3), (3,3), (4,2), (5,2), (6,1).

  1. (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
  2. (B)
    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. (C)
    SELECT B.EmpID, B.TeamSize
FROM (SELECT EmpID, COUNT(TeamID) AS TeamSize
FROM Employee GROUP BY EmpID) AS B
  4. (D)
    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

Answer and solution

ANSWER

Only option (A) is correct.

Official answer: ASource: Official answer key, GATE 2026 DA (IIT Guwahati)

  1. Target output: one row per employee, with the size of that employee's own team. From the Employee table, team 8 has 3 members, team 7 has 2 and team 9 has 1, so the required rows are .

  2. Option (A): the derived table B is built with GROUP BY TeamID and holds . The condition E.TeamID = B.TeamID matches each employee to the row for their own team. Row by row, employee 1, 2, 3 (team 8) get 3, employees 4, 5 (team 7) get 2, and employee 6 (team 9) gets 1. This is exactly the required output. Correct.

  3. Option (B): the condition A.EmpID = B.EmpID pairs each row with itself, because EmpID is the primary key and so unique. The extra condition A.TeamID = B.TeamID is then always true. Each GROUP BY A.EmpID group therefore holds exactly one row, so COUNT(B.TeamID) is 1 for every employee. The result is , which differs from the required output. Wrong.

  4. Option (C): GROUP BY EmpID makes one group per employee, since EmpID is unique. COUNT(TeamID) is 1 in each group, giving . It never counts the other members of the team. Wrong.

  5. Option (D): the derived table B selects only COUNT(TeamID) AS TeamSize with GROUP BY TeamID, so its only column is TeamSize. It has no TeamID column, so B.TeamID in the WHERE clause refers to a column that does not exist and the query is rejected. Even if the join column were available, a bare count would not carry the team it belongs to. Wrong.

  6. Only (A) produces the required output.

KEY IDEA

To attach a group size to every row, aggregate with GROUP BY on the grouping column (TeamID) in a derived table, keep that column in the derived table's SELECT list, and join it back to the base table on that column.

WATCH OUT FOR

Grouping by the primary key (EmpID), as in (B) and (C), gives one row per group and a count of 1. Dropping TeamID from the derived table, as in (D), leaves nothing to join on, so the query is invalid.

From the GATE 2026 DA paper. Every question of this paper · Download the full paper · All 4 SQL questions, with notes

More SQL questions