Test: Final Exam Semester 2 - Part I (1-10)

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct
answer.
Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

Section 8

1. Which of the following SQL statements will create a table called

Birthdays with three columns for storing employee number, name and Mark for Review
date of birth? (1) Points

CREATE table BIRTHDAYS (EMPNO, EMPNAME,

BIRTHDATE);
CREATE table BIRTHDAYS (employee number, name, date of
birth);
CREATE TABLE Birthdays (Empno NUMBER, Empname
CHAR(20), Birthdate DATE); (*)
CREATE TABLE Birthdays (Empno NUMBER, Empname
CHAR(20), Date of Birth DATE);

Correct

2. Which statement about table and column names is true?

Mark for Review
(1) Points

Table and column names must begin with a letter. (*)

Table and column names can begin with a letter or a number.
Table and column names cannot include special characters.
If any character other than letters or numbers is used in a table or
column name, the name must be enclosed in double quotation
marks.

Correct

3. Evaluate this CREATE TABLE statement:

Mark for Review
(1) Points
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

1
2
3
4 (*)
 Correct

4. You are creating the EMPLOYEES table. This table should contain the
COMMISSION_PCT column and use a value of 10 percent if no Mark for Review
commission value is provided when a record is inserted. Which line (1) Points
should you include in the CREATE TABLE statement to accomplish
this task?

commission_pct NUMBER(4,2) DEFAULT 0.10 (*)

commission_pct NUMBER(4,2) DEFAULT = 0.10
commission_pct NUMBER(4,2) DEFAULT (0.10)
commission_pct NUMBER(4,2) (DEFAULT, 0.10)

Correct

5. Which CREATE TABLE statement will fail?

Mark for Review
(1) Points

CREATE TABLE date_1 (date_1 DATE);

CREATE TABLE date (date_id NUMBER(9)); (*)
CREATE TABLE time (time_id NUMBER(9));
CREATE TABLE time_date (time NUMBER(9));

Correct

6. Which column name is valid?

Mark for Review
(1) Points

1NUMBER
NUMBER
NUMBER_1$ (*)
1_NUMBER#

Correct

7. You are designing a table for the Human Resources department. This
table must include a column that contains each employee's hire date. Mark for Review
Which data type should you specify for this column? (1) Points

CHAR
DATE (*)
TIMESTAMP
INTERVAL YEAR TO MONTH
 Correct

8. Which data types stores variable-length character data? Select two.

Mark for Review
(1) Points

(Choose all correct answers)

CHAR
NCHAR
CLOB (*)
VARCHAR2 (*)

Correct

9. You need to store the HIRE_DATE value with a time zone

displacement value and allow data to be returned in the user's local Mark for Review
session time zone. Which data type should you use? (1) Points

DATETIME
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE (*)

Correct

10.The ELEMENTS column is defined as:

NUMBER(6,4) Mark for Review
(1) Points
How many digits to the right of the decimal point are allowed for the
ELEMENTS column?

Zero
Two
Four (*)
Six

Correc
Test: Final Exam Semester 2 - Part I (11-20)
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct
answer.
Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

Section 8
11.Which statement about data types is true?
Mark for Review
(1) Points

The BFILE data type stores character data up to four gigabytes in

the database.
The TIMESTAMP data type is a character data type.
The VARCHAR2 data type should be used for fixed-length
character data.
The CHAR data type requires that a minimum size be specified
when defining a column of this type. (*)

Correct

12.You are designing a table for the Sales department. You need to
include a column that contains each sales total. Which data type Mark for Review
should you specify for this column? (1) Points

CHAR
DATE
NUMBER (*)
VARCHAR2

Correct

13.Evaluate this CREATE TABLE statement:

CREATE TABLE sales Mark for Review
(sales_id NUMBER, (1) Points
customer_id NUMBER,
employee_id NUMBER,
sale_date TIMESTAMP WITH LOCAL TIME ZONE,
sale_amount NUMBER(7,2));

Which statement about the SALE_DATE column is true?

Data will be normalized to the client time zone.

Data stored will not include seconds.
Data will be stored using a fractional seconds precision of 5.
Data stored in the column will be returned in the database's local
time zone. (*)

Correct

14.You need to truncate the EMPLOYEES table. The EMPLOYEES table

is not in your schema. Which privilege must you have to truncate the Mark for Review
table? (1) Points

The DROP ANY TABLE system privilege (*)

