Puzzles on SQL/Oracle

[nirav]

Hello ,

In my company , I started a contest on Oracle..we would send two
questions on SQL, Pl-SQL and the fastest response with all correct
answer is the winner...this is getting some response and I am wondering
about where I could refer for material which would be good for the
contest...basically queries that are not too easy nor painstakingly
difficult..something that appeals the java programmer or the dot net
programmer and even tempts a QA guy to take a look and try to solve...

I think I have enough to keep going for next seven rounds or so but I
am exploring for getting better ideas...any pointers to such puzzles or
similar links etc I shall be thankful ..(I know of Steven Feuristein's
puzzles on toadsoft or some other site)

Thanks
Nirav

[Paul]

"nirav" <shiv...@gmail.com> wrote:

> In my company , I started a contest on Oracle..we would send two
> questions on SQL, Pl-SQL and the fastest response with all correct
> answer is the winner...

I have visions of cubicles and cublicles full of propeller-heads!
Sorry, only joking.

Take a look at Joe Celko's books.

Paul...

> Nirav

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 10.2.0.1 (Express Edition)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[Bruman]

As far as SQL questions go, be sure to do some on Analytic Functions.
They are highly valuable but in my experience not frequently used.

[nirav]

Thank you very much for sharing your views...

[DA Morgan]

Here's one of my personal favorites and generally a good interview
question too.

Assume two identical tables, one named "A" the other "B" with
identical column definitions. Assume that some rows in "A" are
duplicated in "B" and some in "B" are duplicated in "A" but each
table contains rows unique to that table.

Write a single SELECT statement that will retrieve all of the rows
from table "A" that are unique to "A", all the rows from "B" that
are unique to "B" and label each retrieved row with the name of
the table in which it was found.

Have fun (and yes there are multiple solutions).
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[EscVector]

On Dec 18, 1:52 pm, DA Morgan <damor...@psoug.org> wrote:
> Bruman wrote:
> > nirav wrote:
> >> Hello ,
>
> >> In my company , I started a contest on Oracle..we would send two
> >> questions on SQL, Pl-SQL and the fastest response with all correct
> >> answer is the winner...this is getting some response and I am wondering
> >> about where I could refer for material which would be good for the
> >> contest...basically queries that are not too easy nor painstakingly
> >> difficult..something that appeals the java programmer or the dot net
> >> programmer and even tempts a QA guy to take a look and try to solve...
>
> >> I think I have enough to keep going for next seven rounds or so but I
> >> am exploring for getting better ideas...any pointers to such puzzles or
> >> similar links etc I shall be thankful ..(I know of Steven Feuristein's
> >> puzzles on toadsoft or some other site)
>
> >> Thanks
> >> Nirav
>
> > As far as SQL questions go, be sure to do some on Analytic Functions.

> > They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview

> question too.
>
> Assume two identical tables, one named "A" the other "B" with
> identical column definitions. Assume that some rows in "A" are
> duplicated in "B" and some in "B" are duplicated in "A" but each
> table contains rows unique to that table.
>
> Write a single SELECT statement that will retrieve all of the rows
> from table "A" that are unique to "A", all the rows from "B" that
> are unique to "B" and label each retrieved row with the name of
> the table in which it was found.
>
> Have fun (and yes there are multiple solutions).
> --
> Daniel A. Morgan
> University of Washington

> damor...@x.washington.edu

> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

CREATE TABLE A
( COL1 CHAR(4),
COL2 NUMBER,
COL3 VARCHAR2(10));

begin
for x in 1..10
loop
insert into a values ('ab'||x, x,'NONUNIQUE');
end loop;
end;
/

create table B as select * from a;

begin
for x in 1..10
loop
insert into a values ('a'||x, x,'UNIQUE');
end loop;
end;
/

begin
for x in 1..10
loop
insert into b values ('b'||x, x,'UNIQUE');
end loop;
end;
/

commit;

(select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
b.col1,b.col2,b.col3, 'TABA' from b )
union
(select b.col1,b.col2,b.col3 ,'TABB' from b minus select
a.col1,a.col2,a.col3 ,'TABB' from a );

[DA Morgan]

EscVector wrote:
>
> On Dec 18, 1:52 pm, DA Morgan <damor...@psoug.org> wrote:

