Oracle Database Programming with SQL Section 12
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
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)
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;
DISABLE CONSTRAINT supplier_id_pk CASCADE;
For which task would you issue this statement?
♦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)
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;
DISABLE CONSTRAINT product_id_pk CASCADE;
For which task would you issue this statement?
♦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
♦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?
♦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.
♦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?
♦CASCADE (*)
♦ON DELETE SET NULL
♦FOREIGN KEY
♦REFERENCES
♦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:
♦CONSTRAINTS
♦USER_CONSTRAINTS (*)
♦TABLE_CONSTRAINTS
♦USER_TABLES
♦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
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?
♦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);
(*)
(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;
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);
(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);
(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?
♦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)
♦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?
♦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. (*)
♦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?
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?
♦5
♦6
♦7 (*)
♦8
♦6
♦7 (*)
♦8
10. Which type of constraint by default requires that a column be both unique and not null?
♦UNIQUE
♦FOREIGN KEY
♦PRIMARY KEY (*)
♦CHECK
♦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?
♦CHECK (*)
♦PRIMARY KEY
♦UNIQUE
♦NOT NULL
Which type of constraint should you define on the LAST_NAME column?
♦CHECK (*)
♦PRIMARY KEY
♦UNIQUE
♦NOT NULL
12. Which constraint can only be created at the column level?
♦NOT NULL (*)
♦UNIQUE
♦FOREIGN KEY
♦CHECK
♦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));
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?
♦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. (*)
♦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?
♦PRIMARY KEY
♦NOT NULL (*)
♦UNIQUE
♦CHECK
♦PRIMARY KEY
♦NOT NULL (*)
♦UNIQUE
♦CHECK
15. A table must have at least one not null constraint and one unique constraint. True or False?
♦True
♦False (*)
♦True
♦False (*)
16. 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?
♦REFERENCES
♦FOREIGN KEY
♦CASCADE (*)
♦ON DELETE SET NULL
♦REFERENCES
♦FOREIGN KEY
♦CASCADE (*)
♦ON DELETE SET NULL
17. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?
♦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;
♦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;
18. What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?
♦Nothing extra is created when Primary Keys and Unique Keys are created
♦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
♦Internal Pointers
♦Ordered Lists
19. 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?
♦ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
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);
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);
(*)
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);
FOREIGN KEY CONSTRAINT fk_employee_id_ REFERENCES employees (employee_id);
20. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP CONSTRAINT constraint_name;
♦DROP CONSTRAINT table_name (constraint_name);
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
DROP CONSTRAINT constraint_name CASCADE;
(*)
♦ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT FOREIGN KEY CASCADE;
21. 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?
♦ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);
MODIFY COLUMN (cost part_cost_nn NOT NULL);
♦ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)
♦ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);
ADD (cost CONSTRAINT part_cost_nn NOT NULL);
♦ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);
MODIFY (cost part_cost_nn NOT NULL);
22. 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?
♦CHECK
♦PRIMARY KEY
♦UNIQUE (*)
♦NOT NULL
♦CHECK
♦PRIMARY KEY
♦UNIQUE (*)
♦NOT NULL
23. Which statement about the NOT NULL constraint is true?
♦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.
♦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.
24. Which two statements about NOT NULL constraints are true? (Choose two)
(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.
(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. (*)
ADD CONSTRAINT statement. (*)
♦Columns with a NOT NULL constraint can contain null values by default.
25. What is the highest number of NOT NULL constraints you can have on a table?
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
26. 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));
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));
♦Line 2
♦Line 3
♦Line 5 (*)
♦Line 7
♦Line 3
♦Line 5 (*)
♦Line 7
27. 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
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?
♦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);
(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;
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);
(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);
(*)
(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);
(*)
28. A Primary Key that is made up of more than one column is called a:
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
29. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?
♦REFERENTIAL
♦ON DELETE CASCADE
♦REFERENCES (*)
♦RESEMBLES
♦REFERENTIAL
♦ON DELETE CASCADE
♦REFERENCES (*)
♦RESEMBLES
30. What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child 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
♦A PRIMARY or UNIQUE KEY 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. (*)
31. A composite primary key may only be defined at the table level. True or False?
♦True (*)
♦False
♦True (*)
♦False
32. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?
♦ON DELETE CASCADE
♦REFERENCES (*)
♦RESEMBLES
♦REFERENTIAL
♦ON DELETE CASCADE
♦REFERENCES (*)
♦RESEMBLES
♦REFERENTIAL
33. 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?
♦NOT NULL
♦UNIQUE
♦PRIMARY KEY
♦FOREIGN KEY (*)
♦NOT NULL
♦UNIQUE
♦PRIMARY KEY
♦FOREIGN KEY (*)
34. What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child 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.
♦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.
♦A CHECK constraint must exist on the Parent table.
♦An index must exist on the Parent table
35. Which constraint type enforces uniqueness?
♦NOT NULL
♦PRIMARY KEY (*)
♦CHECK
♦FOREIGN KEY
♦NOT NULL
♦PRIMARY KEY (*)
♦CHECK
♦FOREIGN KEY
36. 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?
♦UNIQUE (*)
♦PRIMARY KEY
♦CHECK
♦NOT NULL
♦UNIQUE (*)
♦PRIMARY KEY
♦CHECK
♦NOT NULL
37. Which of the following is not a valid Oracle constraint type?
♦NOT NULL
♦EXTERNAL KEY (*)
♦UNIQUE KEY
♦PRIMARY KEY
♦NOT NULL
♦EXTERNAL KEY (*)
♦UNIQUE KEY
♦PRIMARY KEY
38. Which statement about constraints is true?
♦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.
♦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.
39. If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
♦True (*)
♦False
♦True (*)
♦False
40. 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?
♦PRIMARY KEY
♦NOT NULL
♦CHECK (*)
♦UNIQUE
Which type of constraint should you define on the LAST_NAME column?
♦PRIMARY KEY
♦NOT NULL
♦CHECK (*)
♦UNIQUE
41. You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?
♦CHANGE
♦MODIFY (*)
♦ADD
♦DISABLE
♦CHANGE
♦MODIFY (*)
♦ADD
♦DISABLE
42. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?
♦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;
♦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;
43. 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?
♦ALTER TABLE employees
DISABLE fk_dept_id_01;
DISABLE fk_dept_id_01;
♦ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)
DISABLE CONSTRAINT fk_dept_id_01;
(*)
♦ALTER TABLE employees
DISABLE CONSTRAINT ‘fk_dept_id_01’;
DISABLE CONSTRAINT ‘fk_dept_id_01’;
♦ALTER TABLE employees
DISABLE ‘fk_dept_id_01’;
DISABLE ‘fk_dept_id_01’;
44. All of a user’s constraints can be viewed in the Oracle Data Dictionary view called:
♦CONSTRAINTS
♦USER_TABLES
♦USER_CONSTRAINTS (*)
♦TABLE_CONSTRAINTS
♦CONSTRAINTS
♦USER_TABLES
♦USER_CONSTRAINTS (*)
♦TABLE_CONSTRAINTS
45. You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?
♦ALTER TABLE employees
ADD CONSTRAINT PRIMARY KEY (emp_id);
ADD CONSTRAINT PRIMARY KEY (emp_id);
♦ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)
♦ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);
MODIFY CONSTRAINT PRIMARY KEY (emp_id);
♦ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;
MODIFY emp_id PRIMARY KEY;
46. A Primary Key that is made up of more than one column is called a:
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
47. 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
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?
♦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);
(*)
(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);
(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;
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);
(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);
48. To automatically delete rows in a child table when a parent record is deleted use:
♦ON DELETE SET NULL
♦ON DELETE ORPHAN
♦ON DELETE CASCADE (*)
♦None of the Above
♦ON DELETE ORPHAN
♦ON DELETE CASCADE (*)
♦None of the Above
49. 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
♦ON DELETE CASCADE
♦ON DELETE SET NULL
♦Neither A nor B (*)
♦Both A and B
50. Foreign Key Constraints are also known as:
♦Parental Key Constraints
♦Child Key Constraints
♦Referential Integrity Constraints (*)
♦Multi-Table Constraints
♦Parental Key Constraints
♦Child Key Constraints
♦Referential Integrity Constraints (*)
♦Multi-Table Constraints
51. 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;
ALTER TABLE employees
ENABLE employee_id_pk;
Which statement is true?
♦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.
♦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.
52. 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?
♦ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY salary (employee_id) = employees (employee_id);
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);
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);
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);
(*)
ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)
53. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP CONSTRAINT constraint_name;
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
DROP CONSTRAINT constraint_name CASCADE;
(*)
♦ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT FOREIGN KEY CASCADE;
♦DROP CONSTRAINT table_name (constraint_name);
54. 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)
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?
♦ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;
DROP CONSTRAINT product_id_fk;
♦ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)
DISABLE CONSTRAINT product_id_fk;
(*)
♦ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;
ENABLE CONSTRAINT product_id_fk;
♦ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;
DELETE CONSTRAINT product_id_fk;
55. This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
♦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.
56. 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?
♦PRIMARY KEY
♦NOT NULL
♦CHECK (*)
♦UNIQUE
Which type of constraint should you define on the LAST_NAME column?
♦PRIMARY KEY
♦NOT NULL
♦CHECK (*)
♦UNIQUE
57. Which constraint can only be created at the column level?
♦CHECK
♦UNIQUE
♦FOREIGN KEY
♦NOT NULL (*)
♦CHECK
♦UNIQUE
♦FOREIGN KEY
♦NOT NULL (*)
58. 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));
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?
♦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.
♦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.
59. Which of the following is not a valid Oracle constraint type?
♦PRIMARY KEY
♦UNIQUE KEY
♦EXTERNAL KEY (*)
♦NOT NULL
♦PRIMARY KEY
♦UNIQUE KEY
♦EXTERNAL KEY (*)
♦NOT NULL
60. What is the highest number of NOT NULL constraints you can have on a table?
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
61. A unique key constraint can only be defined on a not null column. True or False?
♦True
♦False (*)
♦True
♦False (*)
62. What is the highest number of NOT NULL constraints you can have on a table?
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
♦5
♦10
♦3
♦You can have as many NOT NULL constraints as you have columns in your table. (*)
63. A table can only have one unique key constraint defined. True or False?
♦True
♦False (*)
♦True
♦False (*)
64. Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two)
(Choose all correct answers)
♦Dictionary
♦Null Field
♦Column (*)
♦Table (*)
♦Row
(Choose all correct answers)
♦Dictionary
♦Null Field
♦Column (*)
♦Table (*)
♦Row
65. 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?
♦NOT NULL
♦PRIMARY KEY
♦CHECK (*)
♦UNIQUE
Which type of constraint should you define on the LAST_NAME column?
♦NOT NULL
♦PRIMARY KEY
♦CHECK (*)
♦UNIQUE
66. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?
♦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; (*)
♦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; (*)
67. What actions can be performed on or with Constraints?
♦Add, Drop, Enable, Disable, Cascade (*)
♦Add, Subtract, Enable, Cascade
♦Add, Drop, Disable, Disregard
♦Add, Minus, Enable, Disable, Collapse
♦Add, Drop, Enable, Disable, Cascade (*)
♦Add, Subtract, Enable, Cascade
♦Add, Drop, Disable, Disregard
♦Add, Minus, Enable, Disable, Collapse
68. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
♦ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT FOREIGN KEY CASCADE;
♦DROP CONSTRAINT table_name (constraint_name);
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP CONSTRAINT constraint_name;
♦ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)
DROP CONSTRAINT constraint_name CASCADE;
(*)
69. The command to ‘switch off’ a constraint is:
♦ALTER TABLE PAUSE CONSTRAINT
♦ALTER TABLE STOP CONSTRAINTS
♦ALTER TABLE DISABLE CONSTRAINT (*)
♦ALTER TABLE STOP CHECKING
♦ALTER TABLE PAUSE CONSTRAINT
♦ALTER TABLE STOP CONSTRAINTS
♦ALTER TABLE DISABLE CONSTRAINT (*)
♦ALTER TABLE STOP CHECKING
70. 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
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)
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;
DISABLE CONSTRAINT supplier_id_pk CASCADE;
For which task would you issue this statement?
♦To remove all constraint references to the PRODUCTS table
♦To remove all constraint references to SUPPLIERS 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
71. Which clause could you use to ensure that cost values are greater than 1.00?
♦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)
♦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)
72. 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));
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));
♦Line 2
♦Line 3
♦Line 5 (*)
♦Line 7
♦Line 3
♦Line 5 (*)
♦Line 7
73. A composite primary key may only be defined at the table level. True or False?
♦True (*)
♦False
♦True (*)
♦False
74. 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?
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?
♦5
♦6
♦7 (*)
♦8
♦6
♦7 (*)
♦8
75. What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?
♦A PRIMARY or UNIQUE KEY constraint must exist on the Parent table. (*)
♦A CHECK constraint must exist on the Parent table.
♦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
76. 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?
♦NOT NULL
♦NOT NULL
♦FOREIGN KEY (*)
♦PRIMARY KEY
♦UNIQUE
♦PRIMARY KEY
♦UNIQUE
77. A Primary Key that is made up of more than one column is called a:
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
♦Multiple Primary Key
♦Composite Primary Key (*)
♦Double Key
♦Primary Multi-Key
♦None of the Above
78. 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 is created automatically when a PRIMARY KEY constraint is created.
♦A CHECK constraint enforces referential data integrity.
♦A CHECK constraint enforces referential data integrity.
79. A composite primary key may only be defined at the table level. True or False?
♦True (*)
♦False
♦True (*)
♦False
80. 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?
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?
♦5
♦6
♦7 (*)
♦8
♦6
♦7 (*)
♦8
81. Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;
Which result will the statement provide?
♦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.
♦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.
82. 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)
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?
♦ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;
DELETE CONSTRAINT product_id_fk;
♦ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)
DISABLE CONSTRAINT product_id_fk;
(*)
♦ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;
ENABLE CONSTRAINT product_id_fk;
♦ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;
DROP CONSTRAINT product_id_fk;
83. You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?
♦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;
♦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;
84. Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
♦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 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 (*)
85. This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
♦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. (*)
86. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
♦True
♦False (*)
♦True
♦False (*)
87. 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?
♦CHECK
♦PRIMARY KEY
♦NOT NULL (*)
♦UNIQUE
♦CHECK
♦PRIMARY KEY
♦NOT NULL (*)
♦UNIQUE
88. Which constraint can only be created at the column level?
♦UNIQUE
♦CHECK
♦NOT NULL (*)
♦FOREIGN KEY
♦UNIQUE
♦CHECK
♦NOT NULL (*)
♦FOREIGN KEY
89. 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?
♦CHECK
♦NOT NULL
♦PRIMARY KEY
♦UNIQUE (*)
♦CHECK
♦NOT NULL
♦PRIMARY KEY
♦UNIQUE (*)
90. Which statement about constraints is true?
♦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. (*)
♦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.
InfoCad Blog 