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.
Solution:
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'))
)
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.
Solution:
INSERT INTO temp_student
(studid, first_name, last_name,
zip, registration_date)
VALUES
(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.
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.
Solution:
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