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!