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

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

Accepted 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

Related Database Management and Warehousing PYQs

Turn practice into measurable progress