Database Programming with SQL-Section 14 Quiz

Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. The PO_DETAILS table contains these columns:
PO_NUM NUMBER NOT NULL, Primary Key
PO_LINE_ID NUMBER NOT NULL, Primary Key
PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table
QUANTITY NUMBER
UNIT_PRICE NUMBER(5,2)

Evaluate this statement:

ALTER TABLE po_details
DISABLE CONSTRAINT product_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points


To drop and recreate the PRIMARY KEY constraint on the PO_NUM column


To create a new PRIMARY KEY constraint on the PO_NUM column


To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index


To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


2. 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 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
ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);
(*)



ALTER TABLE employees
MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);



Incorrect Incorrect. Refer to Section 14 Lesson 3.


3. Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points


To remove all constraint references to the PRODUCTS table


To remove all constraint references to SUPPLIERS table


To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)


To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table


To drop the FOREIGN KEY constraint on the PRODUCTS table



Incorrect Incorrect. Refer to Section 14 Lesson 3.


4. You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table? Mark for Review
(1) Points


SYS_DATA_DICT_COLUMNS


CONSTRAINTS_ALL_COLUMNS


US_CON_SYS


USER_CONS_COLUMNS (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


5. 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 activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)


To add a new constraint to the EMPLOYEES table


To disable an existing constraint on the EMPLOYEES table


To activate a new constraint while preventing the creation of a PRIMARY KEY index



Incorrect Incorrect. Refer to Section 14 Lesson 3.


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. A table can have more than one UNIQUE key constraint. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


7. Evaluate this CREATE TABLE statement:
CREATE TABLE customers
    (customer_id NUMBER,
     customer_name VARCHAR2(25),
     address VARCHAR2(25),
     city VARCHAR2(25),
     region VARCHAR2(25),
     postal_code VARCHAR2(11),
     CONSTRAINT customer_id_un UNIQUE(customer_id),
     CONSTRAINT customer_name_nn NOT NULL(customer_name));

Why does this statement fail when executed?

 Mark for Review
(1) Points


UNIQUE constraints must be defined at the column level.


The NUMBER data types require precision values.


NOT NULL constraints CANNOT be defined at the table level. (*)


The CREATE TABLE statement does NOT define a PRIMARY KEY.



Incorrect Incorrect. Refer to Section 14 Lesson 1.


8. You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task? Mark for Review
(1) Points


ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)



ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);


ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);


ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);



Correct Correct


9. Which of the following is not a valid Oracle constraint type? Mark for Review
(1) Points


PRIMARY KEY


EXTERNAL KEY (*)


NOT NULL


UNIQUE KEY



Incorrect Incorrect. Refer to Section 14 Lesson 1.


10. Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


Column (*)


Row


Null Field


Dictionary


Table (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. An example of adding a check constraint to limit the salary that an employee can earn is: Mark for Review
(1) Points


ALTER TABLE employees CONSTRAINT emp_salary_ck CHECK(salary < 100000)


ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000) (*)


ALTER TABLE employees ADD CONSTRAINT emp_salary_ck SALARY < 100000


MODIFY TABLE employees ADD CONSTRAINT emp_salary_ck CHECK(salary < 100000



Incorrect Incorrect. Refer to Section 14 Lesson 2.


12. You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column? Mark for Review
(1) Points


PRIMARY KEY


NOT NULL


FOREIGN KEY (*)


UNIQUE



Incorrect Incorrect. Refer to Section 14 Lesson 2.


13. Which statement about a non-mandatory foreign key constraint is true? Mark for Review
(1) Points


A foreign key value must be unique.


A foreign key value must match an existing value in the parent table.


A foreign key value must either be null or match an existing value in the parent table. (*)


A foreign key value cannot be null.



Correct Correct


14. Which constraint type enforces uniqueness? Mark for Review
(1) Points


CHECK


NOT NULL


PRIMARY KEY (*)


FOREIGN KEY



Incorrect Incorrect. Refer to Section 14 Lesson 2.


15. How many PRIMARY KEY constraints can be created for each table? Mark for Review
(1) Points


None


One and only one (*)


One or two


Unlimited



Incorrect Incorrect. Refer to Section 14 Lesson 2.


Previous Page 3 of 3 Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column? Mark for Review
(1) Points


PRIMARY KEY


FOREIGN KEY (*)


NOT NULL


UNIQUE



Correct Correct


2. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points


RESEMBLES


ON DELETE CASCADE


REFERENCES (*)


REFERENTIAL



Incorrect Incorrect. Refer to Section 14 Lesson 2.


3. What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table? Mark for Review
(1) Points


A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.


A CHECK constraint must exist on the Parent table.


An index must exist on the Parent table


A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)



