Oracle Mid Term Exam Form – Fill Out and Use This PDF

Test: Final Exam Semester 2 - Part II

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

Section 10

1.The FACULTY table contains these columns:

FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY FIRST_NAME VARCHAR2(20)

LAST_NAME VARCHAR2(20) ADDRESS VARCHAR2(35) CITY VARCHAR2(15) STATE VARCHAR2(2)

ZIP NUMBER(9) TELEPHONE NUMBER(10)

STATUS VARCHAR2(2) NOT NULL

The COURSE table contains these columns:

COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY

SUBJECT VARCHAR2(5)

TERM VARCHAR2(6)

FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY

You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?

Mark for Review

(1) Points

CREATE VIEW

(SELECT first_name, last_name, status, courseid, subject, term FROM faculty, course

WHERE facultyid = facultyid);

CREATE VIEW pt_view

ON (SELECT first_name, last_name, status, courseid, subject, term FROM faculty f and course c

WHERE f.facultyid = c.facultyid);

CREATE VIEW pt_view IN	(SELECT first_name, last_name, status,
courseid, subject, term
FROM faculty course);

CREATE VIEW pt_view AS

(SELECT first_name, last_name, status, courseid, subject, term FROM faculty f, course c

WHERE f.facultyid = c.facultyid);

5.Evaluate this CREATE VIEW statement: CREATE VIEW emp_view

AS SELECT SUM(salary) FROM employees;

Which statement is true? Mark for Review

(1) Points

You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*)

You can update any data in the EMPLOYEES table using the EMP_VIEW view.

You can delete records from the EMPLOYEES table using the EMP_VIEW view.

You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.

Correct

6.Evaluate this CREATE VIEW statement: CREATE VIEW pt_view AS

(SELECT first_name, last_name, status, courseid, subject, term FROM faculty f, course c

WHERE f.facultyid = c.facultyid);

Which type of view will this statement create?

Mark for Review

(1)Points Nested

Simple

Inline Complex (*)

Correct

7.Which statement would you use to alter a view? Mark for Review

(1)Points

ALTER VIEW

MODIFY VIEW

ALTER TABLE

CREATE OR REPLACE VIEW (*)

Correct

8.You need to create a view that when queried will display the name, employee identification number, first and last name, salary, and department identification number. When queried, the display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEES table. No DML may be performed when using this view. Evaluate these statements:

CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V

AS SELECT employee_id, last_name, first_name, salary, department_id FROM employees WITH READ ONLY;

SELECT *

FROM emp_salary_v

ORDER BY salary, last_name, first_name;

Which statement is true? Mark for Review

(1) Points

When both statements are executed all of the desired results are achieved.

The CREATE VIEW statement will fail if the EMPLOYEES table does not exist. (*)

The statements will NOT return all of the desired results because the WITH CHECK OPTION clause is NOT included in the CREATE VIEW statement.

To achieve all of the desired results this ORDER ON clause should be added to the CREATE VIEW statement: 'ORDER ON salary, last_name, first_name'

Correct

9.Evaluate this CREATE VIEW statement: CREATE VIEW sales_view

AS SELECT customer_id, region, SUM(sales_amount) FROM sales

WHERE region IN (10, 20, 30, 40) GROUP BY region, customer_id;

Which statement is true?

Mark for Review

(1)Points

You can modify data in the SALES table using the SALES_VIEW view.

You cannot modify data in the SALES table using the SALES_VIEW view.

(*)

You can only insert records into the SALES table using the SALES_VIEW view.

The CREATE VIEW statement generates an error.

Correct

10.An inline view is an unnamed select statement found: Mark for

Review

(1)Points

In the user_views data dictionary view

In a special database column of a users table

Enclosed in parentheses within the select list of a surrounding query

Enclosed in parentheses within the from clause of a surrounding query

(*)

Correct

Page 1 of 5

Test: Final Exam Semester 2 - Part II

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

Section 10

11.You must create a view that when queried will display the name, customer identification number, new balance, finance charge and credit limit of all customers. You issue this statement:

CREATE OR REPLACE VIEW CUST_CREDIT_V

AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit

FROM customers c, accounts a

WHERE c.account_id = a.account_id WITH READ ONLY;

Which type of SQL command can be issued on the CUST_CREDIT_V view? Mark for Review

(1)Points

UPDATE DELETE

INSERT

SELECT (*)

Correct

12.The CUSTOMER_FINANCE table contains these columns: CUSTOMER_ID NUMBER(9)

NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2)

FINANCE_CHARGE NUMBER(7,2) CREDIT_LIMIT NUMBER(7)

You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?

Mark for Review

(1)Points

Inner query: ORDER BY new_balance DESC (*)

Inner query: WHERE ROWNUM = 800

Outer query: ORDER BY new_balance DESC

Inner query: SELECT customer_id, new_balance ROWNUM

Correct

13.Evaluate this SELECT statement:

SELECT ROWNUM "Rank", customer_id, new_balance

FROM (SELECT customer_id, new_balance FROM customer_finance ORDER BY new_balance DESC)

WHERE ROWNUM <= 25; <p> Which type of query is this SELECT statement? Mark for Review

(1)Points

A Top-n query (*)

A complex view

A simple view

A hierarchical view

Correct

14.Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively. Mark for Review

(1)Points

CREATE OR REPLACE VIEW salary_vu

AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"

FROM employees e, departments d, job_grades j

WHERE e.department_id equals d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;

CREATE OR REPLACE VIEW salary_vu

AS SELECT e.empid "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"

FROM employees e, departments d, job_grades j

WHERE e.department_id = d.department_id NOT e.salary BETWEEN j.lowest_sal and j.highest_sal;

CREATE OR REPLACE VIEW salary_vu

AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"

FROM employees e, departments d, job_grades j

WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;

(*)

CREATE OR REPLACE VIEW salary_vu

AS (SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"

FROM employees emp, departments d, job grades j

WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal);

Correct

15.You create a view on the EMPLOYEES and DEPARTMENTS tables to display salary information per department. What will happen if you issue the following statement:

CREATE OR REPLACE VIEW sal_dept

AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d

WHERE e.department_id = d.department_id GROUP BY d.department_name

ORDER BY d.department_name; Mark for Review

(1) Points

A complex view is created that returns the sum of salaries per department, sorted by department name. (*)

A simple view is created that returns the sum of salaries per department, sorted by department name.

A complex view is created that returns the sum of salaries per department, sorted by department id.

Nothing, as the statement constains an error and will fail.

Correct

16.What is the purpose of including the WITH CHECK OPTION clause when creating a view? Mark for Review

(1)Points

To make sure that the parent table(s) actually exist

To keep views form being queried by unauthorized persons To make sure that data is not duplicated in the view

To make sure no rows are updated through the view that will hinder

those rows from being returned by the view. (*)

Correct

17.Which statement about performing DML operations on a view is true? Mark for Review

(1)Points

You can delete data in a view if the view contains the DISTINCT

keyword.

You cannot modify data in a view if the view contains a WHERE clause.

You cannot modify data in a view if the view contains a group function. (*)

You can modify data in a view if the view contains a GROUP BY clause.

Correct

18.You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the

database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE or DELETE operations. Which of the following statements should you issue? Mark for Review

(1) Points

CREATE VIEW sales_view

AS (SELECT companyname, city, orderid, orderdate, total FROM customers, orders

WHERE custid = custid)

WITH READ ONLY;

CREATE VIEW sales_view

(SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o

WHERE c.custid = o.custid)

WITH READ ONLY;

CREATE VIEW sales_view

AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o

WHERE c.custid = o.custid);

CREATE VIEW sales_view

AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o

WHERE c.custid = o.custid)

WITH READ ONLY;

(*)

Correct

19.Which statement about performing DML operations on a view is true? Mark for Review

(1)Points

You can perform DML operations on simple views. (*)

You cannot perform DML operations on a view that contains the WITH

CHECK OPTION clause.

You can perform DML operations on a view that contains the WITH READ ONLY option.

You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.

Correct

20.Which of the following is TRUE regarding simple views? Mark for

Review

(1)Points

They derive data from many tables, so they typically contain joins.

They contain functions or groups of data

They can perform DML operations through the view (*)

They are not stored in the Data Dictionary

Correct

Page 2 of 5

Test: Final Exam Semester 2 - Part II

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

Section 11

21.The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table? Mark for Review

(1)Points

CREATE PUBLIC SYNONYM cust ON mary.customers;

CREATE PUBLIC SYNONYM cust FOR mary.customers;

(*)

CREATE SYNONYM cust ON mary.customers FOR PUBLIC;

CREATE SYNONYM cust ON mary.customers;

GRANT SELECT ON cust TO PUBLIC;

Incorrect. Refer to Section 11

22.The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this? Mark for Review

(1)Points

ALTER INDEX ln_idx ON employees(first_name);