>> Bruman wrote:
>>> nirav wrote:
>>>> Hello ,
>>>> In my company , I started a contest on Oracle..we would send two
>>>> questions on SQL, Pl-SQL and the fastest response with all correct
>>>> answer is the winner...this is getting some response and I am wondering
>>>> about where I could refer for material which would be good for the
>>>> contest...basically queries that are not too easy nor painstakingly
>>>> difficult..something that appeals the java programmer or the dot net
>>>> programmer and even tempts a QA guy to take a look and try to solve...
>>>> I think I have enough to keep going for next seven rounds or so but I
>>>> am exploring for getting better ideas...any pointers to such puzzles or
>>>> similar links etc I shall be thankful ..(I know of Steven Feuristein's
>>>> puzzles on toadsoft or some other site)
>>>> Thanks
>>>> Nirav
>>> As far as SQL questions go, be sure to do some on Analytic Functions.

>>> They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview

>> question too.
>>
>> Assume two identical tables, one named "A" the other "B" with
>> identical column definitions. Assume that some rows in "A" are
>> duplicated in "B" and some in "B" are duplicated in "A" but each
>> table contains rows unique to that table.
>>
>> Write a single SELECT statement that will retrieve all of the rows
>> from table "A" that are unique to "A", all the rows from "B" that
>> are unique to "B" and label each retrieved row with the name of
>> the table in which it was found.
>>
>> Have fun (and yes there are multiple solutions).
>> --
>> Daniel A. Morgan
>> University of Washington

>> damor...@x.washington.edu

>> (replace x with u to respond)

>> Puget Sound Oracle Users Groupwww.psoug.org
>
> CREATE TABLE A
> ( COL1 CHAR(4),
> COL2 NUMBER,
> COL3 VARCHAR2(10));
>
> begin
> for x in 1..10
> loop
> insert into a values ('ab'||x, x,'NONUNIQUE');
> end loop;
> end;
> /
>
> create table B as select * from a;
>
>
> begin
> for x in 1..10
> loop
> insert into a values ('a'||x, x,'UNIQUE');
> end loop;
> end;
> /
>
> begin
> for x in 1..10
> loop

> insert into a values ('b'||x, x,'UNIQUE');

> end loop;
> end;
> /
>
> commit;
>
> (select a.col1 ,a.col2 ,a.col3, 'TABA' from a minus select
> b.col1,b.col2,b.col3, 'TABA' from b )
> union
> (select b.col1,b.col2,b.col3 ,'TABB' from b minus select
> a.col1,a.col2,a.col3 ,'TABB' from a );

I'll just offer one, minor, critique.

Given that the two SELECT statements must be obtaining different
results, and no overlap is possible, UNION ALL would be more
efficient.

How about a second solution? Or a third? <g>

[Charles Hooper]

This is not a comment against EscVector...

I wonder if I would have passed this exam, had this been the only
question on the exam. Quoting: "Write a single SELECT statement that
will retrieve all of the rows..." I counted four SELECT statements in
the answer provided by EscVector. Was the requirement a single SQL
statement? Did I misunderstand the question?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

[Charles Hooper]

I hate the idea of failing an exam:
CREATE TABLE TABLE_A (
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
COL3 VARCHAR2(20));

CREATE TABLE TABLE_B (
COL1 VARCHAR2(20),
COL2 VARCHAR2(20),
COL3 VARCHAR2(20));

INSERT INTO TABLE_A VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_A VALUES ('TEST2A','TEST2B','TEST2C');
INSERT INTO TABLE_A VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_A VALUES ('TEST4A','TEST4B','TEST4C');
INSERT INTO TABLE_A VALUES ('TEST5A','TEST5B','TEST5C');

INSERT INTO TABLE_B VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST2A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_B VALUES ('TEST5A','TEST5B','TEST5C');

SELECT DISTINCT
NVL(A.COL1,B.COL1) COL1,
NVL(A.COL2,B.COL2) COL2,
NVL(A.COL3,B.COL3) COL3,
NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
TABLE_A A
FULL OUTER JOIN
TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
(A.COL1 IS NULL OR B.COL1 IS NULL)
OR (A.COL2 IS NULL OR B.COL2 IS NULL)
OR (A.COL3 IS NULL OR B.COL3 IS NULL);

COL1 COL2 COL3 FROM_TABLE
TEST2A TEST2B TEST2C TABLE A
TEST4A TEST4B TEST4C TABLE A
TEST2A TEST1B TEST1C TABLE B

