The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
0 votes
163 views

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

Can someone please explain the query?

asked in Databases by Boss (34.6k points) | 163 views
0
0
0
Select Rating from Professor P2
where 5>= (Select count(*) from professor P3
                   where P2.Rating <= P3.rating
                  );

If the rating of a professor is less than 6 ratings(<=5) including itself
then that rating will be selected in the query.
0
the output is really strange! I was expecting 8.5 (considered >= 5 by mistake)

EDIT:

For <=5 Given output is correct. :)
+1

@Mk Utkarsh .It selects those ratings which are less than or equal to $at most$ 5 ratings.

0

If the rating of a professor is less than 6 ratings(<=5) including itself
then that rating will be selected in the query.

9.7 is less than 2 ratings (9.7,9.8)

0
ok that's matching the result too but i'm not able to understand how?
+1
@Mk Utkarsh

it is first creating 2 copies  of Professor tables P2 and P3

and then comparing each value of P3 with P2
+1
8.5 is not selected because it is less than equal to 8.7, 9.7, 8.5, 9.8, 8.7 and 8.5, this count 6, and the condition is <=5 hence 8.5 is not selected.
0

 yes i think i understood. The query 

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

is executed 6 times i.e, for each row. am i right?

+1

@Mk Utkarsh, Pick the professors one by one from the $1^{st}$ copy of table i.e from P2 and match it with all the tuples from P3. 
For a particular tuple of P2, check how many tuples are returned from the inner query. If it is less than or equal to 5. output the rating of that P2's tuple. 

0
Edited the comment. sorry for the previous one.
0

@Mk Utkarsh

no

Actually think opposite

when it gets 5 or more in count value, it exits from loop.

means count value =0...........it executes loop 1 time gets value 9.8

go, next when count=1.......it executes loop 1 time gets value 9.7

.........

count=5 loop breaks

2 Answers

+2 votes
Best answer
<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
answered by Active (4.8k points)
selected by
+1
nicely explained
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

answered by Veteran (112k points)
0
@Mk Utkarsh got it?

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
49,814 questions
54,521 answers
188,387 comments
75,421 users