ALTER INDEX ln_idx TO employees(first_name);

ALTER INDEX ln_idx TO fn_idx ON employees(first_name);

None of the above; you cannot ALTER an index. (*)

Incorrect. Refer to Section 11

23.Barry creates a table named INVENTORY. Pam must be able to query the table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create? Mark for Review

(1)Points

A schema

An index

A view

A synonym (*)

Correct

24.The EMPLOYEES table contains these columns: EMP_ID NOT NULL, Primary Key

SSNUM NOT NULL, Unique LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25)

DEPT_ID NUMBER Foreign Key to DEPT_ID column of the DEPARTMENTS table SALARY NUMBER(8,2)

You execute this statement:

CREATE INDEX emp_name_idx

ON employees(last_name, first_name);

Which statement is true? Mark for Review

(1) Points

The statement creates a function-based index.

The statement fails because of a syntax error.

The statement creates a composite unique index.

The statement creates a composite non-unique index. (*)

Correct

25.Which of the following best describes the function of an index? Mark for Review

(1)Points

An index can increase the performance of SQL queries that search

large tables. (*)

An index can reduce the time required to grant multiple privileges to users.

An index can run statement blocks when DML actions occur against a table.

An index can prevent users from viewing certain data in a table.

Correct

26.What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU? Mark for Review

(1)Points

CREATE SYNONYM d_sum

ON dept_sum_vu;

CREATE d_sum SYNONYM

FOR dept_sum_vu;

UPDATE dept_sum_vu

ON SYNONYM d_sum;

CREATE SYNONYM d_sum FOR dept_sum_vu;

(*)

Correct

27.You want to speed up the following query by creating an index: SELECT * FROM employees WHERE (salary * 12) > 100000;

Which of the following will achieve this?

Mark for Review

(1)Points

Create a composite index on (salary,12).

Create a function-based index on (salary * 12). (*)

Create an index on (salary).

Create a function_based index on ((salary * 12) > 100000).

Correct

28.Unique indexes are automatically created on columns that have which two types of constraints? Mark for Review

(1)Points

NOT NULL and UNIQUE

UNIQUE and PRIMARY KEY (*)

UNIQUE and FOREIGN KEY

PRIMARY KEY and FOREIGN KEY

Correct

29.Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table? Mark for Review

(1)Points

DROP INDEX last_name_idx;

(*)

DROP INDEX last_name_idx(last_name);

DROP INDEX last_name_idx(employees.last_name);

ALTER TABLE employees

DROP INDEX last_name_idx;

Correct

30.Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table? Mark for Review

(1) Points

CREATE index_name, table_name, uniqueness

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

SELECT index_name, table_name, uniqueness

FROM 'EMPLOYEES';

SELECT index_name, table_name, uniqueness FROM user_indexes

WHERE table_name = 'EMPLOYEES';

(*)

SELECT index_name, table_name, uniqueness

FROM user_indexes

WHERE index = EMPLOYEES;

Correct

Page 3 of 5

Test: Final Exam Semester 2 - Part II

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

Section 11

31.What is the most common use for a Sequence? Mark for Review

(1)Points

To generate primary key values (*)

To improve the performance of some queries To give an alternative name for an object

To logically represent subsets of data from one or more tables

Correct

32.Evaluate this statement: SELECT po_itemid_seq.CURRVAL FROM dual;

What does this statement accomplish?

Mark for Review

(1)Points

It resets the current value of the PO_ITEM_ID_SEQ sequence.

It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)

It displays the next available value of the PO_ITEM_ID_SEQ sequence.

It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.

Correct

33.Evaluate this CREATE SEQUENCE statement: CREATE SEQUENCE line_item_id_seq CYCLE;

Which statement is true?

Mark for Review

(1)Points

The sequence cannot be used with more than one table.

The sequence preallocates values and retains them in memory.

The sequence cannot generate additional values after reaching its maximum value.

The sequence will continue to generate values after the maximum sequence value has been generated. (*)

Correct

34.You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform? Mark for Review

(1)Points

Do nothing. Oracle automatically generates unique values for columns

that are defined as primary keys.

Specify a UNIQUE constraint on the CUSTOMER_ID column.

Create a synonym.

Create a sequence. (*)

Correct

35.Evaluate this statement: CREATE SEQUENCE line_item_id_seq

MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;

What will be the first five numbers generated by this sequence? Mark for Review

(1)Points

The fifth number cannot be generated.

130120110100130

100110120130100

The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)

Correct

Section 12

