Re: Quiz

Oops... The function below does in fact accept '(((() 999-9999' as a phone number. We thus also have to check that the number of '(',')','-' and ' ' is exactly one respectively :

create or replace function check_number(number in varchar2) return boolean
is
begin
  return (
   number like '(___) ___-____'
  and ltrim(translate(number,'()- 0123456789',' ')) is null   and length(translate(number,'z() -','z')) = 10 ); end check_number;

• Kenneth Koenraadt

On Sat, 25 Oct 2003 14:06:51 GMT, Kenneth Koenraadt wrote:

>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:42:14 CDT