I assume this is the relation
Sailors(sid, sname, rating, age)
and I take a sample table
sid |
sname |
rating |
age |
1
2
3
4
5
6
7
8
9
|
Raj
Anurag
Vishal
Rahul
Sameer
Sayan
Karan
Pradeep
Abhishek
|
9
5
8
7
3
6
10
7
8
|
31
24
19
20
29
21
18
34
17
|
for query 2 and query 3 the sub-query(Select S2.rating from sailor S2 where S2.age<22) I have filtered sailors with age<22
S2:
sid |
sname |
rating |
age |
3
4
6
7
9
|
Vishal
Rahul
Sayan
Karan
Abhishek
|
8
7
6
10
8
|
19
20
21
18
17
|
Query 2: Select S.name from sailor S where S.rating>>ANY(Select S2.rating from sailor S2 where S2.age<22)
sname |
Raj
Vishal
Karan
Pradeep
Abhishek
|
it includes tuples from age<22 as well so it is incorrect
Query 3: Select S.name from sailor S where S.rating>ALL(Select S2.rating from sailor S2 where S2.age<22)
it will select those tuples from relation S whose rating is more than the rating of all tuples of relation S2
it will return empty relation as there is no such tuple in the relation S
Now, query 1 is a co-related sub-query so for every tuple returned by the outer query, the inner sub-query will be executed
Query 1: Select S.name from sailor S where NOT EXISTS(Select * from sailor S2 where S2.age<22 and S.rating<=S2.rating)
S<1, Raj, 9, 31>
in the inner query one such tuple satisfies the condition S2<7, Karan, 10, 18>
thus NOT EXISTS clause will evaluate to false as the sub-query doesn't evaluates an empty relation
another example- take S<2, Anurag, 5, 24>
the tuples that satisfies condition mentioned in the sub-query
S2<3, Raj, 8, 19>
S2<4, Rahul, 7, 20>
S2<6, Sayan, 6, 21>
S2<7, Karan, 10, 18>
S2<9, Abhishek, 8, 17>
if you check for all rows of S the NOT EXISTS clause will become false. So there is not such sailor
so query 1 is working as desired
So query1 and query 3 is correct