in Databases edited by
612 views
1 vote
1 vote

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?

in Databases edited by
by
612 views

4 Comments

mam, take sample table, and analyze it !
0
0

@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??

0
0

@Shaik Masthan

what will  be explanation for the above table?? 

0
0

2 Answers

2 votes
2 votes
Best answer

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

4 Comments

If u r new here, plz chk other answer, before giving your own answer
0
0

@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 ?

0
0
Any given answer need detail explaination with it.

Need not be very large, but  clearity of concept
0
0

 @srestha

Thanks:)

0
0

Related questions