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 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
- A.
M = 4, N = 2
- B.
M = 5, N = 3
- C.
M = 2, N = 2
- D.
M = 3, N = 3
Attempted by 48 students.
Show answer & explanation
Correct answer: A
To find M, we evaluate SQ₁. The query joins R and S on R.A = S.C with the filter S.D < 7. Matching tuples: - R.A=1 matches S.C=1 (S tuple <3, 1, 4>, D=4<7). R tuples with A=1 are <1,2,3>, <1,2,0>, <1,3,1>, <1,4,2>. R.B values are 2, 2, 3, 4. - R.A=3 matches S.C=3 (S tuple <2, 3, 4>, D=4<7). R tuple with A=3 is <3,1,4>. R.B value is 1. Grouping by R.B yields distinct groups for B=1, 2, 3, 4. Thus M = 4 tuples returned.
To find N, we evaluate SQ₂. It groups S by B and filters groups where COUNT(DISTINCT D) > 1. - B=2 has tuples <2,3,7> and <2,3,4>. Distinct D are {7, 4}, count=2. Included. - B=1 has tuples <1,4,5> and <1,2,3>. Distinct D are {5, 3}, count=2. Included. - B=3 has tuple <3,1,4>. Distinct D is {4}, count=1. Excluded. Thus N = 2 tuples returned.
A video solution is available for this question — log in and enroll to watch it.