Which three statements indicate the end of a transaction? (Choose three.)

A. after a COMMIT is issued

B. after a ROLLBACK is issued

C. after a SAVEPOINT is issued

D. after a SELECT statement is issued

E. after a CREATE statement is issued

Answer: A, B, E

Question: 12

View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

A. UPDATE dept d

SET city = ANY (SELECT city

FROM locations l);

B. UPDATE dept d

SET city = (SELECT city

FROM locations l)

WHERE d.location_id = l.location_id;

C. UPDATE dept d

SET city = (SELECT city

FROM locations l

WHERE d.location_id = l.location_id);

D. UPDATE dept d

SET city = ALL (SELECT city

FROM locations l

WHERE d.location_id = l.location_id);

Answer: C

Question: 13

Evaluate the following DELETE statement: DELETE FROM orders;

There are no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?

A. It removes all the rows in the table and allows ROLLBACK.

B. It would not remove the rows if the table has a primary key.

C. It removes all the rows as well as the structure of the table.

D. It removes all the rows in the table and does not allow ROLLBACK.

Answer: A

Question: 14

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER_ID is the primary key in the ORDERS table.

It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

A. DELETE order_id

FROM orders

WHERE order_total < 1000;

B. DELETE orders

WHERE order_total < 1000;

C. DELETE

FROM orders

WHERE (SELECT order_id

FROM order_items);

D. DELETE orders o, order_items i

WHERE o.order_id = i.order_id;

Answer: B

Question: 15

View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables.

The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.

Which two SQL statements can you execute to accomplish the task? (Choose two.)

A. DELETE

FROM job_history j

WHERE employee_id =

(SELECT employee_id

FROM employees e

WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id

FROM employees e

WHERE j.job_id = e.job_id);

B. DELETE

FROM job_history j

WHERE (employee_id, job_id) = ALL

(SELECT employee_id, job_

FROM employees e

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )

C. DELETE

FROM job_history j

WHERE employee_id =

(SELECT employee_id

FROM employees e

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )

D. DELETE

FROM job_history j

WHERE (employee_id, job_id) =

(SELECT employee_id, job_id

FROM employees e

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )

Answer: C, D

Question: 16

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

Evaluate the following UPDATE statement: UPDATE

(SELECT order_date, order_total, customer_id

FROM orders)

SET order_date = ’22-mar-2007′ WHERE customer_id =

(SELECT customer_id

FROM customers

WHERE cust_last_name = ‘Roberts’ AND

credit_limit = 600);

Which statement is true regarding the execution of the above UPDATE statement?

A. It would not execute because two tables cannot be used in a single UPDATE statement.

B. It would execute and restrict modifications to only the columns specified in the SELECT statement.

C. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

D. It would not execute because the SELECT statement cannot be used in place of the table name.

Answer: B

Question: 17

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.

B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.

D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Answer: D

Question: 65

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

A. It enables users to store the results of a query permanently.

B. It enables users to store the query block permanently in the memory and use it to create complex queries.

C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

D. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user’s temporary tablespace.

Answer: C, D

Question: 66

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.

In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT

value as SYSDATE.

Evaluate the following statement: UPDATE orders

SET order_date=DEFAULT

WHERE order_id IN (SELECT order_id FROM order_items

WHERE qty IS NULL);

What would be the outcome of the above statement?

A. The UPDATE statement would not work because the main query and the subquery use different tables.

B. The UPDATE statement would not work because the DEFAULT value can be used only in

INSERT statements.

C. The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULL and QTY is NULL.

D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL.

Answer: D

Question: 67

The first DROP operation is performed on PRODUCTS table using the following command:

DROP TABLE products PURGE;

Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP;

Which statement describes the outcome of the FLASHBACK command?

A. It recovers only the table structure.

B. It recovers the table structure, data, and the indexes.

C. It recovers the table structure and data but not the related indexes.

D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

Question: 68

Evaluate the following SQL statement: CREATE INDEX upper_name_idx

ON product_information(UPPER(product_name)); Which query would use the UPPER_NAME_IDX index?

D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer: A

Chapter 03: Manipulating Large Data Set

Question: 71

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?

A. external table

B. the MERGE command

C. the multitable INSERT command

D. INSERT using WITH CHECK OPTION

Answer: C

Question: 72

View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.

Which is the best method to load data from the MARKS_DETAILStable to the MARKStable?

A. Pivoting INSERT

B. Unconditional INSERT

C. Conditional ALL INSERT

D. Conditional FIRST INSERT

Answer: A

Question: 73

View the Exhibit and examine the structure of the ORDERS table:

The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.

Evaluate the following statement:

INSERT INTO (SELECT order_id,order_date,customer_id

FROM ORDERS

WHERE order_total = 1000

WITH CHECK OPTION) VALUES (13, SYSDATE, 101);

What would be the outcome of the above INSERT statement?

A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.

B. It would execute successfully and the ORDER_TOTAL column would have the value 1000

inserted automatically in the new row.

C. It would not execute successfully because the ORDER_TOTAL column is not specified in the

SELECT list and no value is provided for it.

D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table.

The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1% of their salary and also a salary increase of 1%. The salary of each employee can be obtained from the EMPLOYEES table.

Which option should be used to perform this task most efficiently?

A. MERGE

B. Unconditional INSERT

C. Conditional ALL INSERT

D. Conditional FIRST INSERT

Answer: A

Question: 77

Which statement is true regarding Flashback Version Query?

A. It returns versions of rows only within a transaction.

B. It can be used in subqueries contained only in a SELECT statement.

C. It will return an error if the undo retention time is less than the lower bound time or SCN

specified.

D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.

Answer: D

Question: 78

View the Exhibit and examine the structure of the ORDERS table.