Extra credit:
SELECT DISTINCT
NVL(A.COL1,B.COL1) COL1,
NVL(A.COL2,B.COL2) COL2,
NVL(A.COL3,B.COL3) COL3,
NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
TABLE_A A
FULL OUTER JOIN
TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE
UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(A.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(A.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<>
UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(B.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(B.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
(NVL(B.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8');

COL1 COL2 COL3 FROM_TABLE
TEST2A TEST2B TEST2C TABLE A
TEST4A TEST4B TEST4C TABLE A
TEST2A TEST1B TEST1C TABLE B

Is more than one SELECT acceptable?

[DA Morgan]

The intention was a single SQL statement ... I should have written it
more clearly and for that I apologize.

The point though, much like with new math, is not just to get the right
answer but to observe how someone approaches the problem.

[Charles Hooper]

Sorry, I had bad flashbacks of a couple test questions from years ago.

Please note that the DISTINCT in the previous solution is not required.
I mistakenly inserted the rows twice into TABLE_A, and did not notice
that mistake until the solution was posted.

Here are a couple more solutions, using more than one SELECT in a SQL
statement:
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

WITH M AS (
SELECT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A
INTERSECT
SELECT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
M
WHERE
A.COL1=M.COL1(+)
AND A.COL2=M.COL2(+)
AND A.COL3=M.COL3(+)
AND M.COL1 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
M
WHERE
B.COL1=M.COL1(+)
AND B.COL2=M.COL2(+)
AND B.COL3=M.COL3(+)
AND M.COL1 IS NULL;

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
WHERE
(A.COL1,A.COL2,A.COL3) NOT IN (
SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B)
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B
WHERE
(B.COL1,B.COL2,B.COL3) NOT IN (
SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A);

SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A,
(SELECT DISTINCT
B.COL1,
B.COL2,
B.COL3
FROM
TABLE_B B) B
WHERE
A.COL1=B.COL1(+)
AND A.COL2=B.COL2(+)
AND A.COL3=B.COL3(+)
AND B.COL3 IS NULL
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B,
(SELECT DISTINCT
A.COL1,
A.COL2,
A.COL3
FROM
TABLE_A A) A
WHERE
B.COL1=A.COL1(+)
AND B.COL2=A.COL2(+)
AND B.COL3=A.COL3(+)
AND A.COL3 IS NULL;

SELECT
COL1,
COL2,
COL3,
FROM_TABLE
FROM
(SELECT
COL1,
COL2,
COL3,
FROM_TABLE,
COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3)
NUM_TABLES
FROM
(SELECT
A.COL1,
A.COL2,
A.COL3,
'TABLE A' FROM_TABLE
FROM
TABLE_A A
UNION ALL
SELECT
B.COL1,
B.COL2,
B.COL3,
'TABLE B' FROM_TABLE
FROM
TABLE_B B))
WHERE
NUM_TABLES=1;

[EscVector]

Not that I could do better, but I put this out there on purpose. I
figured you meant use only one Select in the SQL statement, but I
posted the first thing that came to mind. The rational is that it is a
"single" non-procedural SQL statement. :) I used multiple selects in
one statement. You were asking for a single select in one statement.
I'd like to add a bonus question: Which is the optimal statement?

"Get it done or Make it Perform". The typical approach is as I have
posted; get it done. Performance always comes second. Prereq: clarify
the work before investing effort. Since I did the fastest thing I
could with almost zero effort, you were able to clarify the work for me
while I ate dinner :) Is this what they call "agile"?

[EscVector]

Here's the plan for my first solution.
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 1280 | 12 (84)|
00:00:01 |
| 1 | SORT UNIQUE | | 40 | 1280 | 12 (84)|
00:00:01 |
| 2 | UNION-ALL | | | | |
|
| 3 | MINUS | | | | |
|
| 4 | SORT UNIQUE | | 20 | 320 | 3 (34)|
00:00:01 |
| 5 | TABLE ACCESS FULL| A | 20 | 320 | 2 (0)|
00:00:01 |
| 6 | SORT UNIQUE | | 20 | 320 | 3 (34)|
00:00:01 |
| 7 | TABLE ACCESS FULL| B | 20 | 320 | 2 (0)|
00:00:01 |
| 8 | MINUS | | | | |
|
| 9 | SORT UNIQUE | | 20 | 320 | 3 (34)|
00:00:01 |
| 10 | TABLE ACCESS FULL| B | 20 | 320 | 2 (0)|
00:00:01 |
| 11 | SORT UNIQUE | | 20 | 320 | 3 (34)|
00:00:01 |
| 12 | TABLE ACCESS FULL| A | 20 | 320 | 2 (0)|
00:00:01 |
------------------------------------------------------------------------------

[EscVector]

I renamed my original A and B tables to save work and make things
equal.
Query results from my first option match Charles' first option with
diff display order

Plan for Charles Option 1
----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 2262 | 10 (20)|
00:00:01 |
| 1 | HASH UNIQUE | | 39 | 2262 | 10 (20)|
00:00:01 |
| 2 | VIEW | | 39 | 2262 | 9 (12)|
00:00:01 |
| 3 | UNION-ALL | | | | |
|
|* 4 | FILTER | | | | |
|
|* 5 | HASH JOIN OUTER | | 20 | 640 | 5 (20)|
00:00:01 |
| 6 | TABLE ACCESS FULL| TABLE_A | 20 | 320 | 2 (0)|
00:00:01 |
| 7 | TABLE ACCESS FULL| TABLE_B | 20 | 320 | 2 (0)|
00:00:01 |
|* 8 | HASH JOIN ANTI | | 19 | 608 | 5 (20)|
00:00:01 |
| 9 | TABLE ACCESS FULL | TABLE_B | 20 | 320 | 2 (0)|
00:00:01 |
| 10 | TABLE ACCESS FULL | TABLE_A | 20 | 320 | 2 (0)|
00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("A"."COL1" IS NULL OR "B"."COL1" IS NULL OR "A"."COL2" IS
NULL OR "B"."COL2" IS NULL OR "A"."COL3" IS NULL OR
"B"."COL3" IS NULL)
5 - access("A"."COL3"="B"."COL3"(+) AND "A"."COL2"="B"."COL2"(+) AND
"A"."COL1"="B"."COL1"(+))
8 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2" AND
"A"."COL3"="B"."COL3")

[EscVector]

Best statement so far (cost comparison... and yes I know cost isn't
everything)
23:36:10 @splat>
23:36:10 @splat> SELECT
23:36:10 2 A.COL1,
23:36:10 3 A.COL2,
23:36:10 4 A.COL3,
23:36:10 5 'TABLE A' FROM_TABLE
23:36:10 6 FROM
23:36:10 7 TABLE_A A
23:36:10 8 WHERE
23:36:10 9 (A.COL1,A.COL2,A.COL3) NOT IN (
23:36:10 10 SELECT DISTINCT
23:36:10 11 B.COL1,
23:36:10 12 B.COL2,
23:36:10 13 B.COL3
23:36:10 14 FROM
23:36:10 15 TABLE_B B)
23:36:10 16 UNION ALL
23:36:10 17 SELECT
23:36:10 18 B.COL1,
23:36:10 19 B.COL2,
23:36:10 20 B.COL3,
23:36:10 21 'TABLE B' FROM_TABLE
23:36:10 22 FROM
23:36:10 23 TABLE_B B
23:36:10 24 WHERE
23:36:10 25 (B.COL1,B.COL2,B.COL3) NOT IN (
23:36:10 26 SELECT DISTINCT
23:36:10 27 A.COL1,
23:36:10 28 A.COL2,
23:36:10 29 A.COL3
23:36:10 30 FROM
23:36:10 31 TABLE_A A);

COL1 COL2 COL3 FROM_TA
---- ---------- ---------- -------
a1 1 UNIQUE TABLE A
a2 2 UNIQUE TABLE A
a3 3 UNIQUE TABLE A
a4 4 UNIQUE TABLE A
a5 5 UNIQUE TABLE A
a6 6 UNIQUE TABLE A
a7 7 UNIQUE TABLE A
a8 8 UNIQUE TABLE A
a9 9 UNIQUE TABLE A
a10 10 UNIQUE TABLE A
b1 1 UNIQUE TABLE B
b2 2 UNIQUE TABLE B
b3 3 UNIQUE TABLE B
b4 4 UNIQUE TABLE B
b5 5 UNIQUE TABLE B
b6 6 UNIQUE TABLE B
b7 7 UNIQUE TABLE B
b8 8 UNIQUE TABLE B
b9 9 UNIQUE TABLE B
b10 10 UNIQUE TABLE B

20 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1151068709

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 640 | 44 (50)|
00:00:01 |
| 1 | UNION-ALL | | | | |
|
|* 2 | FILTER | | | | |
|
| 3 | TABLE ACCESS FULL| TABLE_A | 20 | 320 | 2 (0)|
00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE_B | 1 | 16 | 2 (0)|
00:00:01 |
|* 5 | FILTER | | | | |
|
| 6 | TABLE ACCESS FULL| TABLE_B | 20 | 320 | 2 (0)|
00:00:01 |
|* 7 | TABLE ACCESS FULL| TABLE_A | 1 | 16 | 2 (0)|
00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_B" "B" WHERE
LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND
LNNVL("B"."COL3"<>:B3)))
4 - filter(LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND
LNNVL("B"."COL3"<>:B3))
5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_A" "A" WHERE
LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND
LNNVL("A"."COL3"<>:B3)))
7 - filter(LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND
LNNVL("A"."COL3"<>:B3))