The TRUNCATE ANY TABLE system privilege
 The CREATE ANY TABLE system privilege
The ALTER ANY TABLE system privilege

Correct

15.Which command could you use to quickly remove all data from the
rows in a table without deleting the table itself? Mark for Review
(1) Points

ALTER TABLE
DROP TABLE
MODIFY
TRUNCATE TABLE (*)

Correct

16.Your supervisor has asked you to modify the AMOUNT column in the
ORDERS table. He wants the column to be configured to accept a Mark for Review
default value of 250. The table constains data that you need to keep. (1) Points
Which statement should you issue to accomplish this task?

ALTER TABLE orders

CHANGE DATATYPE amount TO DEFAULT 250;
ALTER TABLE orders
MODIFY (amount DEFAULT 250);

(*)
DROP TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY
KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250);
DELETE TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY
KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250)

Correct

17.Comments on tables and columns can be stored for documentation

by: Mark for Review
(1) Points

Embedding /* comment */ within the definition of the table.

Using the ALTER TABLE CREATE COMMENT syntax
 Using the COMMENT ON TABLE or COMMENT on COLUMN (*)
Using an UPDATE statement on the USER_COMMENTS table

Correct

18.The previous administrator created a table named CONTACTS, which

contains outdated data. You want to remove the table and its data Mark for Review
from the database. Which statement should you issue? (1) Points

DROP TABLE (*)

DELETE
TRUNCATE TABLE
ALTER TABLE

Correct

19.You need to remove all the rows from the SALES_HIST table. You
want to release the storage space, but do not want to remove the Mark for Review
table structure. Which statement should you use? (1) Points

The DROP TABLE statement

The ALTER TABLE statement
The DELETE statement
The TRUNCATE TABLE statement (*)

Correct

20.The EMPLOYEES table contains these columns:

Mark for Review
(1) Points
EMPLOYEE_ID NUMBER(9) Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER(9)
SALARY NUMBER(8,2)

Which statement will permanently remove all the data in the

EMPLOYEES table, but will retain the table's structure and storage
space?

DROP TABLE employees;

DELETE employees; COMMIT; (*)
TRUNCATE TABLE employees;
ALTER TABLE employees SET UNUSED (employee_id,
last_name, first_name, department_id, salary);

Correct
Test: Final Exam Semester 2 - Part I (31-40)
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct
answer.
Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

Section 9

31.Which of the following types of constraints enforces uniqueness?

Mark for Review
(1) Points

CHECK
FOREIGN KEY
PRIMARY KEY (*)
NOT NULL

Correct

32.Which of the following best describes the function of a CHECK

constraint? Mark for Review
(1) Points

A CHECK constraint enforces referential data integrity.

A CHECK constraint defines restrictions on the values that can be
entered in a column or combination of columns. (*)
A CHECK constraint enforces uniqueness of 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.

Correct

33.Evaluate the structure of the DONATIONS table.

Mark for Review
(1) Points
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);
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 CONSTRAINT donor_id_fk REFERENCES
donors(donor_id), pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);

(*)

Correct

34.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

ADD
CHANGE
MODIFY (*)
DISABLE

Correct

35.You can view the columns used in a constraint defined for a specific
table by looking at which data dictionary table? Mark for Review
(1) Points

USER_CONS_COLUMNS (*)
CONSTRAINTS_ALL_COLUMNS
SYS_DATA_DICT_COLUMNS
US_CON_SYS
 Correct

36.You successfully create a table named SALARY in your company's

database. Now, you want to establish a parent/child relationship Mark for Review
between the EMPLOYEES table and the SALARY table by adding a (1) Points
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);

(*)
ALTER TABLE salary
ADD CONSTRAINT fk_employee_id_ FOREIGN KEY
BETWEEN salary (employee_id) AND 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 salary
(employee_id) = employees (employee_id);

Correct

37.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 CASCADE;

(*)
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
DROP CONSTRAINT table_name (constraint_name);
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Correct

38.The DEPARTMENTS table contains these columns:

DEPARTMENT_ID NUMBER, Primary Key Mark for Review
DEPARTMENT_ABBR VARCHAR2(4) (1) Points
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 creates a referential constraint

from the EMPLOYEES table to the DEPARTMENTS table.
The ALTER TABLE statement creates a referential constraint
from the DEPARTMENTS table to the EMPLOYEES table.
The ALTER TABLE statement fails because the ADD
CONSTRAINT clause contains a syntax error. (*)
The ALTER TABLE statement succeeds, but does NOT recreate
a referential constraint.

