Q1. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount" 

FROM orde_items oi JOIN orders o 

ON oi.order_id = o.order_id 

GROUP BY CUBE (o.customer_id, oi.product_id); 

Which three statements are true regarding the output of this SQL statement? (Choose three.) 

A. It would return the subtotals for the Order Amount of every CUSTOMER_ID. 

B. It would return the subtotals for the Order Amount for every PRODUCT_ID. 

C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. 

D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

View Answer

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

View Answer

Q3. 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. Itremovesallthe rows in thetable andallows ROLLBACK 

B. It would not removetherows if thetablehasaprimary key. 

C. It removesallthe rows as well as the structure of the table. 

D. Itremoves all the rows inthetable and doesnotallow ROLLBACK 

View Answer

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

You executed the following SQL statement: 

SELECT first_name, department_id, salary 

FROM employees 

ORDER BY department_id, first_name, salary desc; 

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

A. The valuesinall the columns wouldbe sortedin the descending order. 

B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column. 

C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column. 

D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column. 

E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column. 

View Answer

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

You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied 

by QUANTITY), where the total price is greater than 50,000. 

You executed the following SQL statement: 

SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE 

unit_price*quantity > 50000 NATURAL JOIN orders; Which statement is true regarding the execution of the statement? 

A. The statement would execute and provide the desired result. 

B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause. 

C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause. 

D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause. 

View Answer

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

You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100. 

Which SQL clauses would you require to accomplish the task? (Choose all that apply.) 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY 

View Answer

Q7. Which two statements are true regarding subqueries? (Choose two.) 

A. Only two subqueries can be placed at one level. 

B. A subquery can be used to access data from one or more tables or views. 

C. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. 

View Answer

Q8. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

View Answer

Q9. ORD is a private synonym for the OE.ORDERS table. 

The user OE issues the following command: 

DROP SYNONYM ord; 

Which statement is true regarding the above SOL statement? 

A. Onlythesynonymwould be dropped. 

B. The synonym would be dropped and the corresponding table would become invalid. 

C. The synonym would be dropped and the packages referring to the synonym would be dropped. 

D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid. 

View Answer

Q10. Evaluate the following SQL statements that are issued in the given order: 

CREATE TABLE emp 

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, 

enameVARCHAR2(15), 

salary NUMBER(8,2), 

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp); 

ALTER TABLE emp 

DISABLE CONSTRAINT emp_emp_no_pk CASCADE; 

ALTER TABLE emp 

ENABLE CONSTRAINT emp_emp_no_pk; 

What would be the status of the foreign key EMP_MGR_FK? 

A. It wouldbeautomatically enabled and deferred. 

B. It wouldbeautomaticallyenabledand immediate. 

C. Itwouldremaindisabled and has to beenabled manuallyusingthe ALTER TABLEcommand. 

D. Itwould remain disabled and can be enabled only by droppingtheforeign key constraint andre-creating it. 

View Answer