Charles Hooper 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.
>
> 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,
> 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
> UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW
> (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.
Different puzzle. <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
Received on Thu Dec 21 2006 - 13:55:51 CST
