Log In
0 votes

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

in Databases
edited by

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 .


"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.


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




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!

1 Answer

1 vote
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)

edited by

Related questions

2 votes
1 answer
$Emp$ $(eid: integer, ename: string, age: integer, salary :real)$ $Works$ $(eid: integer, did: integer, pct_time: integer)$ $Dept$ $(did: integer, budget: real, managerid: integer)$ $CREATE$ $VIEW$ $AvgsalaryByAge (age, AvgSalary) AS$ ... updated automatically. The above view on Emp can be updated automatically by updating Emp. The above view cannot be created automatically. None of the above
asked Dec 27, 2017 in Databases Harsh Mehta 300 views
0 votes
0 answers
Student table ROLLNO NAME MARKS1 MARKS2 1 T 50 30 2 S 70 99 3 D NULL 10 NULL NULL 78 NULL NULL NULL NULL NULL a) select count(MARKS1) from student; b) select count(*) from student;
asked Dec 15, 2017 in Databases Tuhin Dutta 173 views
0 votes
1 answer
0 votes
0 answers
Suppose there are two tuples in Animals relation with different IDs but of same type and in Adoption table there is a tuple with an ID that matches with the ID of first tuple in Animal relation. AND during the query evaluation, the type of first tuple of the animal relation ... same type are also present in Adoption table . but the thing is given answer is also wrong here. Correct me if i'm wrong.
asked Dec 20, 2018 in Databases Ashwani Yadav 161 views