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>}
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 SQ₁ and SQ₂:
SQ₁: SELECT R.B, AVG (S.B)
FROM R, S
WHERE R.A = S.C AND S.D < 7
GROUP BY R.B;
SQ₂: 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₁ and N is the number of tuples returned by SQ₂ then
Show answerHide answer
Correct answer: (A) M = 4, N = 2
Explanation
SQ₁: S tuples with D < 7 have S.C = 4, 2, 3, 1. Joining with R.A: S.C = 1 matches R tuples with A = 1 (R.B = 2, 2, 3, 4) and S.C = 3 matches <3, 1, 4> (R.B = 1). Grouping by R.B gives groups 1, 2, 3, 4, so M = 4. SQ₂: S.B = 1 has distinct D {5, 3} (count 2), S.B = 2 has {7, 4} (count 2), S.B = 3 has {4} (count 1). The third group is not taken because COUNT(DISTINCT S.D) > 1 fails, so N = 2.