Correct

39.You need to add a PRIMARY KEY constraint on the EMP_ID column

of the EMPLOYEES table. Which ALTER TABLE statement should Mark for Review
you use? (1) Points

ALTER TABLE employees

ADD CONSTRAINT PRIMARY KEY (emp_id);

(*)
ALTER TABLE
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY
employees(emp_id);
ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;
ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);

Incorrect. Refer to Section 9

40.The LINE_ITEM table contains these columns:

Mark for Review
(1) Points
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;

(*)
ALTER TABLE line_item DROP CONSTRAINT product_id_fk;
ALTER TABLE line_item ENABLE CONSTRAINT product_id_fk;
ALTER TABLE line_item DELETE CONSTRAINT product_id_fk;

Correct
Test: Final Exam Semester 2 - Part I (41-50)
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct
answer.
Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

Section 9

41.Which statement should you use to add a FOREIGN KEY constraint to

the DEPARTMENT_ID column in the EMPLOYEES table to refer to Mark for Review
the DEPARTMENT_ID column in the DEPARTMENTS table? (1) Points

ALTER TABLE employees

MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id);
ALTER TABLE employees
ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id);

(*)
ALTER TABLE employees
ADD FOREIGN KEY CONSTRAINT dept_id_fk ON
(department_id) REFERENCES departments(department_id);
ALTER TABLE employees
ADD FOREIGN KEY departments(department_id)
REFERENCES (department_id);

Correct

42.You need to add a PRIMARY KEY to the DEPARTMENTS table.

Which statement should you use? Mark for Review
(1) Points

ALTER TABLE departments ADD PRIMARY KEY dept_id_pk

(dept_id);
ALTER TABLE departments ADD CONSTRAINT dept_id_pk PK
(dept_id);
ALTER TABLE departments ADD CONSTRAINT dept_id_pk
PRIMARY KEY (dept_id); (*)
ALTER TABLE departments ADD CONSTRAINT PRIMARY KEY
dept_id_pk (dept_id);
 Correct

43.You want to disable the FOREIGN KEY constraint that is defined in

the EMPLOYEES table on the DEPARTMENT_ID column. The Mark for Review
constraint is referenced by the name FK_DEPT_ID_01. Which (1) Points
statement should you issue?

ALTER TABLE employees DISABLE 'fk_dept_id_01';

ALTER TABLE employees DISABLE CONSTRAINT
'fk_dept_id_01';
ALTER TABLE employees DISABLE fk_dept_id_01;
ALTER TABLE employees DISABLE CONSTRAINT
fk_dept_id_01; (*)

Correct

44.What is the highest number of NOT NULL constraints you can have
on a table? Mark for Review
(1) Points

5
10
3
You can have as many NOT NULL constraints as you have
columns in your table. (*)

Correct

45.Which two statements about NOT NULL constraints are true?

(Choose two) Mark for Review
(1) Points

(Choose all correct answers)

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.
The NOT NULL constraint requires that every value in a column
be unique.
Columns without the NOT NULL constraint can contain null
values by default.
You CANNOT add a NOT NULL constraint to an existing column
using the ALTER TABLE ADD CONSTRAINT statement. (*)

Correct

46.You need to add a NOT NULL constraint to the COST column in the
PART table. Which statement should you use to complete this task? Mark for Review
(1) Points
 ALTER TABLE part
MODIFY (cost part_cost_nn NOT NULL);
ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);

(*)
ALTER TABLE part
MODIFY COLUMN (cost part_cost_nn NOT NULL);
ALTER TABLE part
ADD (cost CONSTRAINT part_cost_nn NOT NULL);

Correct

47.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
UNIQUE (*)
NOT NULL
PRIMARY KEY

Correct

48.Primary Key, Foreign Key, Unique Key and Check Constraints can be
added at which two levels? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)

Null Field
Table (*)
Row
Dictionary
Column (*)

Correct

49.You need to ensure that the LAST_NAME column only contains

certain character values. No numbers or special characters are Mark for Review
allowed. Which type of constraint should you define on the (1) Points
LAST_NAME column?

CHECK (*)
UNIQUE
NOT NULL
PRIMARY KEY
 Correct

50.Which statement about constraints is true?

Mark for Review
(1) Points

A single column can have only one constraint applied.

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.

Correct