edited by
884 views
3 votes
3 votes

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?

  1. SELECT S.sname FROM Sailors S WHERE NOT EXISTS ( SELECT * FROM Sailors S2 WHERE S2.age < 21  AND S.rating <= S2.rating )
  2. SELECT * FROM Sailors S WHERE S.rating > ANY ( SELECT S2.rating FROM Sailors S2 WHERE S2.age < 21 )
    
  3. SELECT S.sname FROM Sailors S WHERE EXISTS ( SELECT * FROM Sailors S2 WHERE S2.age < 21 AND S.rating <= S2.rating )
  4. SELECT S.sname FROM Sailors S WHERE S.rating >ALL ( SELECT S2.rating  FROM Sailors S2 WHERE S2.age < 21 )
    
edited by

2 Answers

Best answer
9 votes
9 votes

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:

selected by
0 votes
0 votes
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.
Answer:

Related questions

1 votes
1 votes
1 answer
1
Bikram asked Nov 26, 2016
319 views
Consider the join of relation R with a relation S. If R has $m$ tuples and S has $n$ tuples, then the maximum and minimum sizes of the join, respectively, are __________....
0 votes
0 votes
1 answer
2
Bikram asked Nov 26, 2016
229 views
A functional dependency of the form x → y is trivial ify ⊆ xy ⊂ xx ⊆ yx ⊂ y
0 votes
0 votes
1 answer
3
Bikram asked Nov 26, 2016
295 views
What does the following Tuple Relational Calculus query produce?The expression σθ1 (E1 ⋈θ2 E2) is the same as: E1 ⋈θ1^ θ2 E2 (σθ1 E1) ∧ (σθ2 E2 ) E1 ⋈ θ...