Question

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

• 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

• 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

• C.

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

• 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

Hint:

Work out what each subquery actually returns on the sample data before joining it back to Employee; watch for grouping by the wrong column or a self-join that inadvertently matches only identical rows.

Answer 1

The Correct Option is A

This question checks whether a SQL query correctly computes, for every employee, the number of employees who belong to the same team. The Employee table has EmpID as the primary key and a NOT NULL TeamID, and the target output attaches each employee's team size next to their EmpID. Let's test each candidate query against the sample data directly.

1. Option (A): The inner query "SELECT TeamID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID" groups the six rows into three teams: TeamID 8 with 3 members, TeamID 7 with 2 members, TeamID 9 with 1 member. Joining this back onto Employee by TeamID gives EmpID 1, 2, 3 a TeamSize of 3, EmpID 4, 5 a TeamSize of 2, and EmpID 6 a TeamSize of 1, exactly matching the target output. This option is correct.
2. Option (B): This query joins Employee to itself and restricts the match to rows where both TeamID and EmpID agree. Because EmpID alone already identifies a unique row, adding A.EmpID = B.EmpID collapses every match down to the single row itself, so COUNT(B.TeamID) is always 1 no matter which team the employee belongs to. This does not reproduce the target output, so this option is wrong.
3. Option (C): Grouping by EmpID instead of TeamID is the key mistake here. Since every EmpID value appears exactly once, each group contains one row, so the count is always 1 for every employee. This never gives 3, 2, or 1 the way the target output needs, so this option is wrong.
4. Option (D): The subquery computes COUNT(TeamID) AS TeamSize but drops TeamID from its own SELECT list. The outer WHERE clause then tries to use B.TeamID, a column the subquery never produced, so the statement is not valid SQL and cannot even run. This option is wrong.

Only option (A) both runs correctly and produces the exact TeamSize values shown in the target output table.

Let's summarize:

• Grouping by TeamID (not EmpID) is what gives the real per-team headcount.
• A self-join that also matches on the primary key EmpID collapses to one row per match, which defeats the purpose of counting teammates.
• A subquery cannot be referenced by a column it never selected.

So the correct query is option (A).