edited by
1,027 views
1 votes
1 votes

Find the name of Sailors with a higher rating than all sailors with age $<22?$

$Query1: $ Select S.name from sailor S where not exists (Select * from sailor $S_{2} $where $S_{2}.age<22$ and $S.rating<=S_{2}.rating$)

$Query2:$ Select S.name from sailor $S$ where $S.rating>$ ANY (Select $S_{2}.rating$ from sailor $S_{2} $where $S_{2}.age<22$)

$Query3:$ Select S.name from sailor $S$ where $S.rating>$ ALL (Select $S_{2}.rating$ from sailor $S_{2} $where $S_{2}.age<22$)


Which one is correct sql for above query??

I think Query2 and Query3 itself differentiate with ANY and ALL keyword. But what about Query1? Will it return ALL tuples?

edited by

2 Answers

Best answer
2 votes
2 votes

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

selected by
0 votes
0 votes
Query3 is correct
reshown by

Related questions

2 votes
2 votes
1 answer
3
srestha asked May 9, 2019
1,797 views
Consider the relation $R\left ( A,B,C,D,E \right )$$A\rightarrow BC$$C\rightarrow E$$B\rightarrow D$$E\rightarrow A$Total number of superkeys present in relation will be ...
1 votes
1 votes
1 answer
4
Shreya2002 asked Aug 9, 2022
1,456 views
Consider a relation schema r(A, B, C, D, E, F) and attribute A is element of every candidate key of r. Maximum number of possible candidate keys of r is ________.