1z0-047 | The Renew Guide To 1z0-047 practice test


Q121. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. 

In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). 

Which two CREATE INDEX statements are valid? (Choose two.) 

A. CREATE INDEX ord_idx 

ON ord(ord_no); 

B. CREATE INDEX ord_idx 

ON ord_items(ord_no); 

C. CREATE INDEX ord_idx 

ON ord_items(item_no); 

D. CREATEINDEX ord_idx 

ON ord,ord_items(ord_no, ord_date,qty); 

Answer: BC

Q122. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 

Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

Answer: B

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

You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101 

Which query would give you the desired output? 

A. SELECT order id, order_date FROM orders 

WHERE order_date > ALL (SELECT MAX(order_date) 

FROM orders)AND 

Customer_id = 101; 

B. SELECT order id, order_date FROM orders 

WHERE order_date > ANY (SELECT order_date 

FROM orders 

WHERE customer_id = 101); 

C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101); 

D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101); 

Answer: C

Q124. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. 

In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. 

Which view can have all the DML operations performed on it? 

A. CREATE VIEWV1 

ASSELECTorder_id, product_id 

FROMorder_items; 

B. CREATE VIEW V4(or_no, or_date, cust_id) 

ASSELECTorder_id, order_date, customer_id 

FROM orders 

WHEREorder_date < 30-mar-2007' 

WITH CHECK OPTION; 

C. CREATEVIEW V3 

ASSELECT o.order_id,o.customer_id, i.product_id 

FROM orders o, order_items i 

WHERE o.order_id=i.order_id; 

D. CREATE VIEWV2 

AS SELECT order_id, line_item_id, unit_price*quantity total 

FROM order items; 

Answer: B

Q125. 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, department_id, salary+50*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

Q126. Given below are the SQL statements executed in a user session: 

CREATE TABLE product 

(pcode NUMBER(2), 

pnameVARCHAR2(10)); 

INSERT INTO product VALUES(1, ‘pen’); 

INSERT INTO product VALUES (2,'pencil); 

SAVEPOINT a; 

UPDATE product SET pcode = 10 WHERE pcode = 1; 

SAVEPOINT b; 

DELETE FROM product WHERE pcode = 2; 

COMMIT; 

DELETE FROM product WHERE pcode=10; 

ROLLBACK TO SAVEPOINT a; 

Which statement describes the consequences? 

A. No SQL statementwouldberolledback. 

B. Both the DELETE statements would be rolled back. 

C. Only the second DELETE statementwould be rolledback. 

D. Both theDELETE statementsand the UPDATE statement would berolledback. 

Answer: A

Q127. The first DROP operation is performed on PRODUCTS table using the following command: 

DROP TABLE products PURGE; 

Then you performed the FLASHBACK operation by using the following command: 

FLASHBACK TABLE products TO BEFORE DROP; 

Which statement describes the outcome of the FLASHBACK command? 

A. It recovers only thetablestructure. 

B. It recovers thetablestructure,data,andtheindexes. 

C. It recovers thetablestructure anddatabutnotthe related indexes. 

D. It is not possible to recover the table structure, data, or the related indexes. 

Answer: D

Q128. Evaluate the following query: SELECT INTERVAL 300' MONTH, 

INTERVAL 54-2' YEAR TO MONTH, 

INTERVAL '11:12:10.1234567' HOUR TO SECOND 

FROM dual; 

What is the correct output of the above query? 

A. +25-00, +54-02,+00 11:12:10.123457 

B. +00-300, +54-02,+00 11:12:10.123457 

C. +25-00,+00-650,+00 11:12:10.123457 

D. +00-300,+00-650,+0011:12:10.123457 

Answer: A

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

Q130. User OE, the owner of the ORDERS table, issues the following command: 

GRANT SELECT .INSERT ON orders TO hr WITH GRANT OPTION; 

The user HR issues the following command: 

GRANT SELECT ON oe. orders TO scott; 

Then, OE issues the following command: 

REVOKE ALL ON orders FROM hr; 

Which statement is correct? 

A. The user SCOTT losestheprivilege to select rowsfromOE.ORDERS. 

B. Theuser SCOTT retainstheprivilege to selectrowsfrom OE.ORDERS. 

C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT. 

D. TheREVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION. 

Answer: A