612 views

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?

mam, take sample table, and analyze it !

@Shaik Masthan

take this example

 Sid Name Rating Age 1 Mukesh 5 22 2 Akash 6 21 3 Banerjee 5 23 4 Mukesh 6 19

What will it return??

Inner query will return Akash and Mukesh

And outer query eliminate those.

Then how will it be right query??

@Shaik Masthan

what will  be explanation for the above table??

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

by
Query3 is correct

@srestha

Actually i am new here so could you please explain where did i go wrong ? And also what should i do if i wish to share my answer ?

Any given answer need detail explaination with it.

Need not be very large, but  clearity of concept

Thanks:)

1
2
3
1,202 views