edited by
26,534 views
69 votes
69 votes

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")
  1. $Q_1$ is the correct query
  2. $Q_2$ is the correct query
  3. Both $Q_1$ and $Q_2$ produce the same answer
  4. Neither $Q_1$ nor $Q_2$ is the correct query
edited by

5 Answers

Best answer
48 votes
48 votes

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

edited by
19 votes
19 votes
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.
16 votes
16 votes

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

5 votes
5 votes

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

Answer:

Related questions