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. Which of the following is/are correct?
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
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
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$.
Important Questions from SQL
Consider a relation book (title, price) which contains the titles and prices of different books.
Assuming that no two books have the same price, what does the following SQL query list ?
Select title
from book as B
where (select count (*)
from book as T
where T.price > B.price) < 7Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Given the following STUDENT‐COURSE scheme : STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
Which of the following is used to create a database schema?
Which of the component module of DBMS does rearrangement and possible ordering of operations, eliminate redundancy in query and use efficient algorithms and indexes during the execution of a query?