36.You want to grant user BOB the ability to change other users' passwords. Which privilege should you grant to BOB? Mark for Review

(1)Points

The ALTER USER privilege (*)

The CREATE USER privilege The DROP USER privilege

The CREATE PROFILE privilege

Correct

37.Which of the following are object privileges? (Choose two) Mark for Review

(1)Points

(Choose all correct answers)

SELECT (*)

DROP TABLE

CREATE TABLE

INSERT (*)

Correct

38.Which of the following are system privileges? (Choose two) Mark for Review

(1)Points

(Choose all correct answers)

CREATE TABLE (*)

UPDATE

CREATE SYNONYM (*)

INDEX

Correct

39.User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem? Mark for Review

(1)Points

One or more object privileges have been REVOKEd from Adam. ADAM's CREATE SESSION privilege has been revoked. (*)

ADAM's CREATE USER privilege has been revoked.

ADAM's user account has been removed from the database.

Incorrect. Refer to Section 12

40.You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute? Mark for Review

(1)Points

CREATE USER jones IDENTIFIED BY mark;

GRANT CREATE TABLE TO jones;

CREATE USER jones IDENTIFIED BY mark;

GRANT CREATE SESSION TO jones;

GRANT CREATE TABLE TO jones;

(*)

GRANT CREATE SESSION TO jones;

GRANT CREATE TABLE TO jones;

CREATE USER jones IDENTIFIED BY mark;

GRANT CREATE SESSION TO jones;

Correct

Page 4 of 5

Test: Final Exam Semester 2 - Part II

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

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

Section 12

41.Evaluate this statement: ALTER USER bob IDENTIFIED BY jim;

Which statement about the result of executing this statement is true? Mark for Review

(1)Points

A new password is assign to user BOB. (*)

A new user JIM is created from user BOB's profile.

The user BOB is assigned the same privileges as user JIM.

The user BOB is renamed and is accessible as user JIM.

Correct

42.You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform? Mark for Review

(1)Points

Do nothing. As a database user, Audrey's user account has

automatically been granted the SELECT privilege for all database objects.

GRANT SELECT ON employees_view TO public;

GRANT SELECT ON employees_view TO audrey; (*)

GRANT SELECT ON employees AND employees_view TO audrey;

Correct

43.Granting an object privilege WITH GRANT OPTION allows the

recipient to grant other object privileges on the table to other users. Mark for Review

(1)Points

True

False (*)

Correct

44.You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use? Mark for Review

(1)Points

GRANT SELECT ON employees TO bob WITH GRANT OPTION; (*)

GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;

GRANT SELECT ON employees TO bob;

GRANT SELECT ON employees TO bob WITH ADMIN OPTION;

Correct

45.Which statement would you use to remove an object privilege granted to a user? Mark for Review

(1)Points

ALTER USER

REVOKE (*)

REMOVE

DROP

Correct

46.User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:

GRANT SELECT ON employees TO mary WITH GRANT OPTION;

Which of the following statements can MARY now execute successfully? (Choose two)

Mark for Review

(1) Points

(Choose all correct answers)

SELECT FROM bob.employees; (*)

REVOKE SELECT ON bob.employees FROM bob;

GRANT SELECT ON bob.employees TO PUBLIC; (*)

DROP TABLE bob.employees;

Correct

47.Which data dictionary view shows which system privileges have been granted to a user? Mark for Review

(1)Points

USER_TAB_PRIVS USER_SYS_PRIVS (*)

USER_SYSTEM_PRIVS

USER_SYSTEM_PRIVILEGES

Correct

48.When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user? Mark for Review

(1)Points

WITH GRANT OPTION (*)

WITH ADMIN OPTION

PUBLIC

FORCE

Correct

Section 14

49.Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:

UPDATE employees

SET salary = salary * 2 WHERE employee_id = 100;

COMMIT;

UPDATE employees

SET salary = 30000

WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

Mark for Review

(1)Points

48000 (*)

30000

24000

78000

Correct

50.Examine the following statements: UPDATE employees SET salary = 15000; SAVEPOINT upd1_done;

UPDATE employees SET salary = 22000; SAVEPOINT upd2_done;

DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

Mark for Review

(1)Points

ROLLBACK;

ROLLBACK TO SAVEPOINT upd1_done; (*)

ROLLBACK TO SAVEPOINT upd2_done; ROLLBACK TO SAVE upd1_done;

There is nothing you can do, either all changes must be rolled back, or none of them can be rolled back.

Correct

Page 5 of 5