[Charles Hooper]

Did you notice the "2 - filter" and "5 - filter" line in the Predicate
Information? Also notice the "LNNVL("B"."COL1"<>:B1)" syntax. Oracle
"one upped" my solution by changing the NOT IN syntax to NOT EXISTS.
It might be interesting to see how these different SQL statements
behave with a very large data set. I suspect that we find significant
differences in execution time with the various solutions. Thanks for
posting the explain plans.

[nirav]

On Dec 21, 5:06 pm, "Charles Hooper" <hooperc2...@yahoo.com> wrote:
> EscVector wrote:
> > EscVector wrote:
> > > EscVector wrote:
> > > > Here's the plan for my first solution.

> > > > ---------------------------------------------------------------------------­---

> > > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> > > > Time |

> > > > ---------------------------------------------------------------------------­---

> > > > | 0 | SELECT STATEMENT | | 40 | 1280 | 12 (84)|
> > > > 00:00:01 |
> > > > | 1 | SORT UNIQUE | | 40 | 1280 | 12 (84)|
> > > > 00:00:01 |
> > > > | 2 | UNION-ALL | | | | |
> > > > |
> > > > | 3 | MINUS | | | | |
> > > > |
> > > > | 4 | SORT UNIQUE | | 20 | 320 | 3 (34)|
> > > > 00:00:01 |
> > > > | 5 | TABLE ACCESS FULL| A | 20 | 320 | 2 (0)|
> > > > 00:00:01 |
> > > > | 6 | SORT UNIQUE | | 20 | 320 | 3 (34)|
> > > > 00:00:01 |
> > > > | 7 | TABLE ACCESS FULL| B | 20 | 320 | 2 (0)|
> > > > 00:00:01 |
> > > > | 8 | MINUS | | | | |
> > > > |
> > > > | 9 | SORT UNIQUE | | 20 | 320 | 3 (34)|
> > > > 00:00:01 |
> > > > | 10 | TABLE ACCESS FULL| B | 20 | 320 | 2 (0)|
> > > > 00:00:01 |
> > > > | 11 | SORT UNIQUE | | 20 | 320 | 3 (34)|
> > > > 00:00:01 |
> > > > | 12 | TABLE ACCESS FULL| A | 20 | 320 | 2 (0)|
> > > > 00:00:01 |

