Module 9

Alter the new student table to change the registration date column from the DATE data type to the VARCHAR2 data type.

A column's data type cannot be changed when there is data in the column.

10. Write a SQL statement that add a column ZIP to table STUDENT1, which has the same property as that of the ZIP column in table STUDENT.

ALTER TABLE STUDENT1 ADD ZIP varchar2(5);

Rewrite and execute the following CREATE TABLE statement to give the primary key and the foreign key constraint names. CREATE TABLE former_student (studid NUMBER(8) PRIMARY KEY, first_nm VARCHAR2(25), last_nm VARCHAR(25), enrolled VARCHAR2(1) DEFAULT 'N', zip VARCHAR2(5) REFERENCES zipcode(zip))

CREATE TABLE former_student (studid NUMBER(8), first_nm VARCHAR2(25), last_nm VARCHAR2(25), enrolled VARCHAR2(1) DEFAULT 'N', zip VARCHAR2(5) CONSTRAINT former_student_pk PRIMARY KEY(studid), CONSTRAINT former_student_zipcode_fk FOREIGN KEY(zip) REFERENCES zipcode(zip))

Write and execute a table called new student that contains first name, last name, description, date of registration. Determine data type and length for each column.

CREATE TABLE new_student (first_name VARCHAR2(25), last_name VARCHAR2(25), description VARCHAR2(50), registration_date DATE)

8. Write a SQL statement that creates a table STUDENT1, which contains columns STUDENT ID, FIRST NAME, LAST NAME, and PHONE from table STUDENT.

CREATE TABLE student1 (studid NUMBER(8), first_nm VARCHAR(25), last_nm VARCHAR(25), phone NUMBER(8)) FROM STUDENT

Add to the new_student table column Study_Duration of data type INTERVAL YEAR TO MONTH and column ALUMNI JOIN DATE with the data type TIMESTAMP WITH TIME ZONE and a six digit precision

The alter table adds both columns simultaneously. The six digit fractional seconds are the default for the TIMESTAMP WITH TIME ZONE data type and do not need to be specified explicitly. ALTER TABLE new_student ADD (study_duration INTERVAL YEAR TO MONTH, alumni_join_date TIMESTAMP (6) WITH TIME ZONE)

Execute the following INSTER statement to insert a row in the NEW_STUDENT table. Then alter the table to change the PHONE column from NULL to NOT NULL.

The column cannot be NOT NULL because there's already a row in the table that contains a NULL value in the column.

Explain the error(s) in the following CREATE TABLE statement and rewrite the statement correctly. CREATE TABLE student candidate (name VARCHAR2(25) address VARCHAR2(20) city VARCHAR2 zip NUMBER)

Three errors: Table name contains spaces nonexistent length of CITY varchar2 commas required to separate definitions: CREATE TABLE student_candidate (name VARCHAR2(25), address VARCHAR2(20), city VARCHAR(15), NUMBER)

10. The data type definitions NUMBER(10) and NUMBER(10,0) are equivalent. T/F

True

2. It is possible to create one table from three different tables in a single CREATE TABLE statement. T/F

True

4. Every column of a table can have one or more constraints. T/F

True

6. An ALTER TABLE command issues an implicit COMMIT, and cannot be rolled back, true or false?

True

6. On a read only table, no ddl commands except for a DROP TABLE and ALTER TABLE to READ WRITE are permitted. T/F

True

7. A foreign key must match a primary key or unique key. T/F

True

9. The STORAGE clause on a CREATE TABLE statement can specify how much space to allocate. T/F

True

1. The following ALTER TABLE statement contains an error. ALTER TABLE new_student DROP CONSTRAINT PRIMARY_KEY. T/F

True Should not contain both Constraint and Primary Key. Correct syntax is either ALTER TABLE tablename DROP CONSTRAINT followed by the constraint name or ALTER TABLE tablename DROP PRIMARY KEY

1. The primary key of the following CREATE TABLE statement is the concatenated primary key. CREATE TABLE class_roster (class_id NUMBER(3), class_name VARCHAR2(20) UNIQUE, first_class DATE NOT NULL, num_of_students NUMBER(3) CONSTRAINT class_roster_pk PRIMARY KEY (class_id, class_name))

True The primary key consists of multiple columns

6. A CREATE TABLE statement automatically commits all previously issued DML statements. T/F

True. Any DDL command, such as CREATE TABLE, ALTER TABLE, or TRUNCATE TABLE issues an implicit commit.

Rename the school SCHOOL_PROGRAM table you created in exercise c to SCHOOL_PROGRAM2. Then drop both the SCHOOL_PROGRAM and SCHOOL_PROGRAM2 table and explain your observations.

Use the RENAME and DROP TABLE commands

Drop all the tables created throughout the labs. The table names are STUDENT_CANDIDATE, NEW_STUDENT, COURSE2, EXTINCT_ANIMAL, and FORMER_STUDENT

Use the drop table command to remove the tables from the schema. DROP TABLE student_candidate DROP TABLE new_student etc.

