ISRO | ISRO CS 2017 | Question 11

Consider the schema
Sailors(sid, sname, rating, age) with the following data

For the query

SELECT S.rating, AVG(S.age) AS avgage FROM Sailors S
Where S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)

The number of rows returned is
(A) 6
(B) 5
(C) 4
(D) 3


Answer: (D)

Explanation: The qiven query would run like:
First of all where statement would be executed and all the tuples with age less than 18 would be eliminated.
Now with Group By the remaining tuples will be grouped according to rating:

After this, all the group-by tuples having their count more than 1 will be selected and their ratings and average age will be given as output.

So, the correct answer is (C)

Quiz of this Question