> > > > ---------------------------------------------------------------------------­---

>
> > > I renamed my original A and B tables to save work and make things
> > > equal.
> > > Query results from my first option match Charles' first option with
> > > diff display order
>
> > > Plan for Charles Option 1

> > > ---------------------------------------------------------------------------­-------

> > > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> > > Time |

> > > ---------------------------------------------------------------------------­-------

> > > | 0 | SELECT STATEMENT | | 39 | 2262 | 10 (20)|
> > > 00:00:01 |
> > > | 1 | HASH UNIQUE | | 39 | 2262 | 10 (20)|
> > > 00:00:01 |
> > > | 2 | VIEW | | 39 | 2262 | 9 (12)|
> > > 00:00:01 |
> > > | 3 | UNION-ALL | | | | |
> > > |
> > > |* 4 | FILTER | | | | |
> > > |
> > > |* 5 | HASH JOIN OUTER | | 20 | 640 | 5 (20)|
> > > 00:00:01 |
> > > | 6 | TABLE ACCESS FULL| TABLE_A | 20 | 320 | 2 (0)|
> > > 00:00:01 |
> > > | 7 | TABLE ACCESS FULL| TABLE_B | 20 | 320 | 2 (0)|
> > > 00:00:01 |
> > > |* 8 | HASH JOIN ANTI | | 19 | 608 | 5 (20)|
> > > 00:00:01 |
> > > | 9 | TABLE ACCESS FULL | TABLE_B | 20 | 320 | 2 (0)|
> > > 00:00:01 |
> > > | 10 | TABLE ACCESS FULL | TABLE_A | 20 | 320 | 2 (0)|
> > > 00:00:01 |

> > > ---------------------------------------------------------------------------­-------

> > ---------------------------------------------------------------------------­----

> > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> > Time |

> > ---------------------------------------------------------------------------­----

> > | 0 | SELECT STATEMENT | | 40 | 640 | 44 (50)|
> > 00:00:01 |
> > | 1 | UNION-ALL | | | | |
> > |
> > |* 2 | FILTER | | | | |
> > |
> > | 3 | TABLE ACCESS FULL| TABLE_A | 20 | 320 | 2 (0)|
> > 00:00:01 |
> > |* 4 | TABLE ACCESS FULL| TABLE_B | 1 | 16 | 2 (0)|
> > 00:00:01 |
> > |* 5 | FILTER | | | | |
> > |
> > | 6 | TABLE ACCESS FULL| TABLE_B | 20 | 320 | 2 (0)|
> > 00:00:01 |
> > |* 7 | TABLE ACCESS FULL| TABLE_A | 1 | 16 | 2 (0)|
> > 00:00:01 |

> > ---------------------------------------------------------------------------­----