4. What is the difference between TRUNCATE TABLE and DROP TABLE?

When you drop a table, the table and its data are removed, along with any indexes, triggers and constraints. When you truncate a table, all data from the table is removed, but the structure of the table remains intact, as do any triggers and grants.

Rewrite the solution to exercise g to add a UNIQUE constraint on the FIRST_NM and LAST_NM columns.

You add the constraint using a specific name at the end of the CREATE TABLE statement. CREATE TABLE former_student (studid NUMBER(8), first_nm VARCHAR2(25), last_nm VARCHAR2(25), enrolled VARCHAR2(1) DEFAULT 'N', zip VARCHAR(5) CONSTRAINT former_student_pk PRIMARY KEY (studid), CONSTRAINT former_student_zipcode_fk FOREIGN KEY(zip) REFERENCES zipcode(zip), CONSTRAINT former_student_uk UNIQUE(first_nm, last_nm))

2. What must be specified when creating a table?

the columns and their data types explicitly.

Execute the following SQL to create an empty table called COURSE2 and insert the two rows into COURSE2. What do you observe about the values of the COURSE_NO column in the COURSE2 table?

the primary key is not preserved

5. The maximum value for a column defined as NUMBER(4,2) is 9999, true or false?

False

7. A constraint must have a name in order for it to be disabled, true or false?

False

8. Primary key values should always be subject to frequent change. T/F

False

5. A column's data type can be changed only when the column contains no data. T/F

False A column data type can also be changed to a compatible data type such as from VARCHAR2 to CHAR

4. A constraint must have a name in order for it to be added. T/F

False The ALTER TABLE...DISABLE Primary KEY command used without the name of constraint

3. You can always add a NOT NULL constraint T/F

False You can add a NOT NULL constraint only if the referenced column contains values or the table is empty. With Oracle 11g, you can add a default value for the column in order to avoid having to update all the values

2. The ADD and MODIFY keyword can be used interchangeably in an ALTER TABLE statement. T/F

False You use the ADD keyword to add columns or constraints to a table, whereas you use Modify keyword to change characteristics of a column

11. The maximum value for a column defined as NUMBER(3,2) is 999. T/F

False.

3. The CASCADE CONSTRAINTS keywords in a DROP TABLE statement drop all referencing child tables. T/F

False. The foreign key constraints of the child tables are dropped, but the child tables themselves are not.

5. You cannot create a table from another table if it has no rows. T/F

False. You can create a table from another, regardless of whether the table has rows.

9. Write a SQL statement that add a new row to table STUDENT1, using values of your choice.

INSERT INTO student1 (pets) VALUES ('dog')

Disable the primary key constraint on the NEW STUDENT table and write an INSERT statement with the value Joe Fisher for the first and last names to prove it is successful.

Joe Fisher exists twice in the first_name and last_name columns so the primary key constraint cannot be enabled on the table.

1. What are the requirements for table names?

Must be unique within database schema; no other database object, such as another table, view or synonym. Must be no longer than 30 characters and cannot include spaces or hyphens. May contain underscores and must begin with a letter.

3. Give three examples of integrity constraints when creating a table, and briefly explain each.

Primary Key Constraint- (entity integrity constraint) Ensures all values of the table are not null and are unique. Unique Constraints- used to enforce unique values on an individual or group of columns. Allows null values. Foreign Key Constraint- (referential integrity constraint) ensures all the values in the foreign key correspond to values of the primary key.

Execute the following CREATE TABLE statement and explain the result. CREATE TABLE school_program AS SELECT last_name|| ', ' || first_name name FROM student UNION SELECT last_name || ', ' || first_name FROM instructor

SQL DESC school_program

Drop the foreign key constraint FORMER_STUDENT_ZIPCODE_FK on the FORMER_STUDENT table and then change it to an ON DELETE SET NULL foreign key constraint. Test the behavior by inserting a new zip code in the ZIPCODE table and creating a new student row with this new zip code and deleting the same zip code from the ZIPCODE table. QUERY the FORMER_STUDENT table to see effect.

ALTER TABLE former_student DROP CONSTRAINT former_student_zipcode_fk ALTER TABLE former_student ADD CONSTRAINT former_student_zipcode_fk FOREIGN KEY(zip) REFERENCES zipcode(ZIP) ON DELETE SET NULL INSERT INTO zipcode (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES ('90210', 'Hollywood', 'CA', 'Alice', sysdate, 'Alice', sysdate); INSER INTO former_student (studid, first_nm, last_nm, enrolled, zip) VALUES (109, 'Alice', 'Rischert', 3, '90210') DELETE FROM zipcode WHERE zip = '90210' SELECT studid, zip FROM former_student WHERE studid=109

Alter the table from 12.1 by adding four columns. The columns should be called PHONE, NUM_COURSES with data type and length NUMBER(3), CREATED_BY and CREATED_DARE.

ALTER TABLE new_student ADD (phone VARCHAR2(15), num_courses NUMBER(3), created_by VARCHAR2(30) DEFAULT USER, created_date DATE DEFAULT SYSDATE NOT NULL)