Incorrect Incorrect. Refer to Section 14 Lesson 2.


4. What is an attribute of the data that is entered into a primary key column? Mark for Review
(1) Points


Data that is entered into a primary key column references a column of the same datatype in another table.


Data that is entered into a primary key column is restricted to a range of numbers that is defined by the local Oracle database.


Null and non-unique values cannot be entered into a primary key column. (*)


Data that is entered into a primary key column automatically increments by a value of 1 each time a new record is entered into the table.



Correct Correct


5. Which type of constraint by default requires that a column be both unique and not null? Mark for Review
(1) Points


PRIMARY KEY (*)


UNIQUE


FOREIGN KEY


CHECK



Correct Correct


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. 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 requires a column to contain alphanumeric values.


The NOT NULL constraint must be defined at the column level. (*)


The NOT NULL constraint prevents a column from containing alphanumeric values.



Incorrect Incorrect. Refer to Section 14 Lesson 1.


7. Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


Null Field


Row


Column (*)


Dictionary


Table (*)



Correct Correct


8. Which two statements about NOT NULL constraints are true? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


Columns with a NOT NULL constraint can contain null values by default.


The NOT NULL constraint requires that every value in a column be unique.


You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement. (*)


A NOT NULL constraint can be defined at either the table or column level.


The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


9. What is the highest number of NOT NULL constraints you can have on a table? Mark for Review
(1) Points


5


10


3


You can have as many NOT NULL constraints as you have columns in your table. (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


10. You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column? Mark for Review
(1) Points


PRIMARY KEY


CHECK


UNIQUE (*)


NOT NULL



Incorrect Incorrect. Refer to Section 14 Lesson 1.


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

 Mark for Review
(1) Points


An existing constraint on the EMPLOYEES table will be enabled.


An existing constraint on the EMPLOYEES table will be overwritten.


A constraint will be added to the EMPLOYEES table.


A syntax error will be returned. (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


12. You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use? Mark for Review
(1) Points


ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)


ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;


ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);


ALTER TABLE employees
ADD CONSTRAINT PRIMARY KEY (emp_id);



Correct Correct


13. 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 fk_dept_id_01;


ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)



ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';



Correct Correct


14. You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
ALTER TABLE employees
ENABLE employee_id_pk;

Which statement is true?

 Mark for Review
(1) Points


The statement will achieve the desired result.


The statement will execute, but will ensure that the new ID values are unique.


The statement will NOT execute because it contains a syntax error. (*)


The statement will execute, but will not verify that the existing values are unique.



Incorrect Incorrect. Refer to Section 14 Lesson 3.


15. Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points


To remove all constraint references to the PRODUCTS table


To drop the FOREIGN KEY constraint on the PRODUCTS table


To remove all constraint references to SUPPLIERS table


To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)


To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table



Correct Correct


Previous Page 3 of 3 Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

 Mark for Review
(1) Points


An existing constraint on the EMPLOYEES table will be enabled.


An existing constraint on the EMPLOYEES table will be overwritten.


A constraint will be added to the EMPLOYEES table.


A syntax error will be returned. (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


12. You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use? Mark for Review
(1) Points


ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)


ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;


ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);


ALTER TABLE employees
ADD CONSTRAINT PRIMARY KEY (emp_id);



Correct Correct


13. 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 fk_dept_id_01;


ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)



ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';



Correct Correct


14. You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
ALTER TABLE employees
ENABLE employee_id_pk;

Which statement is true?

 Mark for Review
(1) Points


The statement will achieve the desired result.


The statement will execute, but will ensure that the new ID values are unique.


The statement will NOT execute because it contains a syntax error. (*)


The statement will execute, but will not verify that the existing values are unique.



Incorrect Incorrect. Refer to Section 14 Lesson 3.


15. Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

 Mark for Review
(1) Points


To remove all constraint references to the PRODUCTS table


To drop the FOREIGN KEY constraint on the PRODUCTS table


To remove all constraint references to SUPPLIERS table


To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)


To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table



Correct Correct


Previous Page 3 of 3 Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


2. 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 CONSTRAINT fk_dept_id_01;
(*)



ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';


ALTER TABLE employees
DISABLE fk_dept_id_01;


