1z0-047 | All About Best Quality 1z0-047 pdf

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

Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary: 

SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5; 

When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly? 

A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation". 

B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation". 

C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation". 

D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation". 

Answer: C

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

Evaluate the following SQL statement: SELECT employee_id, last_name, jobjd, manager_id, LEVEL FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id; 

Which two statements are true regarding the output of this command? (Choose two.) 

A. Theoutputwouldbeintop-downhierarchy starting with EMPLOYEE_ID having value 101. 

B. Theoutput wouldbein bottom-up hierarchystartingwith EMPLOYEE_ID havingvalue101. 

C. TheLEVEL columndisplaysthenumber of employees in the hierarchy under the employee having theEMPLOYEE_ID 101. 

D. The LEVEL column displays the level in the hierarchy at which the employee is placed undertheemployee having the EMPLOYEE_ID 101 

Answer: AD

Q143. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. 

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: 

SELECT p.product_name, i.item_cnt 

FROM (SELECT product_id, COUNT (*) item_cnt 

FROM order_items 

GROUP BY product_id) i RIGHT OUTER JOIN products p 

ON i.product_id = p.product_id; 

What would happen when the above statement is executed? 

A. The statement would execute successfully to produce the required output. 

B. The statement would not execute because inline views and outer joins cannot be used together. 

C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query. 

D. The statement would not execute because the GROUP BY clause cannot be used in the inline view. 

Answer: A

Q144. View the Exhibit and examine the structure of the ORDERS and ORDERJTEMS tables. 

Evaluate the following SQL statement: 

SELECT oi.order_id, product_jd, order_date 

FROM order_items oi JOIN orders o 

USING(order_id); 

Which statement is true regarding the execution of this SQL statement? 

A. The statement would not execute because table aliases are not allowed in the JOIN clause. 

B. The statement would not execute because the table alias prefix is not used in the USING clause. 

C. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases. 

D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list. 

Answer: D

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

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

A. 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; 

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

Q146. 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 jtem_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 indexes ORD_ITM_IDXand ORD_ITM_ID_PKwould becreated. 

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

Answer: A

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

Q148. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.) 

A. It can be used only with the SELECT clause. 

B. The WITH clause can hold more than one query. 

C. If the query block name and the table name were the same, then the table name would take precedence. 

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 

Answer: ABD

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

Answer: AC

Q150. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.) 

A. It can be used only with the SELECT clause. 

B. The WITH clause can hold more than one query. 

C. If the query block name and the table name were the same, then the table name would take precedence. 

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 

Answer: ABD