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
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
Diposting
oleh Ali Ismail di 00.47
Which of
the following best describes the function of a CHECK constraint?
A CHECK constraint enforces uniqueness of the values that can be entered in a column or combination of columns.
A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*)
A CHECK constraint enforces referential data integrity.
A CHECK constraint is created automatically when a PRIMARY KEY constraint is created.
Correct
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,
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);
(*)
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 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);
A CHECK constraint enforces uniqueness of the values that can be entered in a column or combination of columns.
A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*)
A CHECK constraint enforces referential data integrity.
A CHECK constraint is created automatically when a PRIMARY KEY constraint is created.
Correct
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,
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);
(*)
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 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);
No comments:
Post a Comment