The columns ORDER_MODE and ORDER_TOTAL have the default values ‘direct’ and 0

The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_ORDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?

A. Pivoting INSERT

B. Unconditional INSERT

C. Conditional ALL INSERT

D. Conditional FIRST INSERT

Answer: C

Question: 80

View the Exhibit and examine the data in the CUST_DET table.

You executed the following multitable INSERT statement: INSERT FIRST

WHEN credit_limit >= 5000 THEN

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THEN

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) WHEN grade = THEN

B. CUST_1 and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied

C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied

D. CUST_1, CUST_2 and CUST_4 tables because CREDIT_LIMIT and GRADE conditions are satisfied for CUST_1 and CUST_2, and CUST_4 has no condition on it

Answer: A

Question: 81

Evaluate the following statement:

INSERT ALL

WHEN order_total < 10000 THEN INTO small_orders

WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders

WHEN order_total > 2000000 THEN INTO large_orders

SELECT order_id, order_total, customer_id

FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT

statement?

A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

D. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

Answer: A

Chapter 04: Generating Reports by Grouping Related Data

Question: 82

View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.

You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT_ID for the last six months.

GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id); View the Exhibit2 and examine the output of the command.

Which two statements are true regarding the output? (Choose two.)

A. The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.

B. The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.

C. The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and

LOCATION_ID columns are taken into account to generate the subtotal.

D. The value 0 in GRP_DEPT, GRP_JOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal.

Answer: B, D

Question: 89

View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.

Evaluate the following SQL statement:

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total

FROM employees e JOIN departments d

ON e.department_id = d.department_id

GROUP BY CUBE (e.department_id, e.job_id, d.location_id);

Which two statements are true regarding the output of this command? (Choose two.)

A. The output would display the total salary for all the departments.

B. The output would display the total salary for all the JOB_IDs in a department.

C. The output would display only the grand total of the salary for all JOB_IDs in a LOCATION_ID.

D. The output would display the grand total of the salary for only the groups specified in the GROUP BY clause.

Answer: A, B

Question: 90

Exam Name

Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?

A. It produces only aggregates for the groups specified in the GROUP BY clause.

B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.

C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.

D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.

Answer: C

Chapter 05: Managing Data in Different Time Zones

Question: 91

Evaluate the following SQL statements in the given order:

DROP TABLE dept; CREATE TABLE dept

(deptno NUMBER(3) PRIMARY KEY, deptname VARCHAR2(10));

DROP TABLE dept;

FLASHBACK TABLE dept TO BEFORE DROP;

Which statement is true regarding the above FLASHBACK operation?

A. It recovers only the first DEPT table.

B. It recovers only the second DEPT table.

C. It does not recover any of the tables because FLASHBACK is not possible in this case. D. It recovers both the tables but the names would be changed to the ones assigned in the

RECYCLEBIN.

Answer: B

Question: 92

Which three statements are true? (Choose three.)

A. Only one LONG column can be used per table.

B. A TIMESTAMP data type column stores only time values with fractional seconds.

C. The BLOB data type column is used to store binary data in an operating system file.

D. The minimum column width that can be specified for a varchar2 data type column is one.

E. The value for a CHAR data type column is blank-padded to the maximum defined column

width.

Answer: A, D, E

Question: 93

Given below is a list of datetime data types and examples of values stored in them in a random order:

Datatype Example

1)INTERVAL YEAR TO MONTH a) ‘2003-04-15 8:00:00 -8:00’

2)TIMESTAMP WITH LOCAL TIME ZONE b) ‘+06 03:30:16.000000′

3)TIMESTAMP WITH TIME ZONE c) ’17-JUN-03 12.00.00.000000 AM’

4)INTERVAL DAY TO SECOND d) ‘+02-00’

Identify the option that correctly matches the data types with the values.

A. 1-d, 2-c, 3-a, 4-b

B. 1-b, 2-a, 3-c, 4-d

C. 1-b, 2-a, 3-d, 4-c

D. 1-d, 2-c, 3-b, 4-a

Answer: A

Question: 94

Evaluate the following query: SELECT INTERVAL ‘300’ MONTH, INTERVAL ’54-2′ YEAR TO MONTH,

INTERVAL ’11:12:10.1234567′ HOUR TO SECOND FROM dual;

What is the correct output of the above query?

A. +25-00 , +54-02, +00 11:12:10.123457

B. +00-300, +54-02, +00 11:12:10.123457

C. +25-00 , +00-650, +00 11:12:10.123457

D. +00-300 , +00-650, +00 11:12:10.123457

Answer: A

Chapter 06: Retrieving Data Using Subqueries

Question: 95

Which two statements is true regarding the execution of the correlated subqueries? (Choose two.)

A. The nested query executes after the outer query returns the row.

B. The nested query executes first and then the outer query executes.

C. The outer query executes only once for the result returned by the inner query.

D. Each row returned by the outer query is evaluated for the results returned by the inner query.

Answer: A, D

Question: 96

Which two statements are true regarding the EXISTS operator used in the correlated subqueries?

(Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found. B. It is used to test whether the values retrieved by the inner query exist in the result of the outer

query.

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: A, C

Question: 97

A non-correlated subquery can be defined as ____.

A. a set of sequential queries, all of which must always return a single value

B. a set of sequential queries, all of which must return values from the same table

C. a SELECT statement that can be embedded in a clause of another SELECT statement only

D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

Answer: D

Question: 98

View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables.

You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written:

Which two statements are true regarding the output of this command? (Choose two.)

A. The output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101. B. The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101. C. The LEVEL column displays the number of employees in the hierarchy under the employee

having the EMPLOYEE_ID 101.

D. The LEVEL column displays the level in the hierarchy at which the employee is placed under the employee having the EMPLOYEE_ID 101.