1z0-047 exam details (161 to 170)

Pinpoint of 1z0-047 practice materials and interactive bootcamp for Oracle certification for IT professionals, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!

2016 Aug 1z0-047 dump:

Q161. View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables. 

The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table. 


Which two SQL statements can you execute to accomplish the task? (Choose two.) 

A. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e WHERE j.job_id = e.job_id); 

B. DELETE FROM job_history j WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

C. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

D. DELETE FROM job_history j WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id) 

Answer: CD


Q162. Which SQL statement would display the view names and definitions of all the views owned by you? 

A. SELECTviewjiame, text FROM user_view; 

B. SELECTviewjiame, text FROM user_object; 

C. SELECTviewjiame, text FROM user_objects; 

D. SELECTview_name, text FROM user views; 

Answer: D


Q163. View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables. 

You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days. 

Which query would you execute? 


A. SELECT orde_id, product_id, unit_price*quantity "TOTAL" 

FROM order_items oi JOIN orders o 

ON (o.order_id=oi. order_id) 

WHERE o.order_date>=SYSDATE-7; 


B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM order_items oi JOIN orders o 

USING (order_id) 

WHERE o.order_date>=SYSDATE-7; 


C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM order_items oi JOIN orders o 


WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id); 

D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM orde_items oi JOIN orders o 

ON (o.order_id=oi. order_id) 

WHERE o. order date>=SYSDATE-7; 


Answer: D


Q164. View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'. 

Which SQL statement would give you the desired result? 


A. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena' 

B. SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena' 

C. SELECT first_name, salary FROM employees WHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena' 

D. SELECT first_name, salaryFROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena' 

Answer: D


Q165. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.) 

A. 'os' 

B. local 

C. -8:00' 

D. dbtimezone Li 

E. 'Australia' 

Answer: BCD


1z0-047 test preparation

Renovate 1z0-047 dumps pdf free download:

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


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

Answer: CE


Q168. You executed the following SQL statements in the given order: 

CREATE TABLE orders 

(order_id NUMBER(3) PRIMARY KEY, 

order_date DATE, 

customer_id number(3)); 

INSERT INTO orders VALUES (100,'10-mar-2007,,222); 

ALTER TABLE orders MODIFY order_date NOT NULL; 

UPDATE orders SET customer_id=333; 

DELETE FROM order; 

The DELETE statement results in the following error: 

ERROR at line 1: 

ORA-00942: table or view does not exist 

What would be the outcome? 

A. All the statements before the DELETEstatementwouldberolled back. 

B. Allthestatements before theDELETEstatement wouldbeimplicitly committedwithin thesession. 

C. Allthe statements up to theALTER TABLEstatement would be committedandthe outcomeof UPDATEstatementwouldberolled back. 

D. All the statements up to theALTERTABLEstatementwould be committed and the outcome oftheUPDATE statement is retained uncommittedwithin thesession. 

Answer: D


Q169. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. 



You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. 


Which CREATE VIEW statement would create the view successfully? 

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


B. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


C. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

FROM orders o JOIN order_items i ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 


D. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS' 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date 

WITH CHECK OPTION; 


Answer: B


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



see more 1z0-047 dumps