Sql query

Question

Q). Consider STUDENT table with the following tuples.

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)))

How many numbers of tuples are there in the output of the above query?

                                    

Comments

I think Answer is 3 tuples

---

I think answer shud be 2 , given was 4. :) ​

So, it shud be  2 / 3 / 4.

---

Yeah U r right @Amsar

---

4 is  the right ans.

1st  part of the query returns dilip,pallab,

union

2nd  part of the query  returns deepak,dilip,pallab,sourav.

because inner query will return(deepak,dilip,pallab,sourav) and after comparing with S1 along with all condition .we get the desired result. as NULL

Answer 1

Ans - 2

Lets divide the queries in to Q1 U Q2

Q1 Select students who have not more than 3 students with cpi '>=' theirs

Q2 Select students who have cpi > ALL(subQ2). Similar to Q1 subQ2 selects students who have not more than 5 students with cpi '>=' theirs

Q1 outputs Dilip and Pallab
subQ2 outputs Dilip, Pallab, Deepak and Sourav. So, Q2 outputs empty as none have cpi greater than Dilip(not even himself)

So, result = 2 rows union phi. no intersection. So, ans is 2.

This(http://sqlfiddle.com/#!9/95e5e/7) might help to understand the chunking. Try deleting part of query and running