Test: Final Exam Semester 2 - Part I

Test: Final Exam Semester 2 - Part I



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 10
(Answer all questions in this section)

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


2. You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true? Mark for Review
(1) Points

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.

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


Correct


3. Which clause could you use to ensure that cost values are greater than 1.00? Mark for Review
(1) Points

CONSTRAINT CHECK cost > 1.00

CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)

CHECK CONSTRAINT part_cost_ck (cost > 1.00)

CONSTRAINT CHECK part_cost_ck (cost > 1.00)


Incorrect. Refer to Section 10 Lesson 2.


4. You need to create the PROJECT_HIST table. The table must meet these requirements:

The table must contain the EMPLOYEE_ID and TASKED_HOURS columns for numeric data.
The table must contain the START_DATE and END_DATE column for date values.
The table must contain the HOURLY_RATE and PROJECT_COST columns for numeric data with precision and scale of 5,2 and 10,2 respectively.
The table must have a composite primary key on the EMPLOYEE_ID and START_DATE columns.
Evaluate this CREATE TABLE statement:

CREATE TABLE project_hist
( employee_id NUMBER,
start_date DATE,
end_date DATE,
tasked_hours NUMBER,
hourly_rate NUMBER(5,2),
project_cost NUMBER(10,2),
CONSTRAINT project_hist_pk PRIMARY KEY(employee_id, start_date));

How many of the requirements does the CREATE TABLE statement satisfy?
Mark for Review
(1) Points

None of the four requirements

All four of the requirements (*)

Only three of the requirements

Only two of the requirements


Correct


5. How many PRIMARY KEY constraints can be created for each table? Mark for Review
(1) Points

None

One and only one (*)

One or two

Unlimited


Correct


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


7. Which statement about a FOREIGN KEY constraint is true? Mark for Review
(1) Points

An index is automatically created for a FOREIGN KEY constraint.

A FOREIGN KEY constraint requires the constrained column to contain values that exist in the referenced Primary or Unique key column of the parent table. (*)

A FOREIGN KEY constraint allows that a list of allowed values be checked before a value can be added to the constrained column.

A FOREIGN KEY column can have a different data type from the primary key column that it references.


Correct


8. Which type of constraint by default requires that a column be both unique and not null? Mark for Review
(1) Points

FOREIGN KEY

PRIMARY KEY (*)

UNIQUE

CHECK


Correct


9. When creating a referential constraint, which keyword(s) identifies the table and column in the parent table? Mark for Review
(1) Points

FOREIGN KEY

REFERENCES (*)

ON DELETE CASCADE

ON DELETE SET NULL


Correct


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



Page 1 of 5






Test: Final Exam Semester 2 - Part I



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 10
(Answer all questions in this section)

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

FOREIGN KEY

REFERENCES

CASCADE (*)

ON DELETE SET NULL


Correct


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



Incorrect. Refer to Section 10 Lesson 3.


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


14. Which of the following would definitely cause an integrity constraint error? Mark for Review
(1) Points

Using a subquery in an INSERT statement.

Using the MERGE statement to conditionally insert or update rows.

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.


Correct


15. 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 fk_dept_id_01;


ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)



Correct


16. What actions can be performed on or with Constraints? Mark for Review
(1) Points

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

Add, Minus, Enable, Disable, Collapse

Add, Subtract, Enable, Cascade

Add, Drop, Disable, Disregard


Correct


17. 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?
Mark for Review
(1) Points

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


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


Correct


19. 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 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 NOT execute because it contains a syntax error. (*)


Correct


20. Which constraint can only be created at the column level? Mark for Review
(1) Points

NOT NULL (*)

FOREIGN KEY

UNIQUE

CHECK


Correct



Page 2 of 5






Test: Final Exam Semester 2 - Part I



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 10
(Answer all questions in this section)

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


22. Which statement about the NOT NULL constraint is true? Mark for Review
(1) Points

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

The NOT NULL constraint can be defined at either the column level or the table level.

The NOT NULL constraint requires a column to contain alphanumeric values.

The NOT NULL constraint prevents a column from containing alphanumeric values.


Correct


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

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.

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


Correct


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


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

UNIQUE

NOT NULL

PRIMARY KEY


Correct


26. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points

True

False (*)


Correct




Section 8
(Answer all questions in this section)

27. Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));

You are a member of the SYSDBA role, but are not logged in as SYSDBA. You issue this CREATE TABLE statement. Which statement is true?
Mark for Review
(1) Points

You created the LINE_ITEM table in the public schema.

You created the LINE_ITEM table in the SYS schema.

You created the table in your schema. (*)

You created the table in the SYSDBA schema.


Correct


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

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


29. You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create? Mark for Review
(1) Points

2001_PRODUCTS

PRODUCTS_2001 (*)

PRODUCTS_(2001)

PRODUCTS--2001


Correct


30. Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth? Mark for Review
(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



Page 3 of 5






Test: Final Exam Semester 2 - Part I



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8
(Answer all questions in this section)

31. Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns. Mark for Review
(1) Points

CREATE TABLE employee
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;


CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);


CREATE TABLE emp
SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);


CREATE TABLE emp
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;
(*)



Correct


32. Which column name is valid? Mark for Review
(1) Points

1NUMBER

NUMBER

NUMBER_1$ (*)

1_NUMBER#


Correct


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

CHAR

DATE (*)

TIMESTAMP

INTERVAL YEAR TO MONTH


Incorrect. Refer to Section 8 Lesson 2.


34. Evaluate this CREATE TABLE statement:
CREATE TABLE sales
(sales_id NUMBER,
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?
Mark for Review
(1) Points

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


35. A column that will be used to store binary data up to 4 Gigabyes in size should be defined as which datatype? Mark for Review
(1) Points

LONG

NUMBER

BLOB (*)

LONGRAW


Correct


36. To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
(1) Points

DATE

INTERVAL YEAR TO MONTH

TIMESTAMP (*)

INTERVAL DAY TO SECOND


Correct


37. Which data types stores variable-length character data? Select two. Mark for Review
(1) Points

(Choose all correct answers)

CHAR

NCHAR

CLOB (*)

VARCHAR2 (*)


Correct


38. 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 session time zone. Which data type should you use? Mark for Review
(1) Points

DATETIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE (*)


Correct


39. The ELEMENTS column is defined as:
NUMBER(6,4)
How many digits to the right of the decimal point are allowed for the ELEMENTS column?
Mark for Review
(1) Points

Zero

Two

Four (*)

Six


Correct


40. The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue? Mark for Review
(1) Points

DROP TABLE (*)

DELETE

TRUNCATE TABLE

ALTER TABLE


Incorrect. Refer to Section 8 Lesson 3.



Page 4 of 5






Test: Final Exam Semester 2 - Part I



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8
(Answer all questions in this section)

41. 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 table structure. Which statement should you use? Mark for Review
(1) Points

The DROP TABLE statement

The ALTER TABLE statement

The DELETE statement

The TRUNCATE TABLE statement (*)


Correct


42. Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)

The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?
Mark for Review
(1) Points

ALTER TABLE employee
MODIFY (employee_id VARCHAR2(9));


ALTER TABLE employee
REPLACE (employee_id VARCHAR2(9));


ALTER employee TABLE
MODIFY COLUMN (employee_id VARCHAR2(15));


You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty. (*)


Incorrect. Refer to Section 8 Lesson 3.


43. Which statement about a column is NOT true? Mark for Review
(1) Points

You can increase the width of a CHAR column.

You can modify the data type of a column if the column contains non-null data. (*)

You can convert a CHAR data type column to the VARCHAR2 data type.

You can convert a DATE date type column to a VARCHAR2 column.


Incorrect. Refer to Section 8 Lesson 3.


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

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)



Incorrect. Refer to Section 8 Lesson 3.


45. You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use? Mark for Review
(1) Points

RENAME employees emp;

RENAME employees TO emp; (*)

ALTER TABLE employees TO emp;

ALTER TABLE employees RENAME TO emp;


Correct


46. Evaluate this statement:
TRUNCATE TABLE employees;
Which statement about this TRUNCATE TABLE statement is true?
Mark for Review
(1) Points

You can produce the same results by issuing the 'DROP TABLE employee' statement.

You can issue this statement to retain the structure of the employees table. (*)

You can reverse this statement by issuing the ROLLBACK statement.

You can produce the same results by issuing the 'DELETE employees' statement.


Correct


47. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
TEAM_ID NUMBER(4)
SALARY NUMBER(9,2)

Which statement should you use to decrease the width of the FIRST_NAME column to 10 if the column currently contains 1500 records, but none are longer than 10 bytes or characters?
Mark for Review
(1) Points

ALTER players TABLE
MODIFY COLUMN first_name VARCHAR2(10);


ALTER players TABLE
MODIFY COLUMN (first_name VARCHAR2(10));


ALTER TABLE players
RENAME first_name VARCHAR2(10);


ALTER TABLE players
MODIFY (first_name VARCHAR2(10));
(*)


Incorrect. Refer to Section 8 Lesson 3.


48. To do a logical delete of a column without the performance penalty of rewriting all the table datablocks you can issue the following command: Mark for Review
(1) Points

Alter table modify column

Alter table drop column

Alter table set unused (*)

Drop column ?columname?


Correct


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


50. The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)

The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
Mark for Review
(1) Points

ALTER teams
MODIFY (mgr_id VARCHAR2(15));


ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)


ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));


ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));


You CANNOT modify the data type of the MGR_ID column.


Incorrect. Refer to Section 8 Lesson 3.



Page 5 of 5