Re: Puzzles on SQL/Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 20 Dec 2006 14:12:28 -0800
Message-ID: <1166652748.452166@bubbleator.drizzle.com>

EscVector wrote:
>
> On Dec 18, 1:52 pm, DA Morgan <damor..._at_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..._at_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>

-- 
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 Wed Dec 20 2006 - 16:12:28 CST