Section 14: Database programming with SQL

1.Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES
table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table?

ALTER TABLE employees

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

2.The DEPARTMENTS table contains these columns:

DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER

The EMPLOYEES table contains these columns:

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

Evaluate this statement:

ALTER TABLE employees

ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);

Which statement is true?

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

3.A table can have more than one UNIQUE key constraint. True or False?

True (*)

4.You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?

The PRIMARY KEY constraint must be defined at the table level.(*)

5.When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more?

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

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?

To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS 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?

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

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

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

5. All of a user's constraints can be viewed in the Oracle Data Dictionary view called:

USER_CONSTRAINTS (*)

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?

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

7. Which clause could you use to ensure that cost values are greater than 1.00?

CONSTRAINT part_cost_ck CHECK (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 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?

7 (*)

10. Which type of constraint by default requires that a column be both unique and not null?

PRIMARY KEY (*)

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

12. Which constraint can only be created at the column level?

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?

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?

NOT NULL (*)

15. A table must have at least one not null constraint and one unique constraint. True or False?

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?

CASCADE (*)

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

3. What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?

Unique key indexes are created in the background by Oracle when Primary key and Unique key constraints are created or enabled (*)

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?

ALTER TABLE salary

ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id)
REFERENCES employees (employee_id);
(*)

5. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?

ALTER TABLE table_name

DROP CONSTRAINT constraint_name 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?

ALTER TABLE part

MODIFY (cost CONSTRAINT 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?
UNIQUE (*)

8. Which statement about the NOT NULL constraint is true?

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

9. Which two statements about NOT NULL constraints are true? (Choose two)
The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)

You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE
ADD CONSTRAINT statement. (*)
10. What is the highest number of NOT NULL constraints you can have on a table?

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

Line 5 (*)

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?

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:

Composite Primary Key (*)

14. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?

REFERENCES (*)

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

1. A composite primary key may only be defined at the table level. True or False?

True (*)

2. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?

REFERENCES (*)

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?

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?

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

5. Which constraint type enforces uniqueness?

PRIMARY 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?

UNIQUE (*)

7. Which of the following is not a valid Oracle constraint type?

EXTERNAL KEY (*)

8. Which statement about constraints is true?

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

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?

True (*)

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?

CHECK (*)

11. You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?

MODIFY (*)

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

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?

ALTER TABLE employees

DISABLE CONSTRAINT fk_dept_id_01;
(*)

14. All of a user's constraints can be viewed in the Oracle Data Dictionary view called:

USER_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?

ALTER TABLE employees

ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)

1. A Primary Key that is made up of more than one column is called a:

Composite Primary Key (*)

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?

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

3. To automatically delete rows in a child table when a parent record is deleted use:

ON DELETE CASCADE (*)

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?

Neither A nor B (*)

5. Foreign Key Constraints are also known as:

Referential Integrity 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?

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

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?

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?
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)

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?

ALTER TABLE line_item

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

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

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

12. Which constraint can only be created at the column level?

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?

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

14. Which of the following is not a valid Oracle constraint type?

EXTERNAL KEY (*)

15. What is the highest number of NOT NULL constraints you can have on a table?

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?

False (*)

2. What is the highest number of NOT NULL constraints you can have on a table?
 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?

False (*)

4. Primary Key, Foreign Key, Unique Key, and Check Constraints can be added at which two levels? (Choose two)

Column (*)
Table (*)

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?

CHECK (*)

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

7. What actions can be performed on or with Constraints?

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

8. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?

ALTER TABLE table_name

DROP CONSTRAINT constraint_name CASCADE;
(*)

9. The command to 'switch off' a constraint is:

ALTER TABLE DISABLE CONSTRAINT (*)

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?

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

11. Which clause could you use to ensure that cost values are greater than 1.00?

CONSTRAINT part_cost_ck CHECK (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));

Line 5 (*)
13. A composite primary key may only be defined at the table level. True or False?

True (*)

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?

7 (*)

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

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?

FOREIGN KEY (*)

2. A Primary Key that is made up of more than one column is called a:

Composite Primary Key (*)

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

4. A composite primary key may only be defined at the table level. True or False?

True (*)

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?

7 (*)

6. Evaluate this statement:

ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;

Which result will the statement provide?

A syntax error will be returned. (*)

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?

ALTER TABLE line_item

DISABLE 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?

ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT; (*)

9. Evaluate this statement

ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;

For which task would you issue this statement?

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

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?

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?

NOT NULL (*)

13. Which constraint can only be created at the column level?

NOT NULL (*)

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?

UNIQUE (*)

15. Which statement about constraints is true?

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