Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Quiz

Re: Quiz

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 28 Oct 2003 13:55:57 -0800
Message-ID: <1067378174.678766@yasure>


Jonathan Lewis wrote:

>select 'Colorado' from dual;
>
>seems to fit the terms of your question 4
>perfectly - but I'm sure that wasn't what
>you intended.
>
>In passing, the solution you posted changed
>the question from 'location name' to 'location'
>and returned the numeric.
>
>Were you expecting vanilla SQL, or would
>an Oracle analytic solution be accepted for
>the more serious solution ? After all, it does
>do a single pass across the data, and is
>often a less I/O and CPU intensive operation.
>
>Off the top of my head, the following looks
>about right:
>
>select v.loc_name
>from (
> select
> rank() over(order by location desc) rnk,
> loc_name
> from
> test
> ) v
>where rnk = 2
>;
>
>
>Of course, that leaves room for agument about
>what exactly you intended to happen for ties.
>
>--
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
>One-day tutorials:
>http://www.jlcomp.demon.co.uk/tutorial.html
>____Belgium__November (EOUG event - "Troubleshooting")
>____UK_______December (UKOUG conference - "CBO")
>
>
>Three-day seminar:
>see http://www.jlcomp.demon.co.uk/seminar.html
>____UK___November
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>news:1067035247.574203_at_yasure...
>
>
>>4. Assuming the following table and data ...
>>
>> CREATE TABLE test (
>> location NUMBER(2),
>> loc_name VARCHAR2(20));
>>
>> INSERT INTO test VALUES (1, 'Washington');
>> INSERT INTO test VALUES (2, 'Oregon');
>> INSERT INTO test VALUES (3, 'California');
>> INSERT INTO test VALUES (4, 'Idaho');
>> INSERT INTO test VALUES (5, 'Colorado');
>> INSERT INTO test VALUES (6, 'Vermont');
>> COMMIT;
>>
>> Write a single SQL statement that will return the location name
>> that has the second highest location number.
>>--------------------------------------------------------------------
>>
>>
>-
>
>

I accept all answers that are general enough to return the correct answer. Especially when the
quiz is given to students at their fourth class. They are very aggressive and motivated learners
but not so much as to have discovered your solution yet.

I'll post it to the class web site for those not lurking here.

Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue Oct 28 2003 - 15:55:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US