Re: Quiz - The Answers

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 26 Oct 2003 10:42:14 -0800
Message-ID: <1067193754.984869@yasure>

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-10, SYSDATE-9);
    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, SYSDATE+1);
    INSERT INTO test VALUES ('D', SYSDATE+10, SYSDATE+13);

    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.

One possible solution:

    SELECT location, date_out, date_in
    FROM test
    WHERE (location, date_out) IN (

       SELECT location, MIN(date_out)
       FROM test
       GROUP BY location);

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.

One possible solution:

    CREATE OR REPLACE FUNCTION validate_pn (pn_in VARCHAR2)     RETURN BOOLEAN IS     BEGIN
       IF TRANSLATE(pn_in,'A0123456789','BAAAAAAAAAA') = '(AAA) AAA-AAAA' THEN

        RETURN TRUE;
       ELSE
        RETURN FALSE;
       END IF;

    END validate_pn;
/

And to test it a couple of anonymous blocks:

    DECLARE
       x VARCHAR2(20) := '(206) 555-1212';     BEGIN

       IF validate_pn(x) THEN
          dbms_output.put_line('TRUE');
       ELSE
          dbms_output.put_line('FALSE');
       END IF;

    END;
/

    DECLARE
       x VARCHAR2(20) := '(206)-555-1212';     BEGIN

       IF validate_pn(x) THEN
          dbms_output.put_line('TRUE');
       ELSE
          dbms_output.put_line('FALSE');
       END IF;

    END;
/

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 that has the second highest location number.

One possible solution

    SELECT MAX(location)
    FROM test
    WHERE location < (

       SELECT MAX(location)
       FROM test);

I hope everyone had fun with these.

-- 
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 Sun Oct 26 2003 - 12:42:14 CST