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

Q2. View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: 

SELECT order_id, product_id, unit_price FROM order_jtems WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id); 

You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. 

What correction should be made in the above SQL statement to achieve this? 

A. Replace = with theINoperator. 

B. Replace = withthe >ANYoperator. 

C. Replace = with the>ALLoperator. 

D. Remove the GROUP BY clause from the subquery and placeitin the main query. 

View Answer

Q3. View the Exhibit and examine the structure of the ORDER_ITEMS table. 

You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. 

Which query would produce the desired output? 

A. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = MAX(unit_price*quantity) 

GROUP BY order_id; 

B. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) 

GROUP BY order_id; 

C. SELECT order_id 

FROM order_items 

WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items 

GROUP BY order_id); 

D. SELECT order_id 

FROM order_items 

GROUP BY order_id 

HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)) 

FROM order_items GROUP BY order_id); 

View Answer

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

View Answer

Q5. Evaluate the CREATE TABLE statement: 

CREATE TABLE products 

(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, 

product_name VARCHAR2(15)); 

Which statement is true regarding the PROD_ID_PK constraint? 

A. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

View Answer

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

View Answer

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

The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following 

CREATE TABLE command: 

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) 

AS 

SELECT order_id.order_date.customer_id 

FROM orders; 

Which statement is true regarding the above command? 

A. The NEW_ORDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_ORDERS table would get created and only the NOT NULL constraint definedon the specified columns would bepassed to the new table. 

C. The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match. 

D. The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table. 

View Answer

Q8. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.) 

A. 'os' 

B. local 

C. -8:00' 

D. dbtimezone Li 

E. 'Australia' 

View Answer

Q9. 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. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy. 

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

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

You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. STARTWITH 

E. CONNECT BY PRIOR 

View Answer