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

  custom table check if wrong
 

---

Query 1  is selecting the MAX salary from Dept = 5 then searching if there exists anyone in the Employee table who gets higher salary then  max salary of dept 5 employee ----> this is what question is asking 

Query 2 is selecting the MIN salary from Dept = 5 then searching if there exists anyone in the Employee table who gets higher than him -----> wrong !!

is it correct ?

---

To correct query 2 , it would need to use >ALL instead of  >ANY.

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

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.

---

more like a grammar question than sql one

---

@abhirupb no actually you are not correctly interpreting the question, see carefully that question is asking about "we want to find the employees who get higher salary than anyone in the department 5"

so every employee will be compared with all the department 5 employees,

case 1: suppose highest salary is of an employee from some other department (let's say John from department 2), then John will be compared with everyone in department 5 and will NEVER be compared with himself as he is not from department 5, finally John will be output

 

case 2: suppose highest salary is of an employee from department 5 (let's say Jack from department 5), now Jack will be compared with everyone in department 5 including himself, so in this case output will be empty

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

@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)

---

this very helpful mate .Thanks

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

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

---

Similar examples I am getting a answer B . But how the answer is A.

In the phrases "higher than anyone" means "higher than everyone" 🤔. Let's say " I can beat anyone in the department 5" which is same replication of "if you beat the maximum in department 5 then you can beat anyone" .

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

---

Actually this is wrong answer, because we have to find the employee from all the departments who has the highest salary then any of the employee in the department 5. 
we >= is correct, 
if in worst case we assume only department 5 has employees: then

(Select * From employee s Where s.department = "5" and s.salary >= e.salary)
this query always have at least 1 tuple, so the overall output will be empty. 
This is exactly what we want! A person in Department 5 cannot have a strictly higher salary than everyone in Department 5, because they cannot have a higher salary than themselves.
so ouput will be NO EMPLOYEE EXIST.

Answer 5

EVERY ANSWER HERE IS WRONG EXCEPT THE ONE THAT HAS BEEN FLAGGED AS "WRONG ANSWER".

Query 1 would have been correct if > was used instead of >= Q1 would not return any result if the highest salary  from employee table is drawn by someone from Dept 5

Some people have attached images from sql  but they haven't included this case

So, Q1 is correct IF AND ONLY IF no one from dept 5 is drawing the highest salary

I ran the query multiple times on sql.

In no version of sql Q1 can EVER be correct if someone in dept 5 is getting the highest salary out of all employees. Try it for yourself.

And Q2 is wrong for obvious reasons

Comments

ONLY ANSWER A is correct : https://gateoverflow.in/1259/gate-cse-2007-question-61?show=548322#c548322

Answer 6

BEST