ALTER TABLE employees
DISABLE 'fk_dept_id_01';



Correct Correct


3. You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
ALTER TABLE employees
ENABLE employee_id_pk;

Which statement is true?

 Mark for Review
(1) Points


The statement will achieve the desired result.


The statement will NOT execute because it contains a syntax error. (*)


The statement will execute, but will ensure that the new ID values are unique.


The statement will execute, but will not verify that the existing values are unique.



Correct Correct


4. 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 FOREIGN KEY CONSTRAINT dept_id_fk ON (department_id) REFERENCES departments(department_id);


ALTER TABLE employees
ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);
(*)



ALTER TABLE employees
MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);


ALTER TABLE employees
ADD FOREIGN KEY departments(department_id) REFERENCES (department_id);



Correct Correct


5. Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

 Mark for Review
(1) Points


A syntax error will be returned. (*)


A constraint will be added to the EMPLOYEES table.


An existing constraint on the EMPLOYEES table will be enabled.


An existing constraint on the EMPLOYEES table will be overwritten.



Correct Correct


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


7. If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


8. 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


PRIMARY KEY


CHECK (*)


NOT NULL


UNIQUE



Incorrect Incorrect. Refer to Section 14 Lesson 1.


9. A table must have at least one not null constraint and one unique constraint. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


10. Which constraint can only be created at the column level? Mark for Review
(1) Points


FOREIGN KEY


CHECK


NOT NULL (*)


UNIQUE



Incorrect Incorrect. Refer to Section 14 Lesson 1.


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. A composite primary key may only be defined at the table level. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


12. The main reason that constraints are added to a table is: Mark for Review
(1) Points


Constraints add a level of complexity


Constraints ensure data integrity (*)


Constraints gives programmers job security


None of the Above



Incorrect Incorrect. Refer to Section 14 Lesson 2.


13. Which line of the following code will cause an error:
CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
department_id VARCHAR(3),
CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
 Mark for Review
(1) Points


Line 2


Line 3


Line 5 (*)


Line 7



Incorrect Incorrect. Refer to Section 14 Lesson 2.


14. When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more? Mark for Review
(1) Points


CONSTRAINT CHECK salary > 1000


CONSTRAINT employee_salary_min CHECK (salary >= 1000) (*)


CHECK CONSTRAINT employee_salary_min (salary > 1000)


CHECK CONSTRAINT (salary > 1000)


CONSTRAINT employee_salary_min CHECK salary > 1000



Correct Correct


15. 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



Incorrect Incorrect. Refer to Section 14 Lesson 2.


Previous Page 3 of 3 Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points


REFERENCES (*)


RESEMBLES


REFERENTIAL


ON DELETE CASCADE



Correct Correct


2. The number of check constraints that can be defined on a column is: Mark for Review
(1) Points


10


5


100


There is no limit (*)



Correct Correct


3. The main reason that constraints are added to a table is: Mark for Review
(1) Points


Constraints add a level of complexity


Constraints ensure data integrity (*)


Constraints gives programmers job security


None of the Above



Correct Correct


4. 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



Correct Correct


5. Which of the following pieces of code will successfully create a foreign key in the CDS table that references the SONGS table? Mark for Review
(1) Points


CONSTRAINT d_cd_ song_id_fk FOREIGN KEY (song_id)REFERENCES d_songs(id)


CONSTRAINT k_cd_songid_fk FOREIGN KEY (song_id)REFERENCES d_songs(id)


song_id NUMBER(5) CONSTRAINT d_cd_ song_id_fk REFERENCES d_songs(id)


All of the above (*)



Incorrect Incorrect. Refer to Section 14 Lesson 2.


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. 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


CHECK (*)


NOT NULL


PRIMARY KEY


UNIQUE



Correct Correct


7. A unique key constraint can only be defined on a not null column. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


8. You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task? Mark for Review
(1) Points


ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);


ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);


ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);


ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)




Correct Correct


9. Which statement about constraints is true? Mark for Review
(1) Points


NOT NULL constraints can only be specified at the column level. (*)


UNIQUE constraints are identical to PRIMARY KEY constraints.


A single column can have only one constraint applied.


PRIMARY KEY constraints can only be specified at the column level.



Incorrect Incorrect. Refer to Section 14 Lesson 1.


