Re: Puzzles on SQL/Oracle

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 ); Received on Wed Dec 20 2006 - 15:54:11 CST