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

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!

---

Whats wrong with option C. I mean according to me the answer should be option  C. Reason “NOT EXIST” returns true/false when the inner loop is not satisfied. 

i.e. when not exist …..{something}   then return “true”    hence returns students with age >18 and higher grades

@Utkarsh Joshi

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)