10. Evaluate this CREATE TABLE statement:
CREATE TABLE customers
    (customer_id NUMBER,
     customer_name VARCHAR2(25),
     address VARCHAR2(25),
     city VARCHAR2(25),
     region VARCHAR2(25),
     postal_code VARCHAR2(11),
     CONSTRAINT customer_id_un UNIQUE(customer_id),
     CONSTRAINT customer_name_nn NOT NULL(customer_name));

Why does this statement fail when executed?

 Mark for Review
(1) Points


UNIQUE constraints must be defined at the column level.


NOT NULL constraints CANNOT be defined at the table level. (*)


The CREATE TABLE statement does NOT define a PRIMARY KEY.


The NUMBER data types require precision values.



Correct Correct


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. The DEPARTMENTS table contains these columns:
DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
HIRE_DATE DATE

Evaluate this statement:

ALTER TABLE employees
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);

Which statement is true?

 Mark for Review
(1) Points


The ALTER TABLE statement creates a referential constraint from the EMPLOYEES table to the DEPARTMENTS table.


The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.


The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)


The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.



Incorrect Incorrect. Refer to Section 14 Lesson 3.


12. You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use? Mark for Review
(1) Points


ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id);
(*)



ALTER TABLE departments
ADD PRIMARY KEY dept_id_pk (dept_id);


ALTER TABLE departments
ADD CONSTRAINT PRIMARY KEY dept_id_pk (dept_id);


ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PK (dept_id);



Correct Correct


13. What actions can be performed on or with Constraints? Mark for Review
(1) Points


Add, Minus, Enable, Disable, Collapse


Add, Subtract, Enable, Cascade


Add, Drop, Disable, Disregard


Add, Drop, Enable, Disable, Cascade (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


14. The command to 'switch off' a constraint is: Mark for Review
(1) Points


ALTER TABLE DISABLE CONSTRAINT (*)


ALTER TABLE STOP CHECKING


ALTER TABLE PAUSE CONSTRAINT


ALTER TABLE STOP CONSTRAINTS



Correct Correct


15. This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

 Mark for Review
(1) Points


Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.


Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.


Alter the table employees and disable the emp_manager_fk constraint.


Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)



Correct Correct


Previous Page 3 of 3 Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column? Mark for Review
(1) Points


PRIMARY KEY


FOREIGN KEY (*)


NOT NULL


UNIQUE



Correct Correct


2. Foreign Key Constraints are also known as: Mark for Review
(1) Points


Child Key Constraints


Multi-Table Constraints


Parental Key Constraints


Referential Integrity Constraints (*)



Correct Correct


3. What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table? Mark for Review
(1) Points


A CHECK constraint must exist on the Parent table.


An index must exist on the Parent table


A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)


A FOREIGN KEY constraint allows the constrained column to contain values that exist in the primary key column of the parent table.



Correct Correct


4. Which statement about a FOREIGN KEY constraint is true? Mark for Review
(1) Points


A FOREIGN KEY column can have a different data type from the primary key column that it references.


An index is automatically created for a FOREIGN KEY constraint.


A FOREIGN KEY constraint allows that a list of allowed values be checked before a value can be added to the constrained column.


A FOREIGN KEY constraint requires the constrained column to contain values that exist in the referenced Primary or Unique key column of the parent table. (*)



Correct Correct


5. 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 (*)



Incorrect Incorrect. Refer to Section 14 Lesson 2.


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. Which statement about constraints is true? Mark for Review
(1) Points


PRIMARY KEY constraints can only be specified at the column level.


UNIQUE constraints are identical to PRIMARY KEY constraints.


A single column can have only one constraint applied.


NOT NULL constraints can only be specified at the column level. (*)



Correct Correct


7. Which two statements about NOT NULL constraints are true? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


Columns with a NOT NULL constraint can contain null values by default.


A NOT NULL constraint can be defined at either the table or column level.


The NOT NULL constraint requires that every value in a column be unique.


The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)


You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement. (*)



Correct Correct


8. What is the highest number of NOT NULL constraints you can have on a table? Mark for Review
(1) Points


5


10


3


You can have as many NOT NULL constraints as you have columns in your table. (*)



Correct Correct


9. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


10. Which constraint can only be created at the column level? Mark for Review
(1) Points


CHECK


FOREIGN KEY


NOT NULL (*)


UNIQUE



Correct Correct


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. The DEPARTMENTS table contains these columns:
DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
HIRE_DATE DATE

Evaluate this statement:

ALTER TABLE employees
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);

Which statement is true?

 Mark for Review
(1) Points


The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.


The ALTER TABLE statement creates a referential constraint from the EMPLOYEES table to the DEPARTMENTS table.


