Consider the following ORACLE relations: R (A, B, C) = { <1, 2, 3>, <1, 2, 0>,…

Concept

SQL evaluates the logical stages of a grouped query in this order: FROM forms the row source, WHERE filters rows, GROUP BY forms groups, HAVING filters groups, and SELECT computes the displayed columns.

A grouped SELECT returns one result row per surviving group. Aggregate functions such as AVG and MIN determine values inside those rows; DISTINCT removes duplicate result rows only after SELECT.

Application

1. SQ₁ FROM stage: R, S denotes the Cartesian product. The WHERE clause first requires S.D < 7, so <2, 3, 7> is excluded from S.

2. SQ₁ join stage: R.A = S.C leaves five joined rows. The S row <3, 1, 4> matches the four R rows with A = 1 and contributes R.B values 2, 2, 3, and 4 with S.B = 3. The S row <2, 3, 4> matches the R row with A = 3 and contributes R.B = 1 with S.B = 2.

3. SQ₁ GROUP BY and SELECT stages: grouping those rows by R.B gives groups 1, 2, 3, and 4. SELECT emits (R.B, AVG(S.B)) as (1, 2), (2, 3), (3, 3), and (4, 3). Hence M = 4.

4. SQ₂ FROM and GROUP BY stages: S.B = 1 has C values {4, 2} and D values {5, 3}; S.B = 2 has C values {3, 3} and D values {7, 4}; S.B = 3 has C value {1} and D value {4}.

5. SQ₂ HAVING stage: COUNT(DISTINCT S.D) is 2, 2, and 1 for B = 1, 2, and 3 respectively, so only B = 1 and B = 2 survive.

6. SQ₂ SELECT stage: MIN(S.C) gives output rows (1, 2) and (2, 3). Because GROUP BY S.B already produces one row per B value, DISTINCT removes nothing. Hence N = 2.

Cross-check

• SQ₁ has five WHERE-qualified joined rows but four groups because R.B = 2 occurs twice.

• SQ₂ starts with three S.B groups and HAVING removes exactly the B = 3 group; DISTINCT cannot change the remaining group count.

Therefore, M = 4 and N = 2.