Re: Puzzles on SQL/Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 18 Dec 2006 10:52:27 -0800
Message-ID: <1166467947.716537@bubbleator.drizzle.com>

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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Dec 18 2006 - 12:52:27 CST