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
   

Select Another Chapter

1. SQL and Data
2. SQL: The Basics
3. Character and Number Functions
4. Date, Conversion, and Miscellaneous Functions
5. Aggregate Functions, GROUP BY, and HAVING
6. Equijoins
7. Subqueries
8. Set Operators
9. Complex Joins
10. Insert, Update, and Delete
11. Create, Alter, and Drop Tables
12. Views, Indexes, and Sequences
13. The Data Dictionary and Dynamic SQL Scripts
14. Security
15. Advanced SQL Queries
16. SQL Optimization