Exam Code: 1z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-047 Exam.

2016 Sep 1z0-047 study material:

Q71. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement? 

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

B. It produces only the grand totals for the groups specified in the GROUP BY clause. 

C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause. 

D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. 

Answer: C


Q72. 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 firstDEPTtable. 

B. Itrecovers onlythesecondDEPTtable. 

C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case. 

D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN. 

Answer: B


Q73. View the Exhibit and examine the description of the ORDER_ITEMS table. 


The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a UNIT_PRICE of more than 1,000 and has been ordered more than five times: 

SELECT product_id, COUNT(order_id) total, unit_price FROM order_items WHERE unit_price>1000 AND COUNT(order_id)>5 GROUP BY product_id, unit_price; 

Which statement is true regarding this SQL statement? 

A. The statementwouldexecute and give youthedesired result. 

B. Thestatementwould not execute becausetheaggregatefunction is usedintheWHEREclause. 

C. The statementwouldnot execute because the WHERE clause should havetheOR logical operator insteadofAND. 

D. The statement wouldnot execute because intheSELECT clause, the UNIT_PRICE column is placed afterthecolumn having the aggregate function. 

Answer: B


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

A. SELECT o.customer_Id, oi.productj_id, SUM(oi.unit_price*oi. quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id.oi.product_id) 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6; 


B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total" 

FROM orderjtems oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id.oi.product_id) 

HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6; 


C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id, oi.product_id) 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6; 


D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 

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


Answer: D


Q75. 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 = THE 

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) 

WHEN gender = THE 

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) 

INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) 

ELSE 

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) 

SELECT * FROM cust_det; 

The row will be inserted in________. 

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied 

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


certifyforall.com

Abreast of the times 1z0-047 exam fee:

Q76. 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 SOL statement would you execute to accomplish the task? 


A. UPDATE deptd 

SETcity=ANY(SELECT city 

FROMlocations I); 


B. UPDATE deptd 

SET city=(SELECT city 

FROM locations I) 

WHERE d.location_id = l.location_id; 


C. UPDATE dept d 

SETcity =(SELECTcity 

FROM locations I 


WHEREd.location_id = l.location_id); 

D. UPDATE deptd SET city =ALL(SELECT city FROM locations I WHEREd.location_id = l.location_id); 

Answer: C


Q77. View the Exhibit and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables. 


The following SQL statement was executed: 

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

GROUPING(e. department_id) GRP_DEPT, 

GROUPING(e.job_id) GRPJOB, 

GROUPING(d. location_id) GRP_LOC 

FROM employees e JOIN departments d 

ON e.department_id = d.department_id 

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 value1in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal. 

B. The value 1 in GRPJOBandGRP_LOC meansthatJOB_IDandLOCATION_ID columns are nottakeninto accountto generatethesubtotal. 

C. Thevalue1inGRPJOB and GRP_LOC meansthatthe NULL value in JOBJD and LOCATIONJD columns aretakeninto account togeneratethe subtotal. 

D. The value 0 inGRP_DEPT,GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columnsaretaken into account to generate the subtotal 

Answer: BD


Q78. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. 

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom. 

Which SQL statement would you execute to fulfill this requirement? 


A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period; 

B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period; 

C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE; 

D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM product_information WHERE warranty_period >SYSDATE; 

Answer: B


Q79. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? 

A. to find the groups forming the subtotal in a row 

B. to create group-wise grand totals for the groups specified within a GROUP BY clause 

C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals 

D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals 

Answer: C


Q80. Which two statements are true regarding constraints? (Choose two.) 

A. A foreign key cannot contain NULL values. 

B. A column with the UNIQUE constraint can contain NULL. 

C. A constraint is enforced only for the INSERT operation on a table. 

D. A constraint can be disabled even if the constraint column contains data. 

E. All the constraints can be defined at the column level as well as the table level 

Answer: BD