Solutions Chapter 11: Create, Alter, and Drop Tables Test Your Thinking

  1. Create a table called TEMP_STUDENT with the following columns and constraints: a column STUDID for student ID that is NOT NULL and is the primary key, a column FIRST_NAME for student first name; a column LAST_NAME for student last name, a column ZIP that is a foreign key to the ZIP column in the ZIPCODE table, a column REGISTRATION_DATE that is NOT NULL and has a CHECK constraint to restrict the registration date to dates after January 1st, 2000.


    CREATE TABLE temp_student
      (studid             NUMBER(8) NOT NULL,
       first_name         VARCHAR2(25),
       last_name          VARCHAR2(25),
       zip                VARCHAR2(5),
       registration_date  DATE NOT NULL,
       CONSTRAINT temp_student_pk PRIMARY KEY(studid),
       CONSTRAINT temp_student_fk_zip FOREIGN KEY(zip)
         REFERENCES zipcode(zip),
       CONSTRAINT temp_student_chk_regdate
         CHECK(registration_date > 
               TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))
  2. Write an INSERT statement violating at least two of the constraints for the TEMP_STUDENT table you just created. Write another INSERT statement that succeeds when executed, and commit your work.


    INSERT INTO temp_student
      (studid, first_name, last_name,
       zip, registration_date)
      (NULL, 'Alex', 'Morrison', '99999', TO_DATE('01-DEC-1999', 'DD-MON-YYYY'))
    INSERT INTO temp_student
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("STUDENT"."TEMP_STUDENT"."STUDID")
    INSERT INTO temp_student
    VALUES (101, 'Alex', 'Morrison', '07656', TO_DATE('01-DEC-2000', 'DD-MON-YYYY'))
    1 row created.
  3. Alter the TEMP_STUDENT table to add two more columns called EMPLOYER and EMPLOYER_ZIP. The EMPLOYER_ZIP column should have a foreign key constraint referencing the ZIP column of the ZIPCODE table. Update the EMPLOYER column, and alter the table once again to make the EMPLOYER column NOT NULL. Drop the TEMP_STUDENT table once you're done with the exercise.


    ALTER TABLE temp_student
      ADD (employer      VARCHAR2(20),
           employer_zip  VARCHAR2(5),
           CONSTRAINT temp_student_fk 
             FOREIGN KEY(employer_zip)
             REFERENCES zipcode(zip))
    UPDATE temp_student
       SET employer = 'ANM Productions'
    ALTER TABLE temp_student
      MODIFY (employer NOT NULL)
    DROP TABLE temp_student

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