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 May 1z0-047 Study Guide Questions:

Q111. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) 

A. The nested query executes after the outer query returns the row. 

B. The nested query executes first and then the outer query executes. 

C. The outer query executes only once for the result returned by the inner query. 

D. Each row returned by the outer query is evaluated for the results returned by the inner query. 

Answer: AD


Q112. View the Exhibit and examine the data in the PRODUCT INFORMATION table. 


Which two tasks would require subqueries? (Choose two.) 

A. displaying the minimum list price for each product status 

B. displaying all supplier IDs whose average list price is more than 500 

C. displaying the number of products whose list prices are more than the average list price 

D. displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable 

E. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE 

Answer: CD


Q113. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.) 

A. It is possible to retrieve data only in top-down hierarchy. 

B. Itis possible to retrieve data in top-down or bottom-up hierarchy. 

C. It is possible to remove an entire branch from the output of the hierarchical query. 

D. You cannot specify conditions when you retrieve data by using a hierarchical query. 

Answer: BC


1z0-047 actual test

Leading oracle 1z0-047 dumps free download:

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


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


Q116. View the Exhibit and examine the details of the EMPLOYEES table. 


You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100. 

Which SQL clauses would you require to accomplish the task? (Choose all that apply.) 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY 

Answer: ADE


1z0-047 exam cost

100% Guarantee oracle 1z0-047 pdf material:

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


Q118. Which two statements are true regarding subqueries? (Choose two.) 

A. Only two subqueries can be placed at one level. 

B. A subquery can be used to access data from one or more tables or views. 

C. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. 

Answer: BC


Q119. A subquery is called a single-row subquery when 

A. the inner query returns a single value to the main query 

B. the inner query uses an aggregate function and returns one or more values 

C. there is only one inner query in the main query and the inner query returns one or more values 

D. the inner query returns one or more values and the main query returns a single value as output 

Answer: A


Q120. Evaluate the following SQL statement: 

ALTER TABLE hr.emp SET UNUSED (mgr_id); 

Which statement is true regarding the effect of the above SQL statement? 

A. Any synonym existing on the EMP table would have to be re-created. 

B. Any constraints defined on the MGR_ID column would be removed by the above command. 

C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created. 

D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed. 

Answer: B