GATE CSE 2017 Set 1 | Question: 23

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:

$$\overset{\text{EMP}}{\begin{array}{|c|c|c|}\hline\\
\textbf{EmpId}&    \textbf{EmpName}&  \textbf{DeptName}\\\hline
1&     \text{XYA}& \text{AA}   \\ \hline   
2&     \text{XYB}& \text{AA} \\     \hline
3&     \text{XYC}& \text{AA}       \\\hline
4&     \text{XYD}& \text{AA}     \\\hline
5&     \text{XYE}& \text{AB}   \\\hline    
6&     \text{XYF}& \text{AB}     \\\hline   
7&     \text{XYG}& \text{AB}       \\\hline 
8&     \text{XYH}& \text{AC}       \\\hline 
9&     \text{XYI}& \text{AC}       \\\hline 
10&     \text{XYJ}& \text{AC} \\\hline 11&\text{XYK} & \text{AD} \\\hline 12 & \text{XYL}& \text{AD} \\\hline 13 & \text{XYM} & \text{AE}
\\\hline\end{array}}$$

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

The output of executing the SQL query is _____________ .

Comments

exactly

---

i faced syntax error  AS WITH  multiple columns i tried in mysql i think in any sql i shows error so that question may wrong

---

here what i am thinking how to solve
------------------------------------------------------------------------------------------------------------------
before solving sql question

------------------------------------------------------------------------------------------------------------------------------------------------
one thing make sure you look at query and try to map how renaming has happen becuse it actully confuse us in

between calculation so leets first look how they are doing renaming in between.

  * here if you look at inner query they are selecting from emp   (deptname ,count(impid)) and mapping to

EC(empid,num) .

this is the renaming they have done.
---------------------------------------------------------------------------------------------------------------------------------------------------------
now this is nested query  so.

if you look at innear query this is independent of outer query becuse there is no attribute of outer query which innear

query is using.
_____________________________________________________________________________________________

now how inner query will evaluated lets have a look.

 here group by id is given.

for the output of the inner table you can refer best answer.

-------------------------------------------------------------------------------------------------------------------------------------------------------
now after that there is EC table which has two attribute deptname ,num
and sql select tuple one by one and check tuple is inside  EC table or not .

but if you will look outer query itself using EC table .so EC table will be selected and then it does average

----------------------------------------------------------------------------------------------------------------------------------------------------
disclaimer:- if you will look at query how sql will evaluate first it will do  “” from EC””  but if you will see there is no table which is EC at the time of selection of FROM EC by outer query so it will give ERROR….
however EC will evaluted letter but till now error has happen

.

Answer 1

The inner query will return $$\begin{array} {c c} \hline \textbf{DeptName} & \textbf{Num} \\\hline  \text{AA} & 4 \\\hline \text{AB} & 3 \\\hline \text{AC} & 3 \\\hline  \text{AD} & 2 \\\hline\text{AE} & 1  \end{array}$$ Now $\text{AVG(EC.Num)}$ will find the average of Num values in the above-returned query, which is $(4+3+3+2+1)\div 5=2.6$

So according to me, the answer should be $2.6.$

Comments

@gouravs785, yes I agree with you! It also feel like some error in question or here the question is not posted properly ?

---

@sriv_shubham 

I think the way the query has written has produce 2.6 as the output 5 times which is weird 

What is the role of WHERE (DepartName,Num) IN here ??

I think we should have gotten the same output if we had done it like this : 

SELECT AVG(EC.Num) 

FROM (

SELECT DeptName,count(EmpId) as EC(DeptName,Num) 

FROM Emp

GROUP BY DeptName

) 

---

Yes same doubt !!

Answer 2

solution.............

Comments

Simple and great explanation!!

---

one thing we have to keep in mind whenever we select dept_name group-by dept_name,for example,

AA,AA,AA,AA///AB,AB,AB//AC,AC,AC//AD,AD//AE -->OUTPUT OF GROUP-BY

NOW APPLY SELECT OVER THIS

WE GET-->AA,AB,AC,AD,AE.