Skip over navigation to the main content.

Oracle SQL Interactive Workbook, 2nd Edition

Solutions Chapter 10: Insert, Update, and Delete Test Your Thinking

  1. Write and execute two INSERT statements to insert rows into the ZIPCODE table for the following two cities: Newton, MA 02199; Cleveland, OH 43011. After your INSERT statements are successful, make the changes permanent.

    Solution:

    INSERT INTO zipcode
  (city, state, zip, created_date, created_by,
   modified_date, modified_by)
VALUES
  ('Newton', 'MA', '02199', SYSDATE, USER,
   SYSDATE, USER)

INSERT INTO zipcode
  (city, state, zip, created_date, created_by,
   modified_date, modified_by)
VALUES
  ('Cleveland', 'OH', '43011', SYSDATE, USER,
   SYSDATE, USER)
COMMIT

  2. Make yourself a student by writing and executing an INSERT statement to insert a row into the STUDENT table with data about you. Use one of the zip codes you inserted in Exercise 1. Only insert values into the columns STUDENT_ID (use a value of '900'), FIRST_NAME, LAST_NAME, ZIP, REGISTRATION_DATE (use a date that is five days after today), CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE. Issue a COMMIT command afterwards.

    Solution:

    INSERT INTO student
  (student_id, first_name, last_name,
   zip, registration_date,
   created_by, created_date, modified_by, modified_date)
VALUES
  (900, 'Sandy', 'Dellacorte',
   '02199', SYSDATE + 5,
   USER, SYSDATE, USER, SYSDATE)
COMMIT

  3. Write an UPDATE statement to update the data about you in the STUDENT table. Update the columns SALUTATION, STREET_ADDRESS, PHONE, and EMPLOYER. Be sure to also update the MODIFIED_DATE column and make the changes permanent.

    Solution:

    UPDATE student
   SET salutation = 'Ms.',
       street_address = '60 Winter St.',
       phone = '617-236-2746',
       employer = 'Raytone',
       modified_by = USER,
       modified_date = SYSDATE
 WHERE student_id = 900
COMMIT

  4. Delete the row in the STUDENT table and the two rows in the ZIPCODE table you created. Be sure to issue a COMMIT command afterwards.

    Solution:

    DELETE FROM student
 WHERE student_id = 900

DELETE FROM zipcode
 WHERE zip IN ('02199', '43011')

COMMIT

Further Reading

Read Prentice Hall PTR to stay on the cutting edge of computer science and engineering.