The questions in the quiz were, of course, all trick questions.

**Question 1: Answer D**

`SELECT * FROM T1 LEFT NATURAL JOIN T2;`

`SELECT * FROM T1 NATURAL LEFT JOIN T2;`

The second example shows the correct syntax for a natural outer join. In the first example, LEFT is interpreted as a table alias for T1 and so an inner join is used.

**QUESTION 2: Answer E**

`SELECT * FROM T1 INNER JOIN T2 ON C1=C2;`

`SELECT * FROM T1 OUTER JOIN T2 ON C1=C2;`

This is the same sort of issue. Any outer join needs to specify either LEFT or RIGHT but in the second example above OUTER is interpreted as a table alias for T1. They keyword INNER is optional. A third construct that also generates identical results is:

`SELECT * FROM T1 OUTER INNER JOIN T2 ON C1=C2;`

**Question 3: Answer D**

`SELECT *`

FROM t1, t2, t3

WHERE t1.c1 = t2.c2(+) AND t2.c2(+) = t3.c3;

`SELECT *`

FROM t1

LEFT JOIN t2 ON t1.c1 = t2.c2

RIGHT JOIN t3 ON t2.c2 = t3.c3;

Those of you attending my talk on Monday would know the answer to this one. The ambiguity in the first statement meant that it was illegal in releases prior to 12c but has now become legal for performance reasons. To express the results of the first query in ANSI syntax you would have to say:

`SELECT * FROM T1 CROSS JOIN T3 LEFT JOIN T2 ON T1.C1=T2.C2 AND T3.C3=T2.C2;`

**Question 4: Answer E**

`SELECT c1, COUNT (*)`

FROM t1

GROUP BY c1

HAVING COUNT (*) = 1;

`SELECT c1, COUNT (*)`

FROM t1

HAVING COUNT (*) = 1

GROUP BY c1;

I wouldn’t consider it best practice but the HAVING clause may precede the GROUP BY clause.

**Tie breaker 1:**

It is actually possible to solve the Paul and Sam problem using just two query blocks. Here is my solution:

WITH base

AS ( SELECT TRUNC (LEVEL / 97) + 2 a,

MOD (LEVEL, 97) + 3 b,

TRUNC (LEVEL / 97) + MOD (LEVEL, 97) + 5 s,

(TRUNC (LEVEL / 97) + 2) * (MOD (LEVEL, 97) + 3) p,

COUNT (*)

OVER (

PARTITION BY (TRUNC (LEVEL / 97) + 2)

* (MOD (LEVEL, 97) + 3))

p_cnt1,

COUNT (*)

OVER (

PARTITION BY (TRUNC (LEVEL / 97) + 2)

+ (MOD (LEVEL, 97) + 3))

s_cnt1

FROM DUAL b

WHERE TRUNC (LEVEL / 97) <= MOD (LEVEL, 97)

CONNECT BY LEVEL <= 97 * 97)

-- Main query block

SELECT a,b,p,s

FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s

WHERE b.s_cnt1 > 1 AND p.s IS NULL

GROUP BY b.p

HAVING COUNT (*) = 1

MODEL UNIQUE SINGLE REFERENCE RETURN UPDATED ROWS

DIMENSION BY (COUNT (*) OVER (PARTITION BY MAX (b.s)) s_cnt)

MEASURES (MAX (b.a) a, MAX (b.b) b, b.p, MAX (b.s) s)

RULES

(a [1] = a[1]);

The first query block is easy enough to understand. We generate all possible pairs of numbers where A is the lower and B is the higher. P_CNT1 is the number of rows with the same product and S_CNT1 is the number of rows with the same sum.

To understand the main query block we need to build it up in stages.

Let us start with this:

`.....`

SELECT a,b,p,s

FROM base b

WHERE b.s_cnt1 > 1

AND b.s NOT IN (SELECT s

FROM base p

WHERE p.p_cnt1 = 1);

Here we select the just the rows where Sam doesn’t know and Sam knew Paul didn’t know because there is no pair of numbers with the same sum where Paul could know. We can get rid of the subquery with an outer join.

…

`SELECT b.a,`

b.b,

b.p,

b.s

FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s

WHERE b.s_cnt1 > 1 AND b.p_cnt1 > 1 AND p.s IS NULL;

In the above example the last predicate selects just the rows preserved by the outer join by virtue of not matching.

We can now move on to identify the rows where Paul can now declare that he knows. For example, if Paul had been given the product 18, he would know that the numbers couldn’t be 3 and 6 because then Sam would have been given the number 9 and Sam wouldn’t have known in advance that Paul hadn’t been given the number 14 and hence have known the answer. Once Paul is able to eliminate 3 and 6 from consideration he knows that the numbers must be 2 and 9.

We can identify the 86 rows where Paul now knows by using a GROUP BY with a HAVING clause.

`...`

SELECT MAX (b.a) a,

MAX (b.b) b,

b.p,

MAX (b.s) s,

COUNT (*) OVER (PARTITION BY MAX (b.S)) s_cnt

FROM base b LEFT JOIN base p ON p.p_cnt1 = 1 AND b.s = p.s

WHERE b.s_cnt1 > 1 AND b.p_cnt1 > 1 AND p.s IS NULL

GROUP BY b.p

HAVING COUNT (*) = 1;

The MAX aggregate functions could be MIN or even AVG because in the result set we are aggregating just one row. The analytic function identifies the number of possibilities that Sam can now consider: the number out of the remaining 86 rows with identical values for the sum. A visual inspection of these 86 rows yields the one possibility where Sam now knows the answer. But how can we select that row without another query block?

The answer is to use the MODEL clause.

First of all, let us get the MODEL clause setup without attempting to restrict the rows. When a model clause is used the aggregate and analytic expressions move out of the SELECT list and into the PARTITION, DIMENSION, and MEASURES sub-clauses. We ultimately want to look up using S_CNT so that goes in the DIMENSION clause.

`...`

SELECT a,b,p,s,s_cnt

FROM paul_knows b LEFT JOIN paul_knows p ON p.p_cnt1 = 1 AND b.s = p.s

WHERE b.s_cnt1 > 1 AND p.s IS NULL

GROUP BY b.p

HAVING COUNT (*) = 1

MODEL UNIQUE SINGLE REFERENCE

DIMENSION BY (COUNT (*) OVER (PARTITION BY MAX (b.s)) s_cnt)

MEASURES (MAX (b.a) a, MAX (b.b) b, b.p, MAX (b.s) s)

RULES

();

The above construct generates the same 86 rows as the previous example. The UNIQUE SINGLE REFERENCE bit is used to supress a check the SQL engine makes; a reference to a measure `a[3]`

would appear to reference a single cell but would actually reference 6. By specifying UNIQUE SINGLE REFERENCE we promise not to do anything naughty like that and the error is supressed.

To restrict the rows further we add the keywords RETURN UPDATED ROWS. If that is all we did we would get no rows returned because there are no rules yet to update rows. The final thing to do is to update the single row that is our puzzle answer. `a[1] = a[1]`

is a dummy update to identify the one row (a= 4 and b = 13) that is our puzzle answer. We could have used any measure such as `b[1] = b[1]+0.`

## Leave a Reply