6k views

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 | 6k views
0
plz explain this question
+3

Logically any means universal quantifier.
"X is powerful than any guy on the planet."

but in SQL, ANY is used as an existential quantifier.
Where p.power > Any(select s.power from person s .........
Even if only one value satisfies, it will be considered.

+2
Best way to solve this kind of questions is to take an example table having data which will help to eliminate options and tick the correct choice. These kinds of questions come tricky in GATE and even the options are too close to eliminate with mental thinking or intuition. Don't be lazy to write both the tables(which are same; one for inner and other for outer) side by side during evaluation, if you ALWAYS want to answer these questions correctly!
0
0

in inner query of Q1

... not exists ( ... where s.department = "5" and s.salary >= e.salary )

so it becomes where s.department != "5" and s.salary < e.salary.
so it will take salary of employee who is NOT in department=5 and highest salary than
everyone in dept 5.

DOUBT: why the result of Query 2 shows 4 rows in COUNT(*), but showing one row
after giving select * too.


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


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

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

answered by Boss (33.8k points)
edited
+2
You are correct. But according to the question answer must be A rt?
+25

The questions says: we want to find the employees who get higher salary than anyone in the department 5.

What does 'anyone' mean here?

1. Salary of an employee x in the output set > Salaries of all employees in department 5. This will lead to answer as A.

2. Salary of an employee x in the output set > Salary of any one employee in department 5. This will lead to answer as B.

+15
Yes. And it should mean the 1st one. That's the beauty of English grammar :P
+42
Updated. This question should also be tagged as verbal-ability. :P
+2
+1
Kikndly insert 60 and then check the result
I guess D is the correct answer
0
So, here if there is ALL instead of ANY than both queries will be right??
+8
http://math.stackexchange.com/a/519871/309722

‘anyone’ indicates a universal quantifier
+1

Let's focus on the words :

Higher than anyone in the department number 5

Means let say we select an employee X who belongs to dept other than 5

we have an employee Y which belongs to department 5 and can have his salary range from a minimum to maximum value in that department.

So, X's salary should be greater than that of y irrespective of in what range the salary of Y is.

Means select employee X
Now if we randomly select an employee say Y from department 5 and consider worst case that this employee Y has highest salary in department 5, then for X to be listed in Result Set, X's Salary should be  strictly greater(and not equal) than that of Y.

So Q1 gives correct output according to the specification.

0
yes...
+7

Well any does not always means for all quantifier. It depends upon the sentence.We have a sql function Any which says atleast one should be satisfied. If i say "Has anyone completed the task?" then it means is there exists someone who has done the task.But if i say that my height is taller then anyone of you means i am taller than all of you.So in question above it says anyone from department 5 means if i pick anyone from department 5 then the employee coming in the output should have higher salary than that.

https://math.stackexchange.com/questions/509520/the-use-of-any-as-opposed-to-every/509828#509828

0

@ Ayush Upadhyaya can u tell in an easy way.

+3
Consider there are 3 employees in department number 5

A - Rs 35000

B- Rs 40000

C - Rs 45000

Now we want to get all those employees whose salary is greater than anyone in department number 5

IF you use Q2, then Suppose for emp X belonging to some other dept, having salary as Rs 35001 will be selected

but this person X still has his salary less than 2 persons belonging to dept 5

So do you think that X's salary is higher than anyone in dept number 5(Means you randomly pick any person from dept number 5 and X's salary should be strictly greater and not even equal to the person you picked from dept 5)

But If X salary is Rs 50000, then whatever person you pick from Dept5 I don't have to worry because now he has his salary greater than any(or all) persons in dept 5.
+11

There are 2 words- ANYONE and ANY ONE. The space between two words makes a difference.         ANYONE - It's an Indefinite pronoun which refers to any person at all but not to particular individuals.           Any one (two words) - is an adjective phrase that refers to any single member of a group.

Here ANYONE (without space) is used in the question. So it refers to any arbitrary employee of department 5.So it is the case of the universal quantifier.

0

Salary greater then anyone in department 5.

