Re: Quiz

Comments below.
On Sat, 25 Oct 2003 05:20:33 GMT, "nobody" <nobody_at_nowhere.com> wrote:

>must be bored or unemployed
>
>1)
> select *
> from test a
> where date_out in (select min(date_out)
> from test where location=a.location)
>
>

>ok I cheated and should spell out the elements instead of *
>
>2) Unless there is some new function in 9i that I am unaware of. I dont see
>how to do this without using a function that returns the locations of a
>character within a string to ensure
>that the brackets and space are in the correct place . without using
>instring or substring.
>

Hmm...but you could use SUBSTR and INSTR...INSTRING and SUBSTRING are not even present in Oracle...a trick question, I guess....

But even without SUBSTR and INSTR, you can do it :

The number must be of length 14. It must start with a '(', the fifth character must be ')',
the 7th character must be a '-', the 6th and 8th must be spaces. The rest are digits So :

create or replace function check_number(number in varchar2) is
begin
  return (   

  number like '(___) ___-____'
  and ltrim(translate(number,'()- 0123456789',' ')) is null;

         );
end check_number;

>The rest of the string can check for numerics or change or change the space
>to a + and abs(the string)
>if this doesnt fail the string is valid
>
>4)
>select loc_name
>from test
>where location =
>(select max(location) from test
>where location != (select max(location) from test))
>
>
>P.S. What happened to 3.
>
>"Daniel Morgan" <damorgan_at_x.washingt
>
>on.edu> wrote in message news:1067035247.574203_at_yasure...
>> I answered a post earlier this week and made a statement with respect to
>> the fact that
>> Americans no longer seem to value education. Some of my students
>> suggested I post
>> a couple of questions from the quiz they took at the beginnng of class
>> Thursday so
>> that others could get a sense of how they are doing too. Senior
>> developers should
>> find these easy as this was just the third classof the quarter. But for
>> others ... enjoy.
>>
>> 1. Assuming the following table and data ...
>>
>> CREATE TABLE test (
>> location VARCHAR2(5),
>> date_out DATE,
>> date_in DATE);
>>
>> INSERT INTO test VALUES ('A', SYSDATE-3, SYSDATE+2);
>> INSERT INTO test VALUES ('B', SYSDATE-6, SYSDATE-4);
>> INSERT INTO test VALUES ('C', SYSDATE-8, SYSDATE-3);
>> INSERT INTO test VALUES ('D', SYSDATE-1, SYSDATE-9);
>> INSERT INTO test VALUES ('E', SYSDATE-10, SYSDATE-9);
>> INSERT INTO test VALUES ('A', SYSDATE-2, SYSDATE);
>> INSERT INTO test VALUES ('D', SYSDATE+1, SYSDATE+3);
>> COMMIT;
>>
>> Write a single SQL statement that will return a single record for
>> each location
>> with the minimum date_out and the corresponding date_in.
>> ---------------------------------------------------------------------
>> 2. Write an Oracle function that will accept as a parameter a phone
>> number
>> formatted like this example: (206) 555-1212 and return True if it
>> is valid
>> an False if it is not.
>>
>> Do not use the SUBSTRING or INSTRING functions.
>> ---------------------------------------------------------------------
>> 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.
>> ---------------------------------------------------------------------
>>
>> --
>> 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 Sat Oct 25 2003 - 09:06:51 CDT