Re: Puzzles on SQL/Oracle

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Dec 2006 18:33:39 -0800
Message-ID: <1166668419.761522.280170@80g2000cwy.googlegroups.com>

Charles Hooper wrote:
> 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.

I hate the idea of failing an exam:
CREATE TABLE TABLE_A (

  COL1 VARCHAR2(20),
  COL2 VARCHAR2(20),
  COL3 VARCHAR2(20));

CREATE TABLE TABLE_B (

  COL1 VARCHAR2(20),
  COL2 VARCHAR2(20),
  COL3 VARCHAR2(20));

INSERT INTO TABLE_A VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_A VALUES ('TEST2A','TEST2B','TEST2C');
INSERT INTO TABLE_A VALUES ('TEST3A','TEST3B','TEST3C');

INSERT INTO TABLE_A VALUES ('TEST4A','TEST4B','TEST4C'); INSERT INTO TABLE_A VALUES ('TEST5A','TEST5B','TEST5C');

INSERT INTO TABLE_B VALUES ('TEST1A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST2A','TEST1B','TEST1C');
INSERT INTO TABLE_B VALUES ('TEST3A','TEST3B','TEST3C');
INSERT INTO TABLE_B VALUES ('TEST5A','TEST5B','TEST5C');

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
  (A.COL1 IS NULL OR B.COL1 IS NULL)
  OR (A.COL2 IS NULL OR B.COL2 IS NULL)
  OR (A.COL3 IS NULL OR B.COL3 IS NULL); COL1 COL2 COL3 FROM_TABLE
TEST2A TEST2B TEST2C TABLE A
TEST4A TEST4B TEST4C TABLE A
TEST2A TEST1B TEST1C TABLE B Extra credit:
SELECT DISTINCT

  NVL(A.COL1,B.COL1) COL1,
  NVL(A.COL2,B.COL2) COL2,
  NVL(A.COL3,B.COL3) COL3,

(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. Received on Wed Dec 20 2006 - 20:33:39 CST