Re: Puzzles on SQL/Oracle

From: EscVector <Junk_at_webthere.com>
Date: 22 Dec 2006 10:25:42 -0800
Message-ID: <1166811942.059178.208940@i12g2000cwa.googlegroups.com>

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 Received on Fri Dec 22 2006 - 12:25:42 CST