[Up to date] 1z0-047 dumps free download

Download of 1z0-047 exam topics materials and free demo for Oracle certification for examinee, Real Success Guaranteed with Updated 1z0-047 pdf dumps vce Materials. 100% PASS Oracle Database SQL Expert exam Today!

2016 Aug oracle 1z0-047 book:

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

You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: 

SELECT employee_id, first_name, department_id 

FROM employees 

WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) 

AND employee_id <> 104; 


When you execute the statement it does not produce the desired output. What is the reason for this? 

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS. 

B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator. 

C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator. 

D. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order. 

Answer: D


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

The ORDERS table belongs to the user OE. HR is another user in the database. 

Evaluate the commands issued by users OE and HR in the following order: 

Statement 1 by user OE: GRANT SELECT, 

UPDATE(customer_id, order_total) 

ON orders 

TOhr; 

Statement 1 by user HR: SELECT * FROM oe.orders; 

Statement 2 by user HR: UPDATE oe.orders 

SET order_totah 10000; 


Which statement is true regarding the above commands? 



A. Statement 1 by user OE would not work because the statement has to be issued by the DBA. 

B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update. 

C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully. 

D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns. 

Answer: C


Q123. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement? 

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

B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. 

C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

Answer: C


Q124. 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,'MON DD YYYY} = 'JAN 20 2003' 

C. WHERE order_date > T0_CHAR(ADD_M0NTHS(SYSDATE,6),'M0N DD YYYY’) 

D. WHERE order_date IN (T0_DATE('0ct 21 2003','Mon DD YYYY’), T0_CHAR('N0V 21 2003','Mon DD YYYY’)) 

Answer: AB


Q125. View the Exhibit and examine the structure of the EMP table. 

You executed the following command to add a primary key to the EMP table: 

ALTER TABLE emp 

ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) 

USING INDEX emp_id_idx; 


Which statement is true regarding the effect of the command? 

A. The PRIMARY KEY is created along with a new index. 

B. The PRIMARY KEY is created and it would use an existing unique index. 

C. The PRIMARY KEY would be created in a disabled state because it is using an existing index. 

D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command. 

Answer: B


1z0-047 exam question

Up to date oracle 1z0-047 dumps free download:

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


Evaluate the following SQL statement: 

SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; 

The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. 

The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session. 

Which statement is true regarding this query? 

A. The querywouldexecutetogive the desired output. 

B. Thequerywouldnot execute becausedatefunctions cannot be nested. 

C. Thequerywould executebuttheoutputwouldgive reviewdates that are Sundays. 

D. Thequery would not execute becausetheNEXT_DAY function acceptsastring as argument. 

Answer: C


Q127. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT oi.order_id, product_id, 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 statementwouldnot execute because table aliasesarenot allowed in the JOIN clause. 

B. Thestatement wouldnotexecute because thetablealias prefix is not usedin theUSING clause. 

C. The statementwouldnot execute because allthecolumns 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 haveaqualifier in the SELECT list. 

Answer: D


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


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 

SET city = ANY (SELECT city FROM locations I); 


B. UPDATE deptd 

SET city = (SELECT city 

FROM locations I) 

WHERE d.location_id = l.location_id; 


C. UPDATE deptd 

SET city = (SELECT city 

FROM locations I 

WHERE d.location_id = l.location_id); 


D. UPDATE deptd 

SET city = ALL (SELECT city 

FROM locations I 

WHERE d.location_id = l.location_id); 


Answer: C


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

You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000. 

Which SOL statement would get you the desired result? 


A. SELECT employee_id, first_name 

FROM employees 

WHERE job_id like‘MAN%' OR job_id like 'CLERK%' 

AND department_id = 30 AND salary > 3000; 


B. SELECT employee_ d, first_name 

FROM employees 

WHERE job_id like‘%MAN' OR job_id like '%CLERK' 

AND (department_id = 30 OR salary > 3000); 


C. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like‘%MAN' AND job_id like '%CLERK’) 

AND department_id = 30 OR salary > 3000; 


D. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like '%MAN' OR job_id like '%CLERK') 

AND department_id= 30 AND salary > 3000; 


Answer: D



see more 1z0-047 dumps