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

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

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

Prepp Team · Accepted Answer

SQL Solution for Employee Team Size

The objective is to determine the size of the team each employee belongs to. This involves aggregating team membership counts and then associating these counts with individual employees.

1. Calculate Team Sizes

A subquery is used to calculate the number of employees per team:

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

$GROUP BY TeamID$ aggregates records by team.
$COUNT(TeamID)$ counts employees in each team, defining $TeamSize$.
This creates a temporary result set (aliased as $B$) mapping $TeamID$ to $TeamSize$.

2. Associate Team Sizes with Employees

The main query joins the original $Employee$ table with the subquery results:

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

$Employee AS E$ represents the individual employee records.
The subquery result is aliased as $B$.
The join condition $E.TeamID = B.TeamID$ links each employee to their team's calculated size.
The final selection retrieves the employee's ID ($E.EmpID$) and their team's size ($B.TeamSize$).

Examination of Alternative Queries

Option 2: The self-join $WHERE A.EmpID = B.EmpID$ incorrectly restricts the count to 1 per employee, failing to measure team size.
Option 3: Grouping by $EmpID$ counts associated $TeamID$s per employee (always 1), not the total members in that employee's team.
Option 4: The subquery omits $TeamID$ from its output, making the join condition $WHERE A.TeamID = B.TeamID$ invalid due to a missing column in the subquery result $B$.

Prepp Team · Answer

SELECT A.EmpID, COUNT(B.TeamID) AS TeamSizeFROM Employee AS A, Employee AS BWHERE A.TeamID = B.TeamID AND A.EmpID = B.EmpIDGROUP BY A.EmpID

Prepp Team · Answer

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

Prepp Team · Answer

SELECT A.EmpID, B.TeamSizeFROM Employee AS A, (SELECT COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS BWHERE A.TeamID = B.TeamID

List I		List II
(a)	DDL	(i)	LOCK TABLE
(b)	DML	(ii)	COMMIT
(c)	TCL	(iii)	Natural Difference
(d)	Binary operation	(iv)	REVOKE

AccNo	Balance
A1	5000
A2	5000
A3	10000
A4	15000
A5	18000

Raider
ID	Name	Raids	RaidPoints
1	Arjun	200	250
2	Ankush	190	219
3	Sunil	150	200
4	Reza	150	190
5	Pratham	175	220
6	Gopal	193	215

Team
City	ID	BidPoints
Jaipur	2	200
Patna	3	195
Hyderabad	5	175
Jaipur	1	250
Patna	4	200
Jaipur	6	200

SQL Solution for Employee Team Size

1. Calculate Team Sizes

2. Associate Team Sizes with Employees

Examination of Alternative Queries

Important Questions from SQL

Start Your Preparation with Prepp Mobile App