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.
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
Which of the following is/are correct?
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
Correct 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
Explanation
Query A first counts the members of each team in a subquery and then joins it back to each employee by TeamID, which gives the team size for every employee. In B the condition $A.EmpID = B.EmpID$ matches each employee only with their own row, so every count is 1. In C the grouping is by EmpID, which is unique, so every count is 1 again. In D the subquery returns the counts without the TeamID column, so the condition $A.TeamID = B.TeamID$ refers to a column that does not exist and the query is invalid.
More Relational algebra, tuple calculus and SQL questions
GATE 2026 DA Q59– Let there be two relations X and Y as shown. X has three columns P, Q and R. Y has two…
GATE 2026 DA Q60– Let Account be a relation as shown. AccNo Balance --- --- A1 5000 A2 5000 A3 10000 A4…
GATE 2025 DA Q17– Consider the following three relations: Car (model, year, serial, color) Make (maker,…
GATE 2025 DA Q33– On a relation named Loan of a bank: loan number branch name amount --- --- --- L11 Banjara…
GATE 2025 DA Q62– Consider the following tables, Loan and Borrower, of a bank. Loan: loan num branch name…
GATE 2024 DA Q26– Consider a database that includes the following relations: Defender(name, rating, side,…