Re: Puzzles on SQL/Oracle

From: EscVector <Junk_at_webthere.com>
Date: 20 Dec 2006 19:57:42 -0800
Message-ID: <1166673462.687427.193640@73g2000cwn.googlegroups.com>

EscVector wrote:
> 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

>

> Not that I could do better, but I put this out there on purpose. I
> figured you meant use only one Select in the SQL statement, but I
> posted the first thing that came to mind. The rational is that it is a
> "single" non-procedural SQL statement. :) I used multiple selects in
> one statement. You were asking for a single select in one statement.
> I'd like to add a bonus question: Which is the optimal statement?

>

> "Get it done or Make it Perform". The typical approach is as I have
> posted; get it done. Performance always comes second. Prereq: clarify
> the work before investing effort. Since I did the fastest thing I
> could with almost zero effort, you were able to clarify the work for me
> while I ate dinner :) Is this what they call "agile"?

Here's the plan for my first solution.

---

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    40 |  1280 |    12  (84)|
00:00:01 |
|   1 |  SORT UNIQUE          |      |    40 |  1280 |    12  (84)|
00:00:01 |
|   2 |   UNION-ALL           |      |       |       |            |
     |
|   3 |    MINUS              |      |       |       |            |
     |
|   4 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|
00:00:01 |
|   5 |      TABLE ACCESS FULL| A    |    20 |   320 |     2   (0)|
00:00:01 |
|   6 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|
00:00:01 |
|   7 |      TABLE ACCESS FULL| B    |    20 |   320 |     2   (0)|
00:00:01 |
|   8 |    MINUS              |      |       |       |            |
     |
|   9 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|
00:00:01 |
|  10 |      TABLE ACCESS FULL| B    |    20 |   320 |     2   (0)|
00:00:01 |
|  11 |     SORT UNIQUE       |      |    20 |   320 |     3  (34)|
00:00:01 |
|  12 |      TABLE ACCESS FULL| A    |    20 |   320 |     2   (0)|

00:00:01 |

---

Received on Wed Dec 20 2006 - 21:57:42 CST