Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an…

GATE · 2026 · DA · Data Science & AI

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

EmpID	TeamID
1	8
2	8
3	8
4	7
5	7
6	9

Output

EmpID	TeamSize
1	3
2	3
3	3
4	2
5	2
6	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

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

SELECT B.EmpID, B.TeamSize
FROM (SELECT EmpID, COUNT(TeamID) AS TeamSize
FROM Employee GROUP BY EmpID) AS B

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

Attempted by 3 students.

Explore the full course: Gate Guidance By Sanchit Sir