Final Exam Oracle Programming - Part 2

(Answer all questions in this section)

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

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

2.            You cannot insert data through a view if the view includes ______.

A WHERE clause

A join

A column alias

A GROUP BY clause (*)

3.            For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?

The view will allow the user to check it against the data dictionary

Prohibits changing rows not returned by the subquery in the view definition. (*)

Prohibits DML actions without administrator CHECK approval

Allows for DELETES from other tables, including ones not listed in subquery

4.            Which statement about performing DML operations on a view is true?

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.

5.            What is the purpose of including the WITH CHECK OPTION clause when creating a view?

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 insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)

6.            You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view can not change salary values in other departments. Which clause should be included in the CREATE VIEW statement?

FORCE

OR REPLACE

WITH READ ONLY

WITH CHECK OPTION (*)

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

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 contains an error and will fail.

8.            Which statement about an inline view is true?

An inline view is a schema object.

An inline view is a subquery in the FROM clause, often named with an alias. (*)

An inline view is a complex view.

An inline view can be used to perform DML operations.

9.            An inline view is an unnamed select statement found:

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

10.        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 execute this statement:

SELECT ROWNUM "Rank", customer_id, new_balance

FROM (SELECT customer_id, new_balance FROM customer_finance)

WHERE ROWNUM <= 25

ORDER BY new_balance DESC;

What statement is true?

The statement failed to execute because an inline view was used.

The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY clause. (*)

The 25 greatest new balance values were displayed from the highest to the lowest.

The statement failed to execute because the ORDER BY clause does NOT use the Top-n column.

11.        Which of the following describes a top-N query?

A top-N query returns the bottom 15 records from the specified table.

A top-N query returns the top 15 records from the specified table.

A top-N query returns a result set that is sorted according to the specified column values.

A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*)

12.        The EMP_HIST_V view is no longer needed. Which statement should you use to the remove this view?

DROP emp_hist_v;

DELETE emp_hist_v;

REMOVE emp_hist_v;

DROP VIEW emp_hist_v; (*)

13.        You need to create a view that will display the name, employee identification number, first and last name, salary, and department identification number. 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?

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

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

Nested

Simple

Inline

Complex (*)

15.        Which statement about the CREATE VIEW statement is true?

A CREATE VIEW statement CAN contain a join query. (*)

A CREATE VIEW statement CANNOT contain an ORDER BY clause.

A CREATE VIEW statement CANNOT contain a function.

A CREATE VIEW statement CANNOT contain a GROUP BY clause.

16.        In order to query a database using a view, which of the following statements applies?

Use special VIEW SELECT keywords.

You can retrieve data from a view as you would from any table. (*)

You can never see all the rows in the table through the view.

The tables you are selecting from can be empty, yet the view still returns the original data from those tables.

17.        Which statement would you use to alter a view?

ALTER VIEW

MODIFY VIEW

ALTER TABLE

CREATE OR REPLACE VIEW (*)

18.        You administer an Oracle database which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first?

Create a report for him.

Create a view. (*)

Create a subquery.

Create an index.

19.        A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?

True

False (*)

20.        Barry creates a table named INVENTORY. Pam must be able to query the same 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?

A schema

An index

A view

A synonym (*)

21.        Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?

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;

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

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;

23.        What is the correct syntax for creating an index?

CREATE INDEX index_name ON table_name(column_name); (*)

CREATE INDEX ON table_name(column_name);

CREATE index_name INDEX ON table_name.column_name;

CREATE OR REPLACE INDEX index_name ON table_name(column_name);

24.        You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task?

CREATE INDEX fl_idx

ON employees(first_name || last_name);

CREATE INDEX fl_idx

ON employees(first_name), employees(last_name);

CREATE INDEX fl_idx

ON employees(first_name,last_name);

(*)

CREATE INDEX fl_idx ON employees(first_name);

CREATE INDEX fl_idx ON employees(last_name);

25.        You need to retrieve the next available value for the SALES_IDX sequence.

Which would you include in your SQL statement?

