Re: Puzzles on SQL/Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 20 Dec 2006 18:35:16 -0800
Message-ID: <1166668516.75289@bubbleator.drizzle.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.

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

Received on Wed Dec 20 2006 - 20:35:16 CST