Section 14 (Answer all questions in this section)

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

2. Which of the following best describes the term "read consistency"?

Mark for Review (1) Points

It ensures that all changes to a table are automatically committed It prevents other users from querying a table while updates are being executed on it It prevents other users from seeing changes to a table until those changes have been committed (*)

It prevents users from querying tables on which they have not been granted SELECT privilege

Correct

Section 16 (Answer all questions in this section)

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

Mark for Review (1) Points

True False (*)

Correct

Section 12 (Answer all questions in this section)

4. The following indexes exist on the EMPLOYEES table: A unique index on the EMPLOYEE_ID primary key column A non-unique index on the JOB_ID column A composite index on the FIRST_NAME and LAST_NAME columns. If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

Mark for Review (1) Points

EMP_ID only JOB_ID only DEPT_ID only EMP_ID and JOB_ID All Indexes (*)

Correct

5. Evaluate this statement: CREATE PUBLIC SYNONYM testing FOR chan.testing; Which task will this statement accomplish?

Mark for Review (1) Points

It recreates the synonym if it already exists. It forces all users to access TESTING using the synonym. It allows only the user CHAN to access TESTING using the synonym. It eliminates the need for all users to qualify TESTING with its schema. (*)

Correct

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

7. The CLIENTS table contains these columns: CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY LAST_NAME VARCHAR2(15) FIRST_NAME VARCHAR2(10) CITY VARCHAR2(15) STATE VARCHAR2(2) You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement: CREATE INDEX clients ON address_index (city, state); Which result does this statement accomplish?

Mark for Review (1) Points

An index named ADDRESS_INDEX is created on the CITY and STATE columns. An index named CLIENTS is created on the CITY and STATE columns. An index named CLIENTS_INDEX is created on the CLIENTS table. An error message is produced, and no index is created. (*)

Correct

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

9. Which statement would you use to remove the EMP_ID_SEQ sequence?

Mark for Review (1) Points

DELETE SEQUENCE emp_id_seq; DROP SEQUENCE emp_id_seq; (*) ALTER SEQUENCE emp_id_seq...; REMOVE SEQUENCE emp_id_seq;

Correct

10.Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?

Mark for Review (1) Points

True False (*)

Correct

Section 15 (Answer all questions in this section)

11.You have the following EMPLOYEES table: EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) ADDRESS VARCHAR2(35) CITY VARCHAR2(25) STATE VARCHAR2(2) ZIP NUMBER(9) TELEPHONE NUMBER(10) DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY The BONUS table includes the following columns: BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY ANNUAL_SALARY NUMBER(10) BONUS_PCT NUMBER(3, 2) EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?

Mark for Review (1) Points

SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id;

(*) SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id; SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct FROM employees, bonus WHERE e.employee_id = b.employee_id; SELECT first_name, last_name, annual_salary * bonus_pct FROM employees, bonus NATURAL JOIN;

Correct

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

Mark for Review (1) Points

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;

Correct

13.Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?

Mark for Review (1) Points

The ON keyword must be included. The JOIN keyword must be included. The FROM clause represents the join criteria. The WHERE clause represents the join criteria. (*)

Correct

14.You need to create a report that lists all employees in department 10 (Sales) Mark for Review whose salary is not equal to $25,000 per year. Which query should you issue (1) Points to accomplish this task?

SELECT last_name, first_name, salary FROM employees WHERE salary > 25000 AND dept_id = 10; SELECT last_name, first_name, salary FROM employees WHERE salary = 25000 AND dept_id = 10; SELECT last_name, first_name, salary FROM employees WHERE salary <= 25000 AND dept_id = 10; SELECT last_name, first_name, salary FROM employees WHERE salary != 25000 AND dept_id = 10; (*)

Correct

15.Which of the following best describes the function of an outer join?

Mark for Review (1) Points

An outer join will return only those rows that do not meet the join criteria. An outer join will return only data from the far left column in one table and the far right column in the other table. An outer join will return data only if both tables contain an identical pair of columns. An outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria. (*)

Correct

16.Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?

Mark for Review (1) Points

AND and = (*) OR and = BETWEEN...AND... and IN IN and =

Correct

17.You need to join the EMPLOYEES table and the SCHEDULES table, but the Mark for Review two tables do not have any corresponding columns. Which type of join will (1) Points you create?

