ISRO-DEC2017-11

Question

Consider the schema

$\text{Sailors(sid,sname,rating,age) with the following data}$

$\begin{array}{|l|l|l|l|} \hline \textbf{sid} & \textbf{sname} & \textbf{rating} & \textbf{age} \\ \hline \text{22} & \text{Dustin}  & \text{7} & \text{45} \\ \hline \text{29} & \text{Borg}  & \text{1} & \text{33} \\ \hline \text{31} & \text{Pathy}  & \text{8} & \text{55} \\ \hline \text{32} & \text{Robert}  & \text{8} & \text{25} \\ \hline \text{58} & \text{Raghu}  & \text{10} & \text{17} \\ \hline \text{64} & \text{Herald}  & \text{7} & \text{35} \\ \hline \text{71} & \text{Vishnu}  & \text{10} & \text{16} \\ \hline \text{74} & \text{King}  & \text{9} & \text{35} \\ \hline \text{85} & \text{Archer}  & \text{3} & \text{26} \\ \hline \text{84} & \text{Bob}  & \text{3} & \text{64} \\ \hline \text{96} & \text{Flinch}  & \text{3} & \text{17} \\ \hline  \end{array}$

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$

Comments

where is the row for 10th rating.I think it should be included.plz explain

---

row with 10th rating will not be included because its avg(age) is not > 18.

Answer 1

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.

Comments

answer is correct , but explanation is wrong.

---

What is wrong?

---

We are selecting those groups which contains more than 1 row.

After groups are formed rating 3,7,8 will have more than 1 rows in it, so the answer is 3.

Isin't it ?

---

Yes, that's correct Roshan Mishra.