Section
14 Quiz
(Answer all questions in this
section)
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.
7. 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
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 (*)
15. 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.
This is an excellent information I would like to say thanks for providing with us. check it once at MSBI Online Training Hyderabad
BalasHapusYou need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?
BalasHapusThe PRIMARY KEY constraint must be defined for the first column of the composite primary key.
The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.
The PRIMARY KEY constraint must be defined at the table level. (*)
A PRIMARY KEY constraint must be defined for each column in the composite primary key.
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?
BalasHapusFalse (*)
1- 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?
BalasHapusALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)
ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);
ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);
ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);
2- 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?
ON DELETE CASCADE
ON DELETE SET NULL
Neither A nor B (*)
Both A and B
3-The table that contains the Primary Key in a Foreign Key Constraint is known as:
Mother and Father Table
Detail Table
Child Table
Parent Table (*)
4-Which of the following best describes the function of a CHECK constraint?
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 uniqueness of the values that can be entered in a column or combination of columns.
A CHECK constraint enforces referential data integrity.
5-You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?
The PRIMARY KEY constraint must be defined for the first column of the composite primary key.
A PRIMARY KEY constraint must be defined for each column in the composite primary key.
The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.
The PRIMARY KEY constraint must be defined at the table level. (*)
6- How many PRIMARY KEY constraints can be created for each table?
None
One and only one (*)
One or two
Unlimited
6. A table can have more than one UNIQUE key constraint. True or False? Mark for Review
BalasHapus(1) Points
True (*)
False
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.
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 (*)
21. You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use?
BalasHapusMark for Review
(1) Points
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PK (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 PRIMARY KEY (dept_id); (*)
22. When creating a referential constraint, which keyword(s) identifies the table and column in the parent table?
Mark for Review
(1) Points
ON DELETE SET NULL
FOREIGN KEY
ON DELETE CASCADE
REFERENCES (*)
Un ejemplo de la adición de una restricción de control para limitar el salario que un empleado puede ganar es:
BalasHapusMarcar para Revisión
(1) Puntos
ALTER TABLE employees CONSTRAINT emp_salary_ck CHECK(salary < 100000)
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
MODIFY TABLE employees ADD CONSTRAINT emp_salary_ck CHECK(salary < 100000
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck SALARY < 100000
You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?
BalasHapusUSER_CONS_COLUMNS (*)
Which of the following pieces of code will successfully create a foreign key in the CDS table that references the SONGS table?
BalasHapusAll of the above (*)
A unique key constraint can only be defined on a not null column. True or False?
BalasHapusTrue
False (*)
You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query?
BalasHapusUSER_CONS_COLUMNS
DBA_CONSTRAINTS
ALL_CONS_COLUMNS
USER_CONSTRAINTS (*)
Thanks for the information. I really like the way you express complex topics in lucid way. It really helps me understand it much better way. company database
BalasHapusThanks to share this post.product details database is a very good product.
BalasHapusYou 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?
BalasHapusTrue
False (*)