It is more faster and easier to pass the Oracle oracle database sql expert 1z0 047 exam by using Precise Oracle Oracle Database SQL Expert questuins and answers. Immediate access to the Abreast of the times 1z0 047 pdf Exam and find the same core area 1z0 047 dumps questions with professionally verified answers, then PASS your exam with a high score now.


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1z0-047-exam-dumps.html

Q121. 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 order_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. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID. 

B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID. 

C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup. 

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

E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup. 

Answer: ABD


Q122. 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_DRDERS 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. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: C


Q123. Which three statements are true regarding single-row functions? (Choose three.) 

A. They can accept only one argument. 

B. They can be nested up to only two levels. 

C. They can return multiple values of more than one data type. 

D. They can be used in SELECT, WHERE, and ORDER BY clauses. 

E. They can modify the data type of the argument that is referenced. 

F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. 

Answer: DEF


Q124. 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 areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. 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 isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

Answer: A


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


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

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. 

Which SOL statement would you execute to get the desired output? 

A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total" 

FROM EMPLOYEES; 

B. SELECTfirst_name,salary, salary*12+NVL((salary*commission_pct), 0) "Total" 

FROMEMPLOYEES; 

C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total" 

FROM EMPLOYEES; 

D. SELECTfirst_name, salary, salary*12+(salary*NVL2(commission_pct, 

salary,salary+commission_pct))"Total" 

FROM EMPLOYEES; 

Answer: B


Q127. Given below is a list of functions and the tasks performed by using these functions, in random order. 

Function Usage 

1) LPAD a) Used to truncate a column, expression, or value to n decimal places 2) TRUNC b) Used to remove heading or trailing or both characters from the character string 3) DECODE c) Pads the character value right-justified to a total width of n character positions 4) TRIM d) Used to return the numeric value for position of a named character from the character string 5) INSTR e) Used to translate an expression after comparing it with each search value 

Which option correctly matches the function names with their usage? 

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

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

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

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

Answer: D


Q128. View the Exhibit and examine the description of the ORDERS table. 

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.) 

A. WHERE order_date> TO_DATE('JUL 10 2006','MON DD YYYY) 

B. WHERE TO_CHAR(order_date,'MONDDYYYY) = 'JAN 20 2003' 

C. WHEREorder_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY") 

D. WHERE order_dateIN(TO_DATE('Oct 21 2003','Mon DD YYYY"),TO_CHAR('NOV21 2003','Mon DD YYYY")) 

Answer: AB


Q129. Evaluate the following CREATE TABLE command: 

CREATE TABLE order_item 

(order_id NUMBER(3), 

item_id NUMBER(2), 

qty NUMBER(4), 

CONSTRAINT ord_itm_id_pk 

PRIMARY KEY (order_id item_id) 

USING INDEX 

(CREATE INDEX ord_itm_idx 

ON order_item(order_id,item_id))); 

Which statement is true regarding the above SOL statement? 

A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created. 

B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey. 

C. It wouldexecutesuccessfully and two indexesORD_ITM_IDXand ORD_ITM_ID_PKwould becreated. 

D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand. 

Answer: A


Q130. View the Exhibit and examine the details for the CATEGORIES_TAB table. 

Evaluate the following incomplete SQL statement: 

SELECT category_name ,category_description 

FROM categories_tab 

You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. 

Which two WHERE clause options can give you the desired result? (Choose two.) 

A. WHEREREGEXP_LIKE(category_description, 'hard+.s’); 

B. WHERE REGEXP_LIKE(category_description,‘^H|hard+.s’); 

C. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$'); 

D. WHEREREGEXP_LIKE (category_description, '[^Hlhard+.s]'); 

Answer: AB