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
>
> During a DBA interview, I was once asked a to "think out loud" and had
> to answer a question similar to this:
> "1. You have 8 marbles that weigh 1 ounce each, & 1 marble that weighs
> 1.5 ounces. You are unable to determine which is the heavier marble by
> looking at them. You have a weighing scale that consists of 2 pans, but
> the scale is only good for 2 total weighings. How can you determine
> which marble is the heaviest 1 using the scale, & in 2 weighings?"
> Question and answer from http://www.puzz.com/classicans.html
I've seen this type of question before and I don't like them. Those that
succeed are almost always those that, not from intellectual brilliance
puzzle it out, but rather those that have heard it before and remember
the answer. I prefer questions that no-one has ever heard before and
that have no single solution.
--
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 Fri Dec 22 2006 - 12:36:01 CST