greater then anyone in department 5 = greater then all the employees in department 5 = greater then any  random employee you select from department 5.

Obviously an employee x from department 5 cannot have salary greater then anyone in department 5, because x's salary cannot be greater then himself/herself.

0
at the place of 90 if we are placing 10, then the query will not return anything? because in the query they are asking for the emp having the salary greater than anyone in dep. no 5
0
empid(8) is also not exist in the inner query result. So why not 8 is also belong to the final output?? bcoz for outer query only empid is to be compared.

(confused for this part only)
0

@Ayush Upadhyaya In the selected answer for Query 1 we will get all those employee ID who are not in department 5 , no? as "Department = 5 " will be false for them so their empId will not be in inner loop hence will be existed in outer loop as well as in out put no? @Manoja Rajalakshmi A can you help here?

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.
answered by Veteran (407k points)
+2
why Q2 is not correct? it is not telling the employees having min sal , but the persons having salary greater than min sal of dept=5
0
sir significance of "Assuming that department 5 has more than one employee" ??
Query 1 : if cardinality of department 5 is 0 then,
Select * From employee s Where s.department = "5" and s.salary >= e.salary is empty set so NOT EXIST select all employee id..

Query 2 : if cardinality of department 5 is 0 then,
Select distinct salary From employee s Where s.department = "5" is empty set so ALL operator not going to select any employee id.

mean that statement for query 1 only..
am i right ??
0

you said that "ALL not selecting any employee id" means r u want to say All employee not selecting in 2nd statement  if cardinality of department 5 is 0? But why?

Though  if cardinality of department 5 cannot be 0 , it should be minimum 2 ,as per the given question

+6

@Arjun,

I think this question is ambigious.

Higher than anyone can also mean that Higher than even single 1 ! :(

How can we interpret questions like this without any mistakes in Exam ? Also why does higher than all is intended meaning ? Should quesiton be then written like  "Higher than everyone in dept 5"  ?

Anyone can either mean Everyone  or Even Someone ! I'm confused here ! (Meaning !)

In this question , selected answer defines,

Anyone means all or any part of the group

So Here Higher than anyone, should really mean , Higher than "Any sinlge one"-> Which boils down to someone !

+5
higher than anyone- so here anyone can be "at least one" but this one can be any.

So, let the set be {1,2,3,4,5}

Now > should be true for any element in set, which would mean > all elements in set.
+1

if the question that is linked below's answer is A . Then its answer should be Q2 .Because I think there is a difference between any and all .

+1

Q1 give all those employee name whose salary is greater or equal to all other employee of department 5

employees who get higher salary than anyone in the department 5

which is $> \forall x$. Hence, both are same only.

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

@Arjun Sir, what if two employees have the same highest salary. and don't you think it should be s.salary > e.salary .??

+2
Did not get. If the highest salary in department 5 is say 100k, the given query is for selecting all employees getting salary > 100k (of course he/she cannot be from dept. 5). What is the confusion here?
+2

thanku sir, it is fine now .. :)

my doubt was-

(of course he/she cannot be from dept. 5).

0
Will be the q2 correct if ANY is replaced by ALL???
0

I have a doubt. If I take an example, say-

Employee Table

EmpId         Name        Department        Salary

001             S                     5                      100

002             T                     5                       200

009             U                    5                       300

003             V                    5                      500

004            W                    5                      400

005            Z                     4                      700

006            A                     3                      350

Then, according to Query 1, the output should be -       005  and 006

But it does not satisfy-   "employees who get salary higher than anyone in department 5 "

0
How can A be the ans.We want employees whose salary is greater than"ANYONE" of the dept="5'".So having salary higher than the minimum salary of employee of dept=5 will do.

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

answered by Active (1.9k points)
+1

@thepeeyoosh Awesome explanation! Simply the best. Actually the answer is (a) option because ANY means for ALL, but the way you explained the whole procedure about everything especially line by line. I so loved it.

Thanks!

+2
Any = All

Ans should be A

Good Explanation !

1
2