Log In
2 votes

in Databases 155 views
here only query one is correct implementation of Given statement.....

i think query 2 will definately work but but query 1 will not work because in  query 1 NOTEXIST is present it will be give true value when inner query will give empty but in inner query firstly department no. 5 employee from s selected then then for each employee of e will check their salary is greater than equal to salary of  every employee salary os of table s which belongs to dept no. 5.

as you can see 20 in table e will compare to each s.salary which is from deptno. inner query give the result .so inner query not empty for 20 means 20 will not in result but  salary 20 is greater than any of  s.sal of deptno. 5 .so query 1 will not give answer .

2 Answers

1 vote

Answer would be D

Q1: In the inner query in the WHERE clause the stament is  : s.salary >= e.salary. So e.sal will be compared against its own value and will always return true, therefore no rows will be selected

Q2:If e.sal is greater than any sal in the table it will o/p it. so it will print all the salaries except the least

Thus the o/p of both the queries is also not the same

0 votes

Here Q1 seems to be correct.. let me explain why...

As question says who get higher salary than anyone in department 5 i.e. we want those employees whose salary is higher than ANY employee we take from department 5 i.e. from ALL employees(great english statement...). So now let's take each query one by one:

Q1: As per this query it says select those employees from outer query in such a way that whenever inner query finds that it's(inner query's) salary is greater that outer query's salary(as it is like nested loop so for each outer row we have to lookup whole inner table) then it will return one row which is against NOT EXISTS i.e. this query won't return anything if at least one row return by inner query hence it will give us ONLY those employees whose salary is higher than ALL employees of dept 5

Q2: It's obvious from Q1 explanation that we can't use ANY...if we change it to ALL then it will work like Q1.

So answer is option (A).

Related questions

1 vote
3 answers
Class(name, meets_at, room, fid) Faculty(fid, fname, deptid) Find the names of faculty members who teach in every room in which some class is taught
asked May 18, 2019 in Databases aditi19 317 views
0 votes
0 answers
Product(maker, model, type) PC(model, speed, ram, hd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Find the maker(s) of the PC(s) with the fastest processor among all those PC 's that have the smallest amount of RAM pls check if my SQL ... p.model from PC p where p.speed IN( select max(p1.speed) from PC p1 where p1.ram IN( select MIN(p2.ram) from PC p2)))
asked May 8, 2019 in Databases aditi19 269 views
0 votes
0 answers
I got why query 2 is wrong. How do I approach to understand Query 1, is there a generic approach for solving questions like these which have no tables given?
asked Jan 8, 2019 in Databases amitqy 193 views
0 votes
1 answer
According to me it should be – “Retrieve the names of all students with a lower rank, than all students with age < 18 ”
asked Dec 24, 2018 in Databases Shubhanshu 388 views