sql query

Question

Comments

Is it C?  not sure

---

Yes C is right answer. (3rd highest)

---

2 = means?

---

Deepanshu

 I was having doubt whether we could write like that or not.. but I took it in this way like if the subquery returns result = 2 (i.e. count(distinct(Emp2.salary)) =2) then

2=2 will be true and the outer query will print salary ...but if the subquery doesn't return 2 then

the where condition will be false and no salary will be printed for that case..

---

@MiNiPanda HOW R U SAYING C .???

NOT GETTING THAT

---

@MiNiPanda, your explanation is correct then what was the doubt.

@Deepanshu, suppose values in employee salary table 

ID	Salary
a	1
b	2
c	3
d	4

run inner query for every tuple-

for (a,1) count will be 3. 

for (b,2) count will be 2.

for (c,3) count will be 1.

for (d,4) count will be 0.

now only (b,2) tuple is match and 2 will be printed which is 3rd highest.

---

I was having doubt in the syntax  where 2=(subquery) ..haven't seen like this before.

---

similar kind of question https://gateoverflow.in/1400/gate2005-77-isro2016-55 and exactly same question came in BARC also .

---

@Shubhgupta i got your point .but your example above example is little wrong you are saying 2 willl be printed which is 3rd highest which is not i think.....

thanku for the point @MiNiPanda included :) :)

 

---

buddy in salary row 3rd highest is 2 only. Isn't it?

---

Deepanshu

If you check the query properly you will notice that it means that a's salary is lesser than 3 persons..

For tuple (a,1) compare will all the 4 tuples.. 1>1=F ,2>1= T ,3>1 =T ,4>1= T count=3 as 3 cases are true

---

your assumption is wrong try that gate 2005 question and come to this. you will understand better.

and (a,1) count is 3, it means that there are 3 rows which are having greater salary then a.

---

@Shubhgupta @MiNiPanda MY MISTAKE :/

I AM NOT THINKING ABOUT COORELATED RELATION

---

@minipanda

we can write it in that form also... Even in C also we can write in that way...

coming to co-related query, form a sample table which can give different result for different options, then run the query on that table... This is a simple method ( as per my opinion )

Answer 1

C. 

This is a correlated nested query. Where inner query gives a count of employee having more salary than the employee in outer query and 2 checks that only 2 people have more salary than him.