UKOUG TECH14 SQL Quiz

If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Troubleshooting Oracle Performance (2nd edition) by Christian Antognini

 

Troubleshooting Oracle Performance, 2nd Edition provides a systematic approach to addressing the underlying causes of poor database application performance.

---

 

AND a you can also win a free copy of Expert Oracle SQL by Tony Hasler

Expert Oracle SQL provides a systematic approach for tuning individual SQL statements and shows how to stabilize production performance with a radical approach to managing object statistics.

---

 

To get a chance to win a copy of both of these books just leave a comment on this blog with your answers to these questions:

For each of the following pairs of SQL statements indicate whether:

A) They are both illegal
B) The first is legal and the second illegal
C) The second is legal and the first illegal
D) They are both legal but may provide different results
E) They are both legal statements guaranteed to return identical results (ignoring order)

You should assume an Oracle 12cR1 database containing tables T1, T2 and T3 in the current schema. Table T1 has a column C1, table T2 has a column C2, and table T3 has a column C3.  All tables have a column called ID.

Question 1:

SELECT * FROM T1 LEFT NATURAL JOIN T2;

SELECT * FROM T1 NATURAL LEFT JOIN T2;

Question 2:

SELECT * from T1 INNER JOIN T2 ON C1=C2;

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

Question 3:

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;

Question 4:

SELECT c1, COUNT (*)
FROM t1
GROUP BY c1
HAVING COUNT (*) = 1;

SELECT c1, COUNT (*)
FROM t1
HAVING COUNT (*) = 1
GROUP BY c1;

Tie breaker 1:

Consider this puzzle.

All the numbers from 2 to 99 are placed into a hat. John draws two numbers from the hat and whispers the product of the two numbers to Paul. John whispers the sum of the two numbers to Sam. Paul and Sam then have the following conversation:

Paul: I don’t know what the two numbers are.
Sam: I knew you didn’t. Neither did I.
Paul: I do now.
Sam: So do I.

Write an SQL query that runs on an Oracle database to identify the only possible pair of numbers that could have been extracted from the hat.

• The only database object you should reference is DUAL.
• Use the keyword SELECT as infrequently as possible.
• No PL/SQL. For the avoidance of doubt, PL/SQL used in part of an SQL statement in 12cR1 is not allowed.
• You cannot work out all or part of the answer yourself. So, for example, you may work out that if Sam knows the answer immediately then so does Paul. Your SQL cannot make this assumption.

Tie breaker 2:

The first three names drawn out of a hat of containing the names of all correct answers will win a copy of the two books.

Rules

• This competition is only available to attendees of the UKOUG Tech14 conference
• To stand a chance of wining you must post your answers to the quiz questions by 11:00 a.m. on Tuesday 9th December 2014
• You must be present at the Optimizer Round Table session at 12:00 on Tuesday 9th December to collect your prize. Otherwise another name will be draw from the hat.