Puzzles on SQL/Oracle

On Dec 18, 1:52 pm, DA Morgan wrote:
> Bruman wrote:
> > nirav wrote:
> >> Hello ,
>
> >> In my company , I started a contest on Oracle..we would send two
> >> questions on SQL, Pl-SQL and the fastest response with all correct
> >> answer is the winner...this is getting some response and I am wondering
> >> about where I could refer for material which would be good for the
> >> contest...basically queries that are not too easy nor painstakingly
> >> difficult..something that appeals the java programmer or the dot net
> >> programmer and even tempts a QA guy to take a look and try to solve...
>
> >> I think I have enough to keep going for next seven rounds or so but I
> >> am exploring for getting better ideas...any pointers to such puzzles or
> >> similar links etc I shall be thankful ..(I know of Steven Feuristein's
> >> puzzles on toadsoft or some other site)
>
> >> Thanks
> >> Nirav
>
> > As far as SQL questions go, be sure to do some on Analytic Functions.
> > They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview
> question too.
>
> 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...@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 b 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 );

On Dec 18, 1:52 pm, DA Morgan wrote:
> Bruman wrote:
> > nirav wrote:
> >> Hello ,
>
> >> In my company , I started a contest on Oracle..we would send two
> >> questions on SQL, Pl-SQL and the fastest response with all correct
> >> answer is the winner...this is getting some response and I am wondering
> >> about where I could refer for material which would be good for the
> >> contest...basically queries that are not too easy nor painstakingly
> >> difficult..something that appeals the java programmer or the dot net
> >> programmer and even tempts a QA guy to take a look and try to solve...
>
> >> I think I have enough to keep going for next seven rounds or so but I
> >> am exploring for getting better ideas...any pointers to such puzzles or
> >> similar links etc I shall be thankful ..(I know of Steven Feuristein's
> >> puzzles on toadsoft or some other site)
>
> >> Thanks
> >> Nirav
>
> > As far as SQL questions go, be sure to do some on Analytic Functions.
> > They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview
> question too.
>
> 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...@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 b 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 );

EscVector wrote:
>
> On Dec 18, 1:52 pm, DA Morgan wrote:
>> Bruman wrote:
>>> nirav wrote:
>>>> Hello ,
>>>> In my company , I started a contest on Oracle..we would send two
>>>> questions on SQL, Pl-SQL and the fastest response with all correct
>>>> answer is the winner...this is getting some response and I am wondering
>>>> about where I could refer for material which would be good for the
>>>> contest...basically queries that are not too easy nor painstakingly
>>>> difficult..something that appeals the java programmer or the dot net
>>>> programmer and even tempts a QA guy to take a look and try to solve...
>>>> I think I have enough to keep going for next seven rounds or so but I
>>>> am exploring for getting better ideas...any pointers to such puzzles or
>>>> similar links etc I shall be thankful ..(I know of Steven Feuristein's
>>>> puzzles on toadsoft or some other site)
>>>> Thanks
>>>> Nirav
>>> As far as SQL questions go, be sure to do some on Analytic Functions.
>>> They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview
>> question too.
>>
>> 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...@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?
--
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

EscVector wrote:
>
> On Dec 18, 1:52 pm, DA Morgan wrote:
>> Bruman wrote:
>>> nirav wrote:
>>>> Hello ,
>>>> In my company , I started a contest on Oracle..we would send two
>>>> questions on SQL, Pl-SQL and the fastest response with all correct
>>>> answer is the winner...this is getting some response and I am wondering
>>>> about where I could refer for material which would be good for the
>>>> contest...basically queries that are not too easy nor painstakingly
>>>> difficult..something that appeals the java programmer or the dot net
>>>> programmer and even tempts a QA guy to take a look and try to solve...
>>>> I think I have enough to keep going for next seven rounds or so but I
>>>> am exploring for getting better ideas...any pointers to such puzzles or
>>>> similar links etc I shall be thankful ..(I know of Steven Feuristein's
>>>> puzzles on toadsoft or some other site)
>>>> Thanks
>>>> Nirav
>>> As far as SQL questions go, be sure to do some on Analytic Functions.
>>> They are highly valuable but in my experience not frequently used.Here's one of my personal favorites and generally a good interview
>> question too.
>>
>> 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...@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?
--
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

DA Morgan wrote:
> EscVector wrote:
> > On Dec 18, 1:52 pm, DA Morgan 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...@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?
> --
> 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.

DA Morgan wrote:
> EscVector wrote:
> > On Dec 18, 1:52 pm, DA Morgan 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...@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?
> --
> 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.

Charles Hooper wrote:
> DA Morgan wrote:
> > EscVector wrote:
> > > On Dec 18, 1:52 pm, DA Morgan 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...@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?
> > --
> > 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.STR ING_TO_RAW
(NVL(A.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(A.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<>
UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STR ING_TO_RAW
(NVL(B.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(B.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_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.

Charles Hooper wrote:
> DA Morgan wrote:
> > EscVector wrote:
> > > On Dec 18, 1:52 pm, DA Morgan 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...@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?
> > --
> > 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.STR ING_TO_RAW
(NVL(A.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(A.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(A.COL3,'1'), 'AL32UTF8'),1),'AL32UTF8')<>
UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.HASH(UTL_I18N.STR ING_TO_RAW
(NVL(B.COL1,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_RAW
(NVL(B.COL2,'1'),
'AL32UTF8'),1)||DBMS_CRYPTO.HASH(UTL_I18N.STRING_T O_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.

Charles Hooper wrote:
> DA Morgan wrote:
>> EscVector wrote:
>>> On Dec 18, 1:52 pm, DA Morgan 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...@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?
>> --
>> 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

Charles Hooper wrote:
> DA Morgan wrote:
>> EscVector wrote:
>>> On Dec 18, 1:52 pm, DA Morgan 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...@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?
>> --
>> 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