Test by Bikram | Databases | Test 1 | Question: 17

Question

This is an Instance of Sailors Relation

$$\overset{\text{Sailors Table}} { \begin{array}{|l|l|} \hline \text{S_ID} & \text{S_Name} & \text{Rating} & \text{Age} \\ \hline 18 & \text{Jones} & 3 & 30.00  \\ \hline 41 & \text{Jonah} & 6 & 56.00   \\ \hline 22 & \text{Ahab} & 7 & 44.00  \\ \hline 63 & \text{Moby} & \text{Null} & 15.00  \\ \hline \end{array}}$$

Consider the following query:

 Find the names of sailors with a higher rating than all sailors with age < 21.

Which one among these SQL queries attempt to obtain the answer to this question?

• A.

  SELECT S.sname FROM Sailors S WHERE NOT EXISTS ( SELECT * FROM Sailors S2 WHERE S2.age < 21  AND S.rating <= S2.rating )

• B.

  SELECT * FROM Sailors S WHERE S.rating > ANY ( SELECT S2.rating FROM Sailors S2 WHERE S2.age < 21 )
  

• C.

  SELECT S.sname FROM Sailors S WHERE EXISTS ( SELECT * FROM Sailors S2 WHERE S2.age < 21 AND S.rating <= S2.rating )

• D.

  SELECT S.sname FROM Sailors S WHERE S.rating >ALL ( SELECT S2.rating  FROM Sailors S2 WHERE S2.age < 21 )
  

Comments

Forget the options, what should be the expected answer for the query?

---

Hi Sir,

Query in option A will return all rows from table Sailors. Since inner query will return empty set (0 Rows ) for each outer tables row and for that  NOT Exists operator will return true.

---

I asked for the query in question- not option A.

Answer 1

A is the correct answer, but not D. because when INNER query contains a record with NULL value then ALL returns unkown and returns empty because comparison with NULL is UNKOWN. (i had written the same in my notebook too!)
To prove my point:

Comments

then what is the answer for given instance when query A is applied.(you said that answer is 0 tuple when query D is applied on inctanse of relation)

---

25(say X) > unknown  what it will return True or False???

Answer 2

i think given answer is wong. answer should be D.

as use of "ANY" in option B gives name of sailors whose rating is higher than any one of the sailors with age<21.

but we need sailors with rating higher than rating of "ALL" sailors with age<21. (which is option D)

second reason is we ae asked here for just NAME of sailors while option B gives complete detail of sailor. in that view also D is more stronger option.