Re: Puzzles on SQL/Oracle

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Dec 2006 17:45:22 -0800
Message-ID: <1166665522.280303.128060@80g2000cwy.googlegroups.com>

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

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. Received on Wed Dec 20 2006 - 19:45:22 CST