Quiz

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 24 Oct 2003 15:40:29 -0700
Message-ID: <1067035247.574203@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 Fri Oct 24 2003 - 17:40:29 CDT