Ans) Option D -3 rows.
Consider the query without the having clause. It calculates the average age (where age >= 18) and groups by ratings. The table returned is:
Rating |
Average |
1 |
33 |
3 |
45 |
7 |
40 |
8 |
40 |
9 |
35 |
Now, the having clause: The inner query selects the rows (from S2) where the rating is equal to the rating in the row returned by the outer query (table S) and then finally selects those rows where this count is greater than 1.
The final table returned is:
Rating |
Average |
3 |
45 |
7 |
40 |
8 |
40 |
In the first table, rows 9 and 1 have count 1 i.e. there is only 1 sailor with rating 9 and only 1 sailor with rating 1. Hence these two rows are filtered out by the having clause.
The query will find the average rating of the employee whose age is greater than or equal to 18 and rating is, and rating is repeated atleast one time.