Question

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

EMP
EmpId	EmpName	DeptName
1	XYA	AA
2	XYB	AA
3	XYC	AA
4	XYD	AA
5	XYE	AB
6	XYF	AB
7	XYG	AB
8	XYH	AC
9	XYI	AC
10	SYJ	AC
11	XYK	AD
12	XYL	AD
13	XYM	AE

 

SELECT AVG(EC.Num FROM EC WHERE(DeptName, Num) IN           (SELECTED DeptName, COUNT(EmpId)AS                                                     EC(DeptName, Num)            FROM EMP            GROUP BY DeptName)

 

The output of executing the SQL query is _________.

Answer 1

Concept:

Count the number of EmpIDs corresponding to a particular department and then write as attribute value of Num in new relational schema EC.

Explanation:

SQL query results                            

EC
DeptName	Num
AA	4
AB	3
AC	3
AD	2
AE	1

 

AVG(EC. Num) will find out the average of Num values in the returned query.,

\(Average = \;\frac{{4 + 3 + 3 + 2 + 1}}{5} = 2.6\)