LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)
You need to disable the FOREIGN KEY constraint. Which statement should you use?
Mark for Review
(1) Points
ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;
ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)
ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;
ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;
8. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use? Mark for Review
(1) Points
ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
DROP CONSTRAINT EMP_FK_DEPT FROM employees;
DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
9. 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 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
To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)
10. 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.
Alter the table employees and disable the emp_manager_fk constraint.
Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.
Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)
11. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
(1) Points
True
False (*)
12. You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column? Mark for Review
(1) Points
CHECK
PRIMARY KEY
NOT NULL (*)
UNIQUE
13. Which constraint can only be created at the column level? Mark for Review
(1) Points
UNIQUE
CHECK
NOT NULL (*)
FOREIGN KEY
14. 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
CHECK
NOT NULL
PRIMARY KEY
UNIQUE (*)
2. Which statement about constraints is true? Mark for Review
(1) Points
UNIQUE constraints are identical to PRIMARY KEY constraints.
PRIMARY KEY constraints can only be specified at the column level.
NOT NULL constraints can only be specified at the column level. (*)
A single column can have only one constraint applied.
1. 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 disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
To remove all constraint references to SUPPLIERS table
To drop the FOREIGN KEY constraint on the PRODUCTS table
To remove all constraint references to the PRODUCTS table
2. 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 disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)
To create a new PRIMARY KEY constraint on the PO_NUM column
To drop and recreate the PRIMARY KEY constraint on the PO_NUM column
To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
3. Which of the following would definitely cause an integrity constraint error? Mark for Review
(1) Points
Using the DELETE command on a row that contains a primary key with a dependent foreign key declared without either an ON DELETE CASCADE or ON DELETE SET NULL. (*)
Using the UPDATE command on rows based in another table.
Using a subquery in an INSERT statement.
Using the MERGE statement to conditionally insert or update rows.
4. 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
CASCADE (*)
ON DELETE SET NULL
FOREIGN KEY
REFERENCES
5. All of a user's constraints can be viewed in the Oracle Data Dictionary view called: Mark for Review
(1) Points
CONSTRAINTS
USER_CONSTRAINTS (*)
TABLE_CONSTRAINTS
USER_TABLES
6. 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 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);
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);
7. Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
(1) Points
CHECK CONSTRAINT part_cost_ck (cost > 1.00)
CONSTRAINT CHECK cost > 1.00
CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
CONSTRAINT CHECK part_cost_ck (cost > 1.00)
8. 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. (*)
9. Evaluate this CREATE TABLE statement:
CREATE TABLE part(
part_id NUMBER,
part_name VARCHAR2(25),
manufacturer_id NUMBER(9),
retail_price NUMBER(7,2) NOT NULL,
CONSTRAINT part_id_pk PRIMARY KEY(part_id),
CONSTRAINT cost_nn NOT NULL(cost),
CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
Which line will cause an error?
Mark for Review
(1) Points
5
6
7 (*)
8
10. Which type of constraint by default requires that a column be both unique and not null? Mark for Review
(1) Points
UNIQUE
FOREIGN KEY
PRIMARY KEY (*)
CHECK
11. 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 (*)
PRIMARY KEY
UNIQUE
NOT NULL
12. Which constraint can only be created at the column level? Mark for Review
(1) Points
NOT NULL (*)
UNIQUE
FOREIGN KEY
CHECK
13. 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.
The CREATE TABLE statement does NOT define a PRIMARY KEY.
NOT NULL constraints CANNOT be defined at the table level. (*)
14. You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column? Mark for Review
(1) Points
PRIMARY KEY
NOT NULL (*)
UNIQUE
CHECK
15. A table must have at least one not null constraint and one unique constraint. True or False? Mark for Review
(1) Points
True
False (*)
1. 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
FOREIGN KEY
CASCADE (*)
ON DELETE SET NULL
2. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use? Mark for Review
(1) Points
DROP CONSTRAINT EMP_FK_DEPT FROM employees;
DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
3. What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints? Mark for Review
(1) Points
Nothing extra is created when Primary Keys and Unique Keys are created
Unique key indexes are created in the background by Oracle when Primary key and Unique key constraints are created or enabled (*)
Internal Pointers
Ordered Lists
4. You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue? Mark for Review
(1) Points
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
BETWEEN salary (employee_id) AND employees (employee_id);
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)
ALTER TABLE salary
FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);
5. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints? Mark for Review
(1) Points
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP CONSTRAINT table_name (constraint_name);
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
6. 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
MODIFY (cost CONSTRAINT 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);
7. 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
CHECK
PRIMARY KEY
UNIQUE (*)
NOT NULL
8. Which statement about the NOT NULL constraint is true? Mark for Review
(1) Points
The NOT NULL constraint requires a column to contain alphanumeric values.
The NOT NULL constraint can be defined at either the column level or the table level.
The NOT NULL constraint must be defined at the column level. (*)
The NOT NULL constraint prevents a column from containing alphanumeric values.
9. Which two statements about NOT NULL constraints are true? (Choose two) Mark for Review
(1) Points
(Choose all correct answers)
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. (*)
A NOT NULL constraint can be defined at either the table or column level.
You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE
ADD CONSTRAINT statement. (*)
Columns with a NOT NULL constraint can contain null values by default.
10. 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. (*)
11. 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
12. 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 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);
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);
(*)
13. 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
14. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points
REFERENTIAL
ON DELETE CASCADE
REFERENCES (*)
RESEMBLES
15. 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. (*)
1. A composite primary key may only be defined at the table level. True or False? Mark for Review
(1) Points
True (*)
False
2. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points
ON DELETE CASCADE
REFERENCES (*)
RESEMBLES
REFERENTIAL
3. 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
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY (*)
4. 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 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.
A CHECK constraint must exist on the Parent table.
An index must exist on the Parent table
5. Which constraint type enforces uniqueness? Mark for Review
(1) Points
NOT NULL
PRIMARY KEY (*)
CHECK
FOREIGN KEY
6. 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
UNIQUE (*)
PRIMARY KEY
CHECK
NOT NULL
7. Which of the following is not a valid Oracle constraint type? Mark for Review
(1) Points
NOT NULL
EXTERNAL KEY (*)
UNIQUE KEY
PRIMARY KEY
8. Which statement about constraints is true? Mark for Review
(1) Points
NOT NULL constraints can only be specified at the column level. (*)
A single column can have only one constraint applied.
PRIMARY KEY constraints can only be specified at the column level.
UNIQUE constraints are identical to PRIMARY KEY constraints.
9. 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
10. 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
NOT NULL
CHECK (*)
UNIQUE
11. You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use? Mark for Review
(1) Points
CHANGE
MODIFY (*)
ADD
DISABLE
12. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use? Mark for Review
(1) Points
ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
DROP CONSTRAINT EMP_FK_DEPT FROM employees;
DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
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 CONSTRAINT fk_dept_id_01;
(*)
ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';
ALTER TABLE employees
DISABLE 'fk_dept_id_01';
14. All of a user's constraints can be viewed in the Oracle Data Dictionary view called: Mark for Review
(1) Points
CONSTRAINTS
USER_TABLES
USER_CONSTRAINTS (*)
TABLE_CONSTRAINTS
15. 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 PRIMARY KEY (emp_id);
ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)
ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);
ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;
1. 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
2. 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 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 NOT NULL,
donor_id NUMBER FOREIGN KEY donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
CREATE TABLE donations
pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE;
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);
3. To automatically delete rows in a child table when a parent record is deleted use: Mark for Review
(1) Points
ON DELETE SET NULL
ON DELETE ORPHAN
ON DELETE CASCADE (*)
None of the Above
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
5. Foreign Key Constraints are also known as: Mark for Review
(1) Points
Parental Key Constraints
Child Key Constraints
Referential Integrity Constraints (*)
Multi-Table Constraints
6. 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 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.
The statement will achieve the desired result.
7. You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue? Mark for Review
(1) Points
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
ALTER TABLE salary
FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
BETWEEN salary (employee_id) AND employees (employee_id);
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)
8. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints? Mark for Review
(1) Points
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT table_name (constraint_name);
9. The LINE_ITEM table contains these columns:
LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)
You need to disable the FOREIGN KEY constraint. Which statement should you use?
Mark for Review
(1) Points
ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;
ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)
ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;
ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;
10. 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
Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager ID to match every employee ID.
Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee. (*)
Alter the table employees and disable the emp_manager_fk constraint.
Alter table employees and add a FOREIGN KEY constraint that indicates each employee ID must be unique.
11. 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
NOT NULL
CHECK (*)
UNIQUE
12. Which constraint can only be created at the column level? Mark for Review
(1) Points
CHECK
UNIQUE
FOREIGN KEY
NOT NULL (*)
13. 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
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.
UNIQUE constraints must be defined at the column level.
14. Which of the following is not a valid Oracle constraint type? Mark for Review
(1) Points
PRIMARY KEY
UNIQUE KEY
EXTERNAL KEY (*)
NOT NULL
15. 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. (*)
1. A unique key constraint can only be defined on a not null column. True or False? Mark for Review
(1) Points
True
False (*)
2. 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. (*)
3. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points
True
False (*)
4. 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)
Dictionary
Null Field
Column (*)
Table (*)
Row
5. 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
NOT NULL
PRIMARY KEY
CHECK (*)
UNIQUE
6. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use? Mark for Review
(1) Points
DROP CONSTRAINT EMP_FK_DEPT FROM employees;
ALTER TABLE employees REMOVE CONSTRAINT EMP_FK_DEPT;
DELETE CONSTRAINT EMP_FK_DEPT FROM employees;
ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)
7. What actions can be performed on or with Constraints? Mark for Review
(1) Points
Add, Drop, Enable, Disable, Cascade (*)
Add, Subtract, Enable, Cascade
Add, Drop, Disable, Disregard
Add, Minus, Enable, Disable, Collapse
8. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints? Mark for Review
(1) Points
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT table_name (constraint_name);
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
9. The command to 'switch off' a constraint is: Mark for Review
(1) Points
ALTER TABLE PAUSE CONSTRAINT
ALTER TABLE STOP CONSTRAINTS
ALTER TABLE DISABLE CONSTRAINT (*)
ALTER TABLE STOP CHECKING
10. 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 PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
To drop the FOREIGN KEY constraint on the PRODUCTS table
11. Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
(1) Points
CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
CONSTRAINT CHECK cost > 1.00
CHECK CONSTRAINT part_cost_ck (cost > 1.00)
CONSTRAINT CHECK part_cost_ck (cost > 1.00)
12. 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
13. A composite primary key may only be defined at the table level. True or False? Mark for Review
(1) Points
True (*)
False
14. Evaluate this CREATE TABLE statement:
CREATE TABLE part(
part_id NUMBER,
part_name VARCHAR2(25),
manufacturer_id NUMBER(9),
retail_price NUMBER(7,2) NOT NULL,
CONSTRAINT part_id_pk PRIMARY KEY(part_id),
CONSTRAINT cost_nn NOT NULL(cost),
CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
Which line will cause an error?
Mark for Review
(1) Points
5
6
7 (*)
8
15. 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 PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
A CHECK 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.
An index must exist on the Parent table
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
NOT NULL
FOREIGN KEY (*)
PRIMARY KEY
UNIQUE
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
3. Which of the following best describes the function of a CHECK constraint? Mark for Review
(1) Points
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 is created automatically when a PRIMARY KEY constraint is created.
A CHECK constraint enforces referential data integrity.
4. A composite primary key may only be defined at the table level. True or False? Mark for Review
(1) Points
True (*)
False
5. Evaluate this CREATE TABLE statement:
CREATE TABLE part(
part_id NUMBER,
part_name VARCHAR2(25),
manufacturer_id NUMBER(9),
retail_price NUMBER(7,2) NOT NULL,
CONSTRAINT part_id_pk PRIMARY KEY(part_id),
CONSTRAINT cost_nn NOT NULL(cost),
CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
Which line will cause an error?
Mark for Review
(1) Points
5
6
7 (*)
8
6. 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 overwritten.
A constraint will be added to the EMPLOYEES table.
A syntax error will be returned. (*)
An existing constraint on the EMPLOYEES table will be enabled.
Comments
Post a Comment