sales_idx

sales_idx.NEXT

sales_idx.NEXTVAL (*)

sales_idx.CURRVAL

26.        When creating a sequence, which keyword or option specifies the minimum sequence value?

MAXVALUE

MINVALUE (*)

NOMAXVALUE

CYCLE

27.        Parentheses are not used to identify the sub expressions within the expression. True or False?

True

False (*)

28.        Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?

True (*)

False

29.        Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?

REGEXP_REPLACE

REGEXP_LIKE

REGEXP (*)

REGEXP_SUBSTR

30.        Which statement would you use to remove an object privilege granted to a user?

ALTER USER

REVOKE (*)

REMOVE

DROP

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

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;

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

WITH GRANT OPTION (*)

WITH ADMIN OPTION

PUBLIC

FORCE

33.        To join a table in your database to a table on a second (remote) Oracle database, you need to use:

A remote procedure call

An Oracle gateway product

An ODBC driver

A database link (*)

34.        Which of the following simplifies the administration of privileges?

An index

A view

A trigger

A role (*)

35.        Which of the following best describes the purpose of the REFERENCES object privilege on a table?

It allows a user's session to read from the table but only so that foreign key constraints can be checked.

It allows a user to refer to the table in a SELECT statement.

It allows a user to create foreign key constraints on the table. (*)

It allows the user to create new tables which contain the same data as the referenced table.

36.        User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?

SELECT * FROM emp_view FOR rudi;

CREATE SYNONYM emp_view FOR employees;

GRANT SELECT ON emp_view TO rudi; (*)

GRANT SELECT ON emp_view ONLY TO rudi;

37.        The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco?

CREATE ANY TABLE

SELECT

CREATE TABLE (*)

CREATE OBJECT

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

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.

39.        You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?

A system privilege (*)

An object privilege

A user privilege

An access privilege

40.        User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this?

GRANT customers(SELECT) TO PUBLIC;

GRANT SELECT ON customers TO ALL;

GRANT SELECT ON customers TO PUBLIC; (*)

CREATE PUBLIC SYNONYM customers FOR james.customers;

41.        Which SQL statement is used to remove all the changes made by an uncommitted transaction?

UNDO;

ROLLBACK; (*)

ROLLBACK TO SAVEPOINT;

REVOKEﾅ;

42.        A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?

An update statement

A savepoint (*)

An object privilege

A database link

A sequence

43.        Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?

(+) (*)

*

+

=

44.        Evaluate this SELECT statement:

SELECT p.player_id, m.last_name, m.first_name, t.team_name

FROM player p

LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)

LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

Which join is evaluated first?

The self-join of the player table (*)

The join between the player table and the team table on TEAM_ID

The join between the player table and the team table on MANAGER_ID

The join between the player table and the team table on PLAYER_ID

45.        You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?

An equijoin

It is not possible to join these two tables.

A non-equijoin (*)

A full outer join

46.        The PATIENTS and DOCTORS tables contain these columns:

PATIENTS

PATIENT_ID NUMBER(9)

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

DOCTORS

DOCTOR_ID NUMBER(9)

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

You issue this statement:

SELECT patient_id, doctor_id

FROM patients, doctors;

Which result will this statement provide?

A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values (*)

A report containing each patient's id value and his doctor's id value

A report with NO duplicate PATIENT_ID or DOCTOR_ID values

A syntax error

47.        What is the minimum number of join conditions required to join 5 tables together?

3

4 (*)

5

One more than the number of tables

48.        Evaluate this SQL statement:

SELECT e.employee_id, e.last_name, e.first_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id AND employees.department_id > 5000

ORDER BY 4;

Which clause contains a syntax error?

SELECT e.employee_id, e.last_name, e.first_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND employees.department_id > 5000 (*)

ORDER BY 4;

49.        What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?

A self-join

An outer join

An equijoin

A Cartesian product (*)

50.        Unit testing is done prior to a database going into production to ensure a random number of business requirements functions properly. True or False?

True

False (*)