>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > 2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_B" "B" WHERE
> > LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND
> > LNNVL("B"."COL3"<>:B3)))
> > 4 - filter(LNNVL("B"."COL1"<>:B1) AND LNNVL("B"."COL2"<>:B2) AND
> > LNNVL("B"."COL3"<>:B3))
> > 5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE_A" "A" WHERE
> > LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND
> > LNNVL("A"."COL3"<>:B3)))
> > 7 - filter(LNNVL("A"."COL1"<>:B1) AND LNNVL("A"."COL2"<>:B2) AND

> > LNNVL("A"."COL3"<>:B3))Did you notice the "2 - filter" and "5 - filter" line in the Predicate

> Information? Also notice the "LNNVL("B"."COL1"<>:B1)" syntax. Oracle
> "one upped" my solution by changing the NOT IN syntax to NOT EXISTS.
> It might be interesting to see how these different SQL statements
> behave with a very large data set. I suspect that we find significant
> differences in execution time with the various solutions. Thanks for
> posting the explain plans.
>
> Charles Hooper
> PC Support Specialist

> K&M Machine-Fabricating, Inc.- Hide quoted text -- Show quoted text -

Woww!! That's a lottt of food for me to think over!!! Thanks a ton!!!

[EscVector]

On visual, I'd have condemned the nested loop, but looks here to be the
winner based on filter rather than sort. The point, never assume
anything.... I missed that NOT EXISTS transformation. Good stuff.
I'm increasing the data set to see the diff. It seems that every day I
find I know less and less.

[DA Morgan]

Different puzzle. <g>

[Charles Hooper]

DA Morgan wrote:
> Charles Hooper wrote:

What I was looking for when I started building the second example was a
way to retrieve the binary values of all columns in a row (ragardless
of the column names), pass the binary values through DBMS_CRYPTO.HASH
to generate a hash key, and then use that to see if two rows resolved
to exactly the same hash key. I didn't find the function that I was
searching for to pull the binary values of all columns. Something like
this, but without listing each of the columns:
SELECT

NVL(A.COL1,B.COL1) COL1,
NVL(A.COL2,B.COL2) COL2,
NVL(A.COL3,B.COL3) COL3,
NVL2(A.COL1,'TABLE A','TABLE B') FROM_TABLE
FROM
TABLE_A A
FULL OUTER JOIN
TABLE_B B ON A.COL1=B.COL1 AND A.COL2=B.COL2 AND A.COL3=B.COL3
WHERE

DUMP(A.COL1||'^'||A.COL2||'^'||A.COL3)<>DUMP(B.COL1||'^'||B.COL2||'^'||B.COL3);

[DA Morgan]

I personally like the question because, as I stated before, it allows
the interviewer to really get a sense of someone's skills as well as
how they approach a problem.

Other approaches I've seen include an INTERSECT or INNER JOIN and then
filtering out anything in the intersection.

[Charles Hooper]

There are a lot of variants - I agree that it is a good question. I
posted a couple solutions that used an ANSI FULL OUTER JOIN, a couple
that used INTERSECT, another that used NOT IN, another using an inline
view and left outer joins (simulating the NOT IN, just more efficient
on older versions of Oracle), and even one that used an analytic
function. EscVector posted a method using MINUS syntax. I am sure
that there are still methods ways.

Is there no method of retrieving the byte values of all columns in a
row without listing the column names?

[DA Morgan]

Not of which I am aware.

[Paul]

DA Morgan <damo...@psoug.org> wrote:

<HUUUUGGGGEEEE SNIp...>

> Different puzzle. <g>

Come on Daniel, as self-appointed moderator of this group, at least
abide by the netiquette rules which you seek to persuade everyone else
to follow.

If I had a cent for every time I saw you write (paraphrase) "Don't top
post - how difficult is it to scroll down to the bottom?", I'd be a
rich man (I do agree with you though!).

I would retort: how difficult is it to delete lines from a post in
order to add TWO WORDS? 170 odd lines of included text for two words
is a breach of netiquette, please apologise and don't do it again.
Some people are still on dial-up!

Thank you.

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 10.2.0.1 (Express Edition)
Interbase 6.0.2.0;

[DA Morgan]

Paul wrote:
>
> DA Morgan <damo...@psoug.org> wrote:
>
>
> <HUUUUGGGGEEEE SNIp...>
>
>
>> Different puzzle. <g>
>
>
> Come on Daniel, as self-appointed moderator of this group, at least
> abide by the netiquette rules which you seek to persuade everyone else
> to follow.
>
>
> If I had a cent for every time I saw you write (paraphrase) "Don't top
> post - how difficult is it to scroll down to the bottom?", I'd be a
> rich man (I do agree with you though!).
>
>
> I would retort: how difficult is it to delete lines from a post in
> order to add TWO WORDS? 170 odd lines of included text for two words
> is a breach of netiquette, please apologise and don't do it again.
> Some people are still on dial-up!
>
>
> Thank you.
>
>
>
> Paul...