An equijoin A cross join A non-equijoin (*)

A full outer join

Correct

Section 11 (Answer all questions in this section)

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

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

Mark for Review (1) Points

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.

Correct

20.You need to create a view that when queried will display the name, Mark for Review employee identification number, first and last name, salary, and department (1) Points 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?

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

Section 11 (Answer all questions in this section)

21.Which of the following statements is a valid reason for using a view?

Mark for Review (1) Points

Views allow access to the data because the view displays all of the columns from the table. Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*) Views are used when you only want to restrict DML operations using a WITH CHECK OPTION. Views are not valid unless you have more than one user.

Correct

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

Mark for Review (1) Points

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.

Correct

23.You need to create a view on the SALES table, but the SALES table has not

Mark for Review

yet been created. Which statement is true?

(1) Points

You must create the SALES table before creating the view. By default, the view will be created even if the SALES table does not exist. You can create the table and the view at the same time using the FORCE option. You can use the FORCE option to create the view before the SALES table has been created. (*)

Correct

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

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

26.Which option would you use when creating a view to ensure that no DML operations occur on the view?

Mark for Review (1) Points

FORCE NOFORCE WITH READ ONLY (*) WITH ADMIN OPTION

Correct

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

Mark for Review (1) Points

A WHERE clause A join A column alias A GROUP BY clause (*)

Correct

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

Mark for Review

statements will create a view called SALARY_VU based on the employee last (1) Points 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);

Correct

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

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

Section 11 (Answer all questions in this section)

31.Which action can be performed by using DML statements?

Mark for Review (1) Points

Deleting records in a table (*) Creating PRIMARY KEY constraints Disabling an index Altering a table

Correct

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

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

Mark for Review (1) Points

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

Correct

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

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

Mark for Review (1) Points

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. (*) The 25 greatest new balance values were displayed from the highest to the lowest. The statement failed to execute because the ORDER BY does NOT use the Top-n column.

Correct

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

Section 13 (Answer all questions in this section)

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

Mark for Review (1) Points

REGEXP_REPLACE REGEXP_LIKE REGEXP (*) REGEXP_SUBSTR

Correct

38.Parenthesis are not used to identify the sub expressions within the expression. True or False?

Mark for Review (1) Points

True False (*)

Correct

39._________________ are special characters that have a special meaning, Mark for Review such as a wildcard character, a repeating character, a non-matching (1) Points character, or a range of characters. You can use several of these symbols in pattern matching.

Clip Art Meta characters (*) Alphanumeric values Reference checks

Correct

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

Mark for Review (1) Points

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.

Correct

Section 13 (Answer all questions in this section)

41.You need to grant user BOB SELECT privileges on the EMPLOYEES table. You Mark for Review want to allow BOB to grant this privileges to other users. Which statement (1) Points 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;

Correct

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

43.User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?

Mark for Review (1) Points

He is not required to take any action because, by default, all database users can automatically access views. He should assign the SELECT privilege to all database users for the INVENTORY table. He should assign the SELECT privilege to all database users for INVENTORY_V view. (*) He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.

Correct

44.Which keyword would you use to grant an object privilege to all database users?

Mark for Review (1) Points

ADMIN ALL PUBLIC (*) USERS

Correct

45.Which statement would you use to add privileges to a role?

Mark for Review (1) Points

CREATE ROLE

ALTER ROLE GRANT (*) ASSIGN

Correct

46.User ADAM has successfully logged on to the database in the past, but today Mark for Review he receives an error message stating that (although he has entered his (1) Points 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.

Correct

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

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

Mark for Review (1) Points

GRANT customers(SELECT) TO PUBLIC; GRANT SELECT ON customers TO ALL; GRANT SELECT ON customers TO PUBLIC; (*) CREATE PUBLIC SYNONYM customers FOR james.customers;

Correct

49.Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?

Mark for Review (1) Points

ALTER SESSION CREATE SESSION (*) OPEN SESSION RESTRICTED SESSION

Correct

50.You want to grant privileges to user CHAN that will allow CHAN to update Mark for Review the data in the EMPLOYEES table. Which type of privileges will you grant to (1) Points CHAN?

User privileges Object privileges (*)

System privileges Administrator privileges

Correct