Consider the following ORACLE relations : R(A, B, C)={<1, 2, 3>, <1, 2, 0>,…
2016
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 the following two SQL queries SQ1 and SQ2 :
SQ1 : SELECT R⋅B, AVG (S⋅B)
FROM R, S
WHERE R⋅A = S⋅C AND S⋅D < 7
GROUP BY R⋅B;
SQ2 : 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 SQ1 and N is the number of tuples returned by SQ2 then
- A.
M = 4, N = 2
- B.
M = 5, N = 3
- C.
M = 2, N = 2
- D.
M = 3, N = 3
Attempted by 210 students.
Show answer & explanation
Correct answer: A
SQ1: Join R and S on R.A = S.C and apply the filter S.D < 7.
S rows with S.D < 7: (B=1,C=4,D=5), (B=1,C=2,D=3), (B=2,C=3,D=4), (B=3,C=1,D=4). (The row (B=2,C=3,D=7) is excluded because 7 is not < 7.)
Match R.A = S.C:
All R tuples with A = 1: (1,2,3), (1,2,0), (1,3,1), (1,4,2) match S tuple (B=3,C=1,D=4), contributing S.B = 3 to R.B values 2, 2, 3, 4 respectively.
R tuple (3,1,4) with A = 3 matches S tuple (B=2,C=3,D=4), contributing S.B = 2 to R.B = 1.
R tuple (6,2,3) with A = 6 has no matching S.C = 6, so it contributes nothing.
Grouping by R.B produces groups for B = 1, 2, 3, 4, so M = 4.
SQ2: Group S by S.B, compute MIN(S.C) and keep groups with more than one distinct S.D.
For B = 2: rows (2,3,7) and (2,3,4). MIN(C) = 3. DISTINCT D values = {7, 4}, count = 2 > 1, so this group qualifies.
For B = 1: rows (1,4,5) and (1,2,3). MIN(C) = 2. DISTINCT D values = {5, 3}, count = 2 > 1, so this group qualifies.
For B = 3: row (3,1,4) only. DISTINCT D values = {4}, count = 1, so this group does not qualify.
Therefore N = 2 (groups for B = 1 and B = 2).
Final answer: M = 4, N = 2