I don't know what you're looking at ... but I didn't top post my comment
to the best of my knowledge. And the fact that your HUGE SNIP is above
my post would lead toward confirming that fact.

[EscVector]

During a DBA interview, I was once asked a to "think out loud" and had
to answer a question similar to this:
"1. You have 8 marbles that weigh 1 ounce each, & 1 marble that weighs
1.5 ounces. You are unable to determine which is the heavier marble by
looking at them. You have a weighing scale that consists of 2 pans, but
the scale is only good for 2 total weighings. How can you determine
which marble is the heaviest 1 using the scale, & in 2 weighings?"
Question and answer from http://www.puzz.com/classicans.html

[DA Morgan]

I've seen this type of question before and I don't like them. Those that
succeed are almost always those that, not from intellectual brilliance
puzzle it out, but rather those that have heard it before and remember
the answer. I prefer questions that no-one has ever heard before and
that have no single solution.

[EscVector]

I actually did like the question. It seems easy now, but under the
weight of the interview, it was rather difficult. I got it in 3 tries
with 7 balls or something, the question wasn't exactly the same, but
the point they were after was more personality and grace under
pressure. I started out by stating Newtons laws and process of
elimination stuff. This question was after 3hrs of interview and was
thrown at me by an senior company exec.

One interview question I like is "what tech books do you like?" This
question usually doesn't go over well.

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> > <HUUUUGGGGEEEE SNIp...>

> > Come on Daniel, as self-appointed moderator of this group, at least

> > abide by the netiquette rules which you seek to persuade everyone else
> > to follow.

> > If I had a cent for every time I saw you write (paraphrase) "Don't top
> > post - how difficult is it to scroll down to the bottom?", I'd be a
> > rich man (I do agree with you though!).

This is the part of netiquette which you try to persuade everyone to
follow (and with which I agree!).

> > I would retort: how difficult is it to delete lines from a post in
> > order to add TWO WORDS? 170 odd lines of included text for two words
> > is a breach of netiquette, please apologise and don't do it again.
> > Some people are still on dial-up!

This is the bit where you failed to implement netiquette. *_YOU_*
quoted 170 lines to add two words.

That is a breach of netiquette and the one which I was pointing out.

> I don't know what you're looking at ... but I didn't top post my comment
> to the best of my knowledge. And the fact that your HUGE SNIP is above
> my post would lead toward confirming that fact.

I thought my post was clear.

point 1) Normally you enforce netiquette by telling people not to
top-post.

point 2) You failed netiquette abysmally by quoting 170 lines to add
two words.

point 3) Apply usual act of contrition - i.e. apologise and do not
repeat.

The "HUGE SNIP" was what you should have done when adding your two
words to a long post - you could/should have snipped it down to a few
lines and then added your 0.2c or two words.

[DA Morgan]

EscVector wrote:

> One interview question I like is "what tech books do you like?" This
> question usually doesn't go over well.

I always ask the following:

1. What was the last technical class you took?
2. What was the last technical book you read?
3. What was the last non-technical book you read?

[DA Morgan]

Paul wrote:

> This is the bit where you failed to implement netiquette. *_YOU_*
> quoted 170 lines to add two words.

Then I apologize.

Though as a matter of integrity I think will find that while I do
actively ask people to bottom post and not spam ... you'll likely
not find me getting on anyone's case about snipping.

Because quite frankly I don't like the snipping. Often people remove
the previous parts of the thread that make it comprehensible. I've
been criticized far more often for not remembering some earlier part
of a thread, long since removed, than for a lack of snipping.

So while you have my sincere apology you'll not, I hope, find any hypocrisy.

Regards

[Galen Boyer]

On Fri, 22 Dec 2006, damo...@psoug.org wrote:
> Paul wrote:
>
>> This is the bit where you failed to implement netiquette. *_YOU_*
>> quoted 170 lines to add two words.
>
> Then I apologize.
>
> Though as a matter of integrity I think will find that while I do
> actively ask people to bottom post and not spam ... you'll likely
> not find me getting on anyone's case about snipping.
>
> Because quite frankly I don't like the snipping. Often people remove
> the previous parts of the thread that make it comprehensible. I've
> been criticized far more often for not remembering some earlier part
> of a thread, long since removed, than for a lack of snipping.
>
> So while you have my sincere apology you'll not, I hope, find any
> hypocrisy.
>
> Regards

