GATE CSE 2007 | Question: 61

Question

Consider the table employee(empId, name, department, salary) and the two queries $Q_1, \, Q_2$ below. Assuming that department $5$ has more than one employee, and we want to find the employees who get higher salary than anyone in the department $5,$ which one of the statements is TRUE for any arbitrary employee table?

$Q_1:$	Select e.empId From employee e Where not exists (Select * From employee s Where s.department = "5" and s.salary >= e.salary)
$Q_2:$	Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department = "5")

• A. $Q_1$ is the correct query
• B. $Q_2$ is the correct query
• C. Both $Q_1$ and $Q_2$ produce the same answer
• D. Neither $Q_1$ nor $Q_2$ is the correct query

Comments

yes it's true

---

yes, there is no rule like this.

But , see what the meaning implies for both the cases.

---

Main crux here is to understand difference between “ANYONE”  and “ANY ONE”  ::)

Answer 1

Answer: A
Create a table like this:

create table employee(empId int(50), name varchar(50), department int(50), salary int(50));
insert into employee values (1, 'a', 4, 90);
insert into employee values (2, 'b', 5, 30);
insert into employee values (3, 'c', 5, 50);
insert into employee values (4, 'd', 5, 80);
insert into employee values (8, 'f', 7, 10);

$Q_{1}$ returns $1$ for the above table. See here: http://sqlfiddle.com/#!9/9acce/1

$Q_{2}$ returns empId of those employees who get salary more than the minimum salary offered in department $5$. It returns $1,3,4$ for the above table. See here: http://sqlfiddle.com/#!9/9acce/2

According the question the answer should be $1$ for the above table.

PS: The question implies that the required employee must not be from department $5$. 

Comments

if we add this 2 record in the above table which is mentionad in the link

 

insert into employee values (9, 'd', 7, 10);
insert into employee values (10, 'd', 7, 10);

 

then also query return 1

which is not correct answer.

---

Ans is incorrect. Would be correct only if >=changed to > for all databases. Try for yourself. make sure that the person drawing the highest salary overall is in dept 5

---

Your answer gave more than one way of looking for the correct answer to me thanks. Q2 was any like for not all but anyone it will work but moreover it will also work for emp of dept 5, I didn't even think of this. Great answer.

Answer 2

Q1: Returns all employees such that there is no employee in dept 5 having same or higher salary. Hence the answer.
Q2: Returns all employees having salary higher than the minimum salary in department 5.

So, answer should be A.

Comments

anyone ( कोई भी ) and any one ( कोई एक ) have different meanings in english.

---

@abhi19961   Read this comment..

https://gateoverflow.in/1259/gate2007-61?show=156352#c156352

---

anyone- anyone can touch mobile.(all member in a group have equal rights)
any one – any one have the right to touch mobile.(only single member in a group have rights)

Answer 3

To solve this type of question, we will take a sample table which includes all possible cases. In this case, we have to find out the employees whose salary is greater than salary of any employee in department 5. So we have taken two employees in department 5 with salary 5000 and 10000 and three other employees:

empId 1 whose salary is less than both employees of department 5.
empId 2 whose salary is greater than both employees of department 5.
empId 4 whose salary is less than one employee of department 5(empId 5) and greater than other employee of department 5(empId 3).

empId	name	department	salary
1	A	1	2000
2	B	2	12000
3	C	5	5000
4	D	3	7000
5	E	5	10000

So we will see whether two queries given in question work for these scenarios or not.

Q1 Select e.empId from employee e where not exists    

                       (Select * from employee s where s.department = “5” and s.salary >=e.salary) 

This is a correlated nested query. For every row in outer query, inner query is executed and result of inner query is used as an input of outer query. First row of employee table from outer query having e.empId=1 is passed to inner query, it will execute the inner query like:

select * from employee s where s.department=”5” and  s.salary>=2000(1.salary)

It will return rows with empId 3 and 5. But the outer query will not return anything for this row because not exists condition is failed.

For second row of employee table, inner query will return no row, and outer query will retun empId 2.

Similarly, we can do the same for other rows and the final output will be:

empId
2

Q2 Select e.empId from employee e   where e.salary > any     

(Select distinct salary From employee s Where s.department = “5”)

This type of query is independent nested query in which inner query is executed independently and its result is used in execution of outer query. Inner query will fetch distinct salaries of employees in department “5” as:

Select distinct salary From employee s Where s.department = “5”

its output will be 5000 and 10000.

The inner query will fetch those empId whose salary is greater than any salary from this set like:

Select e.empId from employee e   where e.salary > any(5000,10000)     

The final output will be:

empId
2
4
5

So the required output is drawn from Q2 but not Q1. Q1 is missing those rows where employee salary is greater than one employee of department 5 but less than other employee of department 5(empId 4). So answer will be (B).

Comments

for query Q1 how final output will be empId=2

Select e.empId
From employee e
Where not exists
    (Select * From employee s Where s.department = "5" and s.salary >= e.salary)

Select e.empId From employee e Where not exists (2) which retuns false for not exist.

Hence, empty set.

---

How can you say , ANY means ALL ?

ANY means that the condition will be true if the operation is true for any of the values in the range.

---

Where not exist given means in sql query nothing returned by subquery how could be the query 1 is correct

Answer 4

Answer Should be D.

Q1: It will also give all those employee name whose salary is greater or equal to all other employee of department 5. But i question only asked GREATER.

Q2. It will give all those employee name of Department 5  who have salary greater than anyone of the employee in department 5 but not ALL

Comments

This is the only correct  answer here