UGC NET CSE | June 2016 | Part 3 | Question: 11

Question

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

• A. M=4, N=2
• B. M=5, N=3
• C. M=2, N=2
• D. M=3, N=3

Comments

I think Arity of the relation will be beoz it is conditional join ??

---

instead of comment post it as answer

i have answered SQ2

---

i have a doubt related to ur sq1 solution as we know that select in sql doesnt discard duplicate tupple automatically then the final answer is having 5 tupples in my solution, in the final table of R.B and  AVG(S.B) i am getting two duplicate tupples of R.B=2......PLEASE HELP ME OUT OF IT

Answer 1

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

Answer 2

Ans M=5, N=4

SQ₁: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

SQ₂: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

:

Answer 3

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

Comments

Second attribute is min(s.c)..why u have written count??

Answer 4

    SQ1:4 tuples