957 views
0 votes
0 votes

Select Rating From professor P2
Where  5>=(Select count (*) From professor P3 Where P2.Rating <= P3.Rating);

Can someone please explain the query?

2 Answers

Best answer
2 votes
2 votes
<Ramesh, 8.7>

8.7<=8.7 -> count=1

8.7<=9.7 -> count=2

8.7<=8.5 -> count=2

8.7<=9.8 -> count=3

8.7<=8.7 -> count=4

8.7<=8.5 -> count=4

5>=4 ? YES, hence display 8.7

<Ram, 9.7>

9.7<=8.7 -> count=0

9.7<=9.7 -> count=1

9.7<=8.5 -> count=1

9.7<=9.8 -> count=2

9.7<=8.7 -> count=2

9.7<=8.5 -> count=2

5>=2 ? YES, hence display 9.7

<Vinod, 8.5>

8.5<=8.7 -> count=1

8.5<=9.7 -> count=2

8.5<=8.5 -> count=3

8.5<=9.8 -> count=4

8.5<=8.7 -> count=5

8.5<=8.5 -> count=6

5>=6 ? NO, hence DON'T display 8.5

<Kapil, 9.8>

9.8<=8.7 -> count=0

9.8<=9.7 -> count=0

9.8<=8.5 -> count=0

9.8<=9.8 -> count=1

9.8<=8.7 -> count=1

9.8<=8.5 -> count=1

5>=1 ? YES, hence display 9.8

<Raju, 8.7>

8.7<=8.7 -> count=1

8.7<=9.7 -> count=2

8.7<=8.5 -> count=2

8.7<=9.8 -> count=3

8.7<=8.7 -> count=4

8.7<=8.5 -> count=4

5>=4 ? YES, hence display 8.7

<Arjun, 8.5>

8.5<=8.7 -> count=1

8.5<=9.7 -> count=2

8.5<=8.5 -> count=3

8.5<=9.8 -> count=4

8.5<=8.7 -> count=5

8.5<=8.5 -> count=6

5>=6 ? NO, hence DON'T display 8.5
selected by
0 votes
0 votes

$Table<P_{2} >$ 

Pname Rating
   
Ramesh 8.7
Ram 9.7
Vinod 8.5
Kapil 9.8
Raju 8.7
Arjun 8.5

$Table<P_{3} >$ 

Pname Rating
   
Ramesh 8.7
Ram 9.7
Vinod 8.5
Kapil 9.8
Raju 8.7
Arjun 8.5

Step $1:$

Now, take Ramesh tuple from $P_{3}$ and check every tuple from $P_{2}$.

Now, question is is it greater than every tuple of $P_{2}?$

No. right?

It is not greater than $9.7$ of $P_{2}$

So, this tuple at first not selected.

Count still 0.

Step $2:$

Now, check for $9.7$ of $R_{3}$ with every tuple of $R_{2}$

but $9.7<9.8$

So, it also not selected

Like that go for 8.5 it will also not selected

Step $3:$

Now, check $9.8$ i

is it greater than or equal to every row of $P_{2}?$

yes 🙂

Now count will be $1$


Now Simierly u go again from first until count !=5

When count $5$ just exit from loop

Related questions

0 votes
0 votes
0 answers
1
kd..... asked Apr 30, 2019
659 views
Here why does the 5th query select * from employees natural join works_on where PID = 'X' AND PID='Y'; is not workingThe queries are The output are
0 votes
0 votes
1 answer
2
Shamim Ahmed asked Nov 12, 2018
393 views
In this question:-https://gateoverflow.in/8225/gate2015-1-27I am not understanding how the query is returning 2 .I think DISTINCT must be there to give the correct answer...
0 votes
0 votes
1 answer
4
Satbir asked Dec 4, 2018
287 views
please give an example to differentiate between self join , natural join and join operation.