SQL_DBMS

Question

Consider the following relational schema:
Students(StudId, StuName, Score) with primary key as underlined. Assuming that the attribute score values can be repeated and cannot contain NULL values. What is the output of the following SQL query?
SELECT S1.Score FROM Students S1 where
((SELECT COUNT(DISTINCT S2.Score) FROM Students S2
Where S2.Score ≤ S1.Score) ≤ 3);
Consider the students table as follows:
Students:

1.   80, 80, 70
2.   90, 80, 70,80
   Explanation:
   Given query is a correlated sub query
   Hence for each tuple in Student S1, we compare the score of the specific student with the scores in Student S2 and check whether the score(S1) is greater than or equal to at most 3 distinct scores in Student S2 table. If this condition returns TRUE, the corresponding score from Student S1 table is displayed. In such a way, the above query returns least 3 scores from the given STUDENT table.
   Given STUDENT table as follows:
   
   Above query returns the least 3 scores.
3.   100, 90, 80,80
4.   100, 100, 90,80

Is it the correct explaination when specified greater then and equal to then 100 should also come I suppose.

Answer 1

Both answer and explanation are correct.

Suppose we are running the co-related query for first tuple of S1, means score = 100. Now for this tuple we get 4 distinct values of S2.score (100, 90, 80, 70). All of these values are greater than or equal to 100.

Therefore Count(DISTINCT S2.score) = 4, and it is greater than 3, it makes the co-related query output false and this is why 100 is not included.