The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.


The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)



Correct Correct


12. When dropping a constraint, which keyword(s) specifies that all the referential integrity constraints that refer to the primary and unique keys defined on the dropped columns are dropped as well? Mark for Review
(1) Points


REFERENCES


CASCADE (*)


ON DELETE SET NULL


FOREIGN KEY



Incorrect Incorrect. Refer to Section 14 Lesson 3.


13. Once constraints have been created on a table, you will have to live with them as they are unless you drop and re-create the table. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 14 Lesson 3.


14. Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

 Mark for Review
(1) Points


An existing constraint on the EMPLOYEES table will be enabled.


A constraint will be added to the EMPLOYEES table.


An existing constraint on the EMPLOYEES table will be overwritten.


A syntax error will be returned. (*)



Correct Correct


15. You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table? Mark for Review
(1) Points


US_CON_SYS


CONSTRAINTS_ALL_COLUMNS


USER_CONS_COLUMNS (*)


SYS_DATA_DICT_COLUMNS



Correct Correct

Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

1. You need to create the PROJECT_HIST table. The table must meet these requirements:
The table must contain the EMPLOYEE_ID and TASKED_HOURS columns for numeric data.
The table must contain the START_DATE and END_DATE column for date values.
The table must contain the HOURLY_RATE and PROJECT_COST columns for numeric data with precision and scale of 5,2 and 10,2 respectively.
The table must have a composite primary key on the EMPLOYEE_ID and START_DATE columns.
Evaluate this CREATE TABLE statement:

CREATE TABLE project_hist
( employee_id NUMBER,
start_date DATE,
end_date DATE,
tasked_hours NUMBER,
hourly_rate NUMBER(5,2),
project_cost NUMBER(10,2),
CONSTRAINT project_hist_pk PRIMARY KEY(employee_id, start_date));

How many of the requirements does the CREATE TABLE statement satisfy?

 Mark for Review
(1) Points


None of the four requirements


All four of the requirements (*)


Only three of the requirements


Only two of the requirements



Incorrect Incorrect. Refer to Section 14 Lesson 2.


2. A Primary Key that is made up of more than one column is called a: Mark for Review
(1) Points


Multiple Primary Key


Composite Primary Key (*)


Double Key


Primary Multi-Key


None of the Above



Incorrect Incorrect. Refer to Section 14 Lesson 2.


3. 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



Correct Correct


4. The number of check constraints that can be defined on a column is: Mark for Review
(1) Points


10


5


100


There is no limit (*)



Correct Correct


5. Which line of the following code will cause an error:
CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
department_id VARCHAR(3),
CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
 Mark for Review
(1) Points


Line 2


Line 3


Line 5 (*)


Line 7



Correct Correct


Page 1 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

6. A unique key constraint can only be defined on a not null column. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


7. Which statement about constraints is true? Mark for Review
(1) Points


PRIMARY KEY constraints can only be specified at the column level.


NOT NULL constraints can only be specified at the column level. (*)


UNIQUE constraints are identical to PRIMARY KEY constraints.


A single column can have only one constraint applied.



Correct Correct


8. If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


9. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 14 Lesson 1.


10. Which constraint can only be created at the column level? Mark for Review
(1) Points


UNIQUE


CHECK


FOREIGN KEY


NOT NULL (*)



Correct Correct


Previous Page 2 of 3 Next Summary


Test: Section 14 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14 Quiz
(Answer all questions in this section)

11. You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


12. The command to 'switch off' a constraint is: Mark for Review
(1) Points


ALTER TABLE DISABLE CONSTRAINT (*)


ALTER TABLE STOP CONSTRAINTS


ALTER TABLE PAUSE CONSTRAINT


ALTER TABLE STOP CHECKING



Correct Correct


13. You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query? Mark for Review
(1) Points


USER_CONS_COLUMNS


USER_CONSTRAINTS (*)


ALL_CONS_COLUMNS


DBA_CONSTRAINTS



Incorrect Incorrect. Refer to Section 14 Lesson 3.


14. 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
MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);


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);



Correct Correct


15. You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use? Mark for Review
(1) Points


ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id);
(*)



ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PK (dept_id);


ALTER TABLE departments
ADD CONSTRAINT PRIMARY KEY dept_id_pk (dept_id);


ALTER TABLE departments
ADD PRIMARY KEY dept_id_pk (dept_id);



Correct Correct


Previous Page 3 of 3 Summary