Re: Puzzles on SQL/Oracle

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Dec 2006 19:45:16 -0800
Message-ID: <1166672716.688494.265470@i12g2000cwa.googlegroups.com>

DA Morgan wrote:
> 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.

> The intention was a single SQL statement ... I should have written it
> more clearly and for that I apologize.

> The point though, much like with new math, is not just to get the right
> answer but to observe how someone approaches the problem.
> --
> 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

Sorry, I had bad flashbacks of a couple test questions from years ago.

Please note that the DISTINCT in the previous solution is not required. I mistakenly inserted the rows twice into TABLE_A, and did not notice that mistake until the solution was posted.

Here are a couple more solutions, using more than one SELECT in a SQL statement:
SELECT

  A.COL1,
  A.COL2,
  A.COL3,

  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  (SELECT

    A.COL1,
    A.COL2,
    A.COL3

  FROM
    TABLE_A A
  INTERSECT
  SELECT

    B.COL1,
    B.COL2,
    B.COL3

  FROM
    TABLE_B B) M
WHERE
  A.COL1=M.COL1(+)

  AND A.COL2=M.COL2(+)
  AND A.COL3=M.COL3(+)
  AND M.COL1 IS NULL

UNION ALL
SELECT

  B.COL1,
  B.COL2,
  B.COL3,

  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  (SELECT

    A.COL1,
    A.COL2,
    A.COL3

  FROM
    TABLE_A A
  INTERSECT
  SELECT

    B.COL1,
    B.COL2,
    B.COL3

  FROM
    TABLE_B B) M
WHERE
  B.COL1=M.COL1(+)

  AND B.COL2=M.COL2(+)
  AND B.COL3=M.COL3(+)
  AND M.COL1 IS NULL;

WITH M AS (
SELECT

  A.COL1,
  A.COL2,
  A.COL3

FROM
TABLE_A A
INTERSECT
SELECT

  B.COL1,
  B.COL2,
  B.COL3

FROM
TABLE_B B)
SELECT

  A.COL1,
  A.COL2,
  A.COL3,

  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  M
WHERE
  A.COL1=M.COL1(+)

  AND A.COL2=M.COL2(+)
  AND A.COL3=M.COL3(+)
  AND M.COL1 IS NULL

UNION ALL
SELECT

  B.COL1,
  B.COL2,
  B.COL3,

  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  M
WHERE
  B.COL1=M.COL1(+)

  AND B.COL2=M.COL2(+)
  AND B.COL3=M.COL3(+)
  AND M.COL1 IS NULL;

SELECT

  A.COL1,
  A.COL2,
  A.COL3,

  'TABLE A' FROM_TABLE
FROM
  TABLE_A A
WHERE
  (A.COL1,A.COL2,A.COL3) NOT IN (
    SELECT DISTINCT

      B.COL1,
      B.COL2,
      B.COL3
    FROM
      TABLE_B B)

UNION ALL
SELECT

  B.COL1,
  B.COL2,
  B.COL3,

  'TABLE B' FROM_TABLE
FROM
  TABLE_B B
WHERE
  (B.COL1,B.COL2,B.COL3) NOT IN (
    SELECT DISTINCT

      A.COL1,
      A.COL2,
      A.COL3
    FROM
      TABLE_A A);

SELECT

  A.COL1,
  A.COL2,
  A.COL3,

  'TABLE A' FROM_TABLE
FROM
  TABLE_A A,
  (SELECT DISTINCT

    B.COL1,
    B.COL2,
    B.COL3

  FROM
    TABLE_B B) B
WHERE
  A.COL1=B.COL1(+)

  AND A.COL2=B.COL2(+)
  AND A.COL3=B.COL3(+)
  AND B.COL3 IS NULL

UNION ALL
SELECT

  B.COL1,
  B.COL2,
  B.COL3,

  'TABLE B' FROM_TABLE
FROM
  TABLE_B B,
  (SELECT DISTINCT

    A.COL1,
    A.COL2,
    A.COL3

  FROM
    TABLE_A A) A
WHERE
  B.COL1=A.COL1(+)

  AND B.COL2=A.COL2(+)
  AND B.COL3=A.COL3(+)
  AND A.COL3 IS NULL;

SELECT

  COL1,
  COL2,
  COL3,

FROM_TABLE
FROM
(SELECT

  COL1,
  COL2,
  COL3,

FROM_TABLE,
COUNT(DISTINCT FROM_TABLE) OVER (PARTITION BY COL1,COL2,COL3) NUM_TABLES
FROM
(SELECT

  A.COL1,
  A.COL2,
  A.COL3,

'TABLE A' FROM_TABLE
FROM
TABLE_A A
UNION ALL
SELECT

  B.COL1,
  B.COL2,
  B.COL3,

  'TABLE B' FROM_TABLE
FROM
  TABLE_B B))
WHERE
  NUM_TABLES=1; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc Received on Wed Dec 20 2006 - 21:45:16 CST