Ace Test Series: DBMS - Correlated Sql Query

Question

According to me it should be – “Retrieve the names of all students with a lower rank, than all students with age < 18 ”

Comments

If we take the following table, ignoring other irrelevant attributes:
 

Age	Rank
15	1
16	2
17	3
18	4
20	5

 

I think the rows returned will be 17,18 and 20 because for them, there exists no student who is less than 18 and has a higher rank than them. (Eg: The student with age 17 will be compared row wise with students with age 15,16 and 17. Now since the ranks of 17 is not less than any of them, none of the values will be selected, which will make NOT EXISTS to be true.)

So I think it should be None? 

If you see any mistake, do point out. 

---

It should be D I guess...

---

@anjali007 how you got D? At first glance, it seems that D is the correct answer, but the answer should be none of these unfortunately not given in the question.

---

I think the answer should be C .

---

Their provided answer is D but ATM it should be NONE.

---

I am also getting D. whats wrong with it??

---

@Shubhanshu The internal query gives us the name of all the students who have got a rank less than the rank of a person of age 18. So our condition is not exists that means in our final output we should have only those who have got rank >= to that of those with age less than 18 here = is not mentioned .. I assumed that RANKS ARE UNIQUE so got D .

---

Shubhanshu

"Retrieve the names of all students with a lower rank, than all students with age < 18 ”

Yes, you are correct. The query will return above statement. None of the options is matching.

---

@Gokulnath ,if in option (d),S1.RANK<= S2.RANK ,then it would have been correct,am I correct?

 

---

internal query for above table only returns 15 and 16 right? then, how are you saying all persons with rank less than 18? because, in the above case itself, it doesn't return 17. So, not exists will return 17,18,20. Right?

@anjali007

---

@codingo1234

I don't think thats the right correction. There is always only 1 case failing from <18 case. Like, with the table above which is 17. With that condition, I think 15 and 16 will also be valid!

Answer 1

the right answer would be D .

what you are saying is answer should be "retrieve the name of all student with lower rank than all student of age 18"

which is wrong because here you are just replacing NOT EXISTS with EXISTS FOR ALL.

NOT EXISTS means( for ALL) (NOT EXIST ) that is internal query or subquery tuples( inside NOT EXISTS) should not be true for  any outer query tuple..

 

we can conclude our answer by breaking into some logical sentences , here is my approach

point 1:consider student s1 and s2 two group of students(basically same students recognize by two different group name)

point 2:for every student in s1 we will check for each student in s2(corelational subquery)

for particular students s1.a

NOT EXISTS will return false  if THERE EXIST a group 2 student with age<18 AND rank >s1.a

NOT EXISTS will return true  if for all students of s2 with age <18 have rank<s1.a

so we can say query return the name of students who has higher  rank than all student with age <18

note: answer might be assuming all ranks are unique otherwise the correct statement would be "name of students who have rank higher than or equal to all student with age<18"

surely if same options were given in multiple select type question this year you should not choose any .(my opinion)