DBMS

Question

SName   CPI
Deepak  8.7
Dilip   9.7
Kaustav 8.5
Pallab  9.8
Sourav  8.7
Swapnil 8.5

(SELECT *
FROM STUDENT S1
WHERE 3 >= (SELECT COUNT(*)
FROM STUDENT S2
WHERE S1.CPI <= S2.CPI) )
UNION
(SELECT *
FROM STUDENT S1
WHERE S1.CPI > ALL (SELECT CPI
FROM STUDENT S2
WHERE 5 >= (SELECT COUNT(*)
FROM STUDENT S3
WHERE S2.CPI <= S3.CPI)) )

"where 3" what does it mean? How mant and which tuples it is returning ?

Answer 1

(SELECT *
FROM STUDENT S1
WHERE 3 >= (SELECT COUNT(*)
FROM STUDENT S2
WHERE S1.CPI <= S2.CPI) )
UNION
(SELECT *
FROM STUDENT S1
WHERE S1.CPI > ALL (SELECT CPI
FROM STUDENT S2
WHERE 5 >= (SELECT COUNT(*)
FROM STUDENT S3
WHERE S2.CPI <= S3.CPI)) )

Very complex, so lets split up

(SELECT * FROM STUDENT S1 WHERE 3 >= 

(SELECT COUNT(*) FROM STUDENT S2 WHERE S1.CPI <= S2.CPI) )

UNION

(SELECT * FROM STUDENT S1 WHERE S1.CPI > ALL 

(SELECT CPI FROM STUDENT S2 WHERE 5 >= 

(SELECT COUNT(*) FROM STUDENT S3 WHERE S2.CPI <= S3.CPI)) )

Now, here subquery 1 is dependent on 2, and subquery 4 is dependent on 5, and subquery 3 is dependent on 4. So, we must execute the ones without dependence first -- which are 2 and 5.

Now queries 1,2 and 4,5 requires JOIN operation. Lets do 1,2 which is on condition S1.CPI <= S2.CPI

SName	S1.CPI	Count
Deepak	8.7	4
Dileep	9.7	2
Kaustav	8.5	6
Pallab	9.8	1
Saurav	8.7	4
Swapnil	8.5	6

  Now, subquery 1 can proceed and it should select the rows for Dileep and Pallab.

Now similarly when we do a join for (4,5) on S2.CPI <= S3.CPI we get the same table as for (1,2). Now, the select condition is COUNT(*) <= 5, and hence we get the rows correspoonging to <Deepak, Dileep, Pallab, Saurav>. Now, we do subquery 3- which require S1.CPI > ALL (CPI <Deepak, Dileep, Pallab, Saurav>)

This is same as saying S1.CPI >= MAX(CPI (Deepak), CPI(Dileep), CPI(Pallab), CPI(Saurav)) = MAX(8.7, 9.7, 9.8, 8.7)

Thus we require S1.CPI >= 9.8 which is TRUE only for Pallab. But the row for Pallab is already selected by (1,2) and hence after UNION also we get the rows for Dileep and Pallab only.

Comments

yes , thank u Sir :)

Answer 2

(SELECT *
FROM STUDENT S1
WHERE 3 >= (SELECT COUNT(*)
FROM STUDENT S2
WHERE S1.CPI <= S2.CPI) )

means select those aatribute whoes vaues less than 3 or more attribute  including itself in same table.

like 9.8  <= 9.8 pass

9.7 <= 9.8 and 9.7 pass

8.7 <= 8.7 ,9.8, 9.7, 8.7  so fail 

second query will be empty 

so ans will be only 2 tuplle i.e. dileep and pallab.

Comments

then how many tuple returning?