| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Puzzles on SQL/Oracle
DA Morgan wrote:
> 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 actually did like the question. It seems easy now, but under the weight of the interview, it was rather difficult. I got it in 3 tries with 7 balls or something, the question wasn't exactly the same, but the point they were after was more personality and grace under pressure. I started out by stating Newtons laws and process of elimination stuff. This question was after 3hrs of interview and was thrown at me by an senior company exec.
One interview question I like is "what tech books do you like?" This question usually doesn't go over well. Received on Fri Dec 22 2006 - 12:48:55 CST
 |
 |