Re: Puzzles on SQL/Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 21 Dec 2006 14:35:53 -0800
Message-ID: <1166740552.782988@bubbleator.drizzle.com>

Charles Hooper wrote:
> DA Morgan wrote:

>> Charles Hooper wrote:
>>> 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?
>>>
>>> Charles Hooper
>>> PC Support Specialist
>>> K&M Machine-Fabricating, Inc.
>> Different puzzle. <g>
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> 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);
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Dec 21 2006 - 16:35:53 CST