105 views

| 105 views
0
here only query one is correct implementation of Given statement.....
0

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. 5.so 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 .

+1 vote

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

by Junior (787 points)

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.

by Active (1.7k points)

+1 vote