Maybe.
--
Galen Boyer

[nirav]

Hello,

How about Games Programming like chess programming or Tic-tac-Toe or
other games etc using SQL or Pl/SQL...has this been done?? I mean some
questions like how to find out how many different moves are possible
for next step in Chess or Tic Tac Toe etc is this something someone has
come across??

[Rene Nyffenegger]

I have once tried to create a sudoku solver in PL/SQL:
http://www.adp-gmbh.ch/blog/2005/september/6.html

Unfortunately, it has a bug, but still...

Regards

--
Rene Nyffenegger
http://www.adp-gmbh.ch

[nirav]

I have once tried to create a sudoku solver in PL/SQL:
> http://www.adp-gmbh.ch/blog/2005/september/6.html
>
> Unfortunately, it has a bug, but still...
>
> Regards
>
> --
> Rene Nyffenegger

Fantastic!!!!!! That was exactly something I was looking for!! I guess
though that this might be quite trying for an .net or java programmer
so I would trim it down to the solution of "Magic Square" ...as the
Sudoku is a combination of sevaral "magic squares" right?....Will try
to work it out but this is great thanks so much for sharing!!

[Paul]

DA Morgan <damo...@psoug.org> wrote:

> > This is the bit where you failed to implement netiquette. *_YOU_*
> > quoted 170 lines to add two words.

> Then I apologize.

> Though as a matter of integrity I think will find that while I do
> actively ask people to bottom post and not spam ... you'll likely
> not find me getting on anyone's case about snipping.

Indeed. But then, you probably have super-wider bandwidth supplied by
the University of Washington. Not everyone does. Not getting on
people's case about snipping is a cop-out - either you practice
netiquette or you don't - it's like manners, it's an ensemble, a
whole. You are not a polite person if you are only polite to little
old ladies and very rude to everyone else.

> Because quite frankly I don't like the snipping. Often people remove
> the previous parts of the thread that make it comprehensible. I've
> been criticized far more often for not remembering some earlier part
> of a thread, long since removed, than for a lack of snipping.

That's where judgement comes in. It is subjective, in much the same
way spam is, as witnessed by numerous debates here. However, there are
rules of thumb, mine would be to add at least 30% of the material
quoted or thereabouts (*_NOT_* case in stone, BTW).

> So while you have my sincere apology you'll not, I hope, find any hypocrisy.

Apology accepted, at least by me. I'm not saying never quote long,
always snip to the bone &c., I'm just asking that people excersise
judgement and restraint instead of lazily top-posting or not snipping.

Two words - "DIAL-UP".

[DA Morgan]

Try mine:
http://www.psoug.org/sudoku.html

Though I'll not guarantee it doesn't have a flaw or twenty. <g>

And it has never seen even two seconds of optimization. Still it
handles the newspaper puzzles subsecond.

[DA Morgan]

Paul wrote:
>
> DA Morgan <damo...@psoug.org> wrote:
>
>
>>> This is the bit where you failed to implement netiquette. *_YOU_*
>>> quoted 170 lines to add two words.
>
>> Then I apologize.
>
>> Though as a matter of integrity I think will find that while I do
>> actively ask people to bottom post and not spam ... you'll likely
>> not find me getting on anyone's case about snipping.
>
>
> Indeed. But then, you probably have super-wider bandwidth supplied by
> the University of Washington.

While that is true I spend very little time at the U. The bandwidth I
use, almost all the time, is at the PSOUG office and is paid for by
all of the members.

> Two words - "DIAL-UP".

A good point well made. I will pay more attention in the future.

[nirav]

Hi Morgan,

The code looks simply awesome!! No there is no way a .net or java guy
could be attracted to it -thinking of my contest thing..but this is a
gem from which to derive simpler questions I guess...

Thanks..

[DA Morgan]

Glad you like the code ... but it is just a first-write so I've no doubt
substantial improvements can be found. If I get some time someday I can
think of several changes I would want to make.

Regards

[EscVector]

Anyone have pl/sql Sudoku puzzle generator?

[DA Morgan]

EscVector wrote:

> Anyone have pl/sql Sudoku puzzle generator?

Now that's nasty. ;-)

There was an article in Scientific American earlier this year on Sudoku
indicating, IIRC, the minimum number of provided values to achieve a
solvable puzzle is 17.

[EscVector]

I have all the known values downloaded.---
http://people.csse.uwa.edu.au/gordon/sudokumin.php
Could just plop them in a table. I'm interested in converting the code
from c++ to pl and was looking to see who might have done it.

[DA Morgan]

Nice link. And I'm game to move anything from C++ to PL/SQL just for the
fun of it. Not sure when I will get to it but it does confirm my
recollection that 17 is the magic number.