Database Programming Section 14 Quiz
Section 14 Quiz
(Answer all questions in this section)
1. Which statement should you use to add a FOREIGN KEY
constraint to the DEPARTMENT_ID column in the EMPLOYEES table
to refer to the DEPARTMENT_ID column in the DEPARTMENTS
table? Mark for Review
(1) Points
ALTER TABLE employees
ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id);
(*)
ALTER TABLE employees
ADD FOREIGN KEY CONSTRAINT dept_id_fk ON (department_id)
REFERENCES departments(department_id);
ALTER TABLE employees
ADD FOREIGN KEY departments(department_id) REFERENCES
(department_id);
ALTER TABLE employees
MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id);
2. What is the syntax for removing a PRIMARY KEY constraint and
all its dependent constraints? Mark for Review
(1) Points
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT table_name (constraint_name);
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
3. Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
Mark for Review
(1) Points
To disable an existing constraint on the EMPLOYEES table
To activate a new constraint while preventing the creation of a
PRIMARY KEY index
To add a new constraint to the EMPLOYEES table
To activate the previously disabled constraint on the EMPLOYEE_ID
column while creating a PRIMARY KEY index (*)
4. Which of the following would definitely cause an integrity
constraint error? Mark for Review
(1) Points
Using the MERGE statement to conditionally insert or update rows.
Using a subquery in an INSERT statement.
Using the UPDATE command on rows based in another table.
Using the DELETE command on a row that contains a primary key
with a dependent foreign key declared without either an ON DELETE
CASCADE or ON DELETE SET NULL. (*)
5. You want to disable the FOREIGN KEY constraint that is defined
in the EMPLOYEES table on the DEPARTMENT_ID column. The
constraint is referenced by the name FK_DEPT_ID_01. Which
statement should you issue? Mark for Review
(1) Points
ALTER TABLE employees
DISABLE fk_dept_id_01;
ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';
ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)
ALTER TABLE employees
DISABLE 'fk_dept_id_01';
(Answer all questions in this section)
6. Which type of constraint by default requires that a column be both
unique and not null? Mark for Review
(1) Points
CHECK
UNIQUE
FOREIGN KEY
PRIMARY KEY (*)
7. Which statement about a non-mandatory foreign key constraint is
true? Mark for Review
(1) Points
A foreign key value must either be null or match an existing value in
the parent table. (*)
A foreign key value must be unique.
A foreign key value cannot be null.
A foreign key value must match an existing value in the parent table.
8. The employees table contains a foreign key column
department_id that references the id column in the departments table.
Which of the following constraint modifiers will NOT allow the deletion
of id values in the department table? Mark for Review
(1) Points
ON DELETE CASCADE
ON DELETE SET NULL
Neither A nor B (*)
Both A and B
9. Evaluate the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER NOT NULL, Primary Key
DONOR_ID NUMBER Foreign key to DONOR_ID column of
DONORS table
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE
Which CREATE TABLE statement should you use to create the
DONATIONS table?
Mark for Review
(1) Points
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY NOT NULL,
donor_id NUMBER FOREIGN KEY donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
CREATE TABLE donations
pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES
donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE;
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES
donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
(*)
10. If a Primary Key is made up of more than one column, one of the
columns can be null. True or False? Mark for Review
(1) Points
True
False (*)
(Answer all questions in this section)
11. You need to ensure that the LAST_NAME column does not
contain null values. Which type of constraint should you define on the
LAST_NAME column? Mark for Review
(1) Points
CHECK
PRIMARY KEY
NOT NULL (*)
UNIQUE
12. You need to ensure that the LAST_NAME column only contains
certain character values. No numbers or special characters are
allowed.
Which type of constraint should you define on the LAST_NAME
column? Mark for Review
(1) Points
UNIQUE
PRIMARY KEY
NOT NULL
CHECK (*)
13. Which of the following is not a valid Oracle constraint type?
Mark for Review
(1) Points
NOT NULL
EXTERNAL KEY (*)
PRIMARY KEY
UNIQUE KEY
14. What is the highest number of NOT NULL constraints you can
have on a table? Mark for Review
(1) Points
10
You can have as many NOT NULL constraints as you have columns
in your table. (*)
15. Which statement about the NOT NULL constraint is true? Mark
for Review
(1) Points
The NOT NULL constraint can be defined at either the column level
or the table level.
The NOT NULL constraint must be defined at the column level. (*)
The NOT NULL constraint requires a column to contain alphanumeric
values.
The NOT NULL constraint prevents a column from containing
alphanumeric values.
