Q1. 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 thetablestructure. 

B. It recovers thetablestructure,data,andtheindexes. 

C. It recovers thetablestructure anddatabutnotthe related indexes. 

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

View Answer

Q2. SCOTT is a user in the database. 

Evaluate the commands issued by the DBA: 

1 - CREATE ROLE mgr; 

2 - GRANT CREATE TABLE, SELECT 

ON oe. orders 

TO mgr; 

3 - GRANT mgr, create table TO SCOTT; 

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

A. Statement 1 would not execute because the WITH GRANT option is missing. 

B. Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing. 

C. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement. 

D. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command. 

View Answer

Q3. View the Exhibit and examine the structure of the PRODUCT INFORMATION table. 

Which two queries would work? (Choose two.) 

A. SELECT product_name 

FROM product_information 

WHERE list_price = (SELECT AVG(list_price) 

FROM product_information); 

B. SELECT product_status 

FROM product_information 

GROUP BY product_status 

WHERE list_price < (SELECT AVG(list_price) 

FROM product_information); 

C. SELECT product_status 

FROM product_information 

GROUP BY product_status 

HAVING list_price > (SELECT AVG(list_price) 

FROM product_information); 

D. SELECT product_name FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price) FROM product_jnformation GROUP BY product_status); 

View Answer

Q4. View the Exhibit and examine the details of the EMPLOYEES table. 

Evaluate the following SQL statements: 

Statement 1: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees START WITH employee_id = 101 

CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ; 

Statement 2: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees 

WHERE manager_id != 108 

START WITH employee_id = 101 

CONNECT BY PRIOR employee_id = manager_id; 

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

A. Statement 2 would not execute because theWHEREclause condition is not 

allowed in a statementthathas the START WITH clause. 

B. Theoutput forstatement1 would displaytheemployee with MANAGER_ID108 and 

all the employeesbelowhim or herin thehierarchy. 

C. The output of statement 1 wouldneither display the employee with MANAGER_ID 108 nor any 

employee below him or herinthe hierarchy. 

D. The output for statement 2 would not display theemployee with MANAGER_ID 108 but it 

would display all the employees belowhimor her in the hierarchy. 

View Answer

Q5. View the Exhibit and examine the structure of the EMPLOYEES table. 

Evaluate the following SQL statement: 

SELECT employee_id, last_name, job_id, manager_id 

FROM employees 

START WITH employee_id = 101 

CONNECT BY PRIOR employee_id=manager_id; 

Which statement is true regarding the output for this command? 

A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers. 

B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports. 

C. Itwouldreturn a hierarchical outputstarting withthe employeewhose EMPLOYEE_IDis101, followed by employeesbelowhim orherin thehierarchy. 

D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy. 

View Answer

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

View Answer

Q7. Which three statements are true? (Choose three.) 

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

B. ATIMESTAMP 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. 

View Answer

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

You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101 

Which query would give you the desired output? 

A. SELECT order id, order_date FROM orders 

WHERE order_date > ALL (SELECT MAX(order_date) 

FROM orders)AND 

Customer_id = 101; 

B. SELECT order id, order_date FROM orders 

WHERE order_date > ANY (SELECT order_date 

FROM orders 

WHERE customer_id = 101); 

C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101); 

D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101); 

View Answer

Q9. 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_id 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) 

View Answer

Q10. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? 

A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234; 

B. DELETE FROM empdet WHERE ename LIKE 'J%' C. CREATE VIEWempvu AS SELECT* FROMempdept; 

D. CREATEINDEX empdet_idx ON empdet(empno); 

View Answer