recategorized by
5,390 views
5 votes
5 votes

Consider the following ORACLE relations:

R(A, B, C)={<1, 2, 3>, <1, 2, 0>, <1, 3, 1>, <6, 2, 3>, <1, 4, 2>, <3, 1, 4>}

S(B, C, D)={<2, 3, 7>, <1, 4, 5>, <1, 2, 3>, <2, 3, 4>, <3, 1, 4>}

Consider teh following two SQL queries:

SQ$_1$; SELECT R.B, AVG(S.B) FROM R, S WHERE R.A=S.C AND S.D<7 GROUP BY R.B

SQ$_2$: SELECT DISTINCT S.B, MIN(S-C) FROM S GROUP BY S.B HAVING COUNT (DISTINCT S.D)>1;

If M is the number of tuples returned by SQ$_1$ and If N is the number of tuples returned by SQ$_2$ then

  1. M=4, N=2
  2. M=5, N=3
  3. M=2, N=2
  4. M=3, N=3
recategorized by

4 Answers

6 votes
6 votes

ANS : M = 4 , N =2

output after RxS in SQ1

R.A R.B R.C S.B S.C S.D
1 2 3 3 1 4
1 2 0 3 1 4
1 3 1 3 1 4
1 4 2 3 1 4
3 1 4 2 3 7
3 1 4 2 3 4

 final output of SQ1 :

R.B AVG(S.B)
2 3
3 3
4 3
1 2

SQ2 output :

S.B MIN(S.C)
  1     2
  2     3
2 votes
2 votes

Ans M=5, N=4

SQ1:For 1st query it will select 6 tuples by joining R and S ,where R.A and S.C are equal. Among that 5 are S.D<7

SQ2:Here we are selecting distinct value of S.D>1. In the picture , we can see only one value is not distinct here. So, getting 4 tuples

:

2 votes
2 votes
for SQ2

distinct S.B       count(distinct s.d)

1                              2

2                             2

3                              1

 

but the third tuple is not taken because count(distinct s.d) >1
Answer:

Related questions

2 votes
2 votes
1 answer
4