[Rebirth] 1z0-047 syllabus

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 Apr 1Z0-047 Study Guide Questions:

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


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


Q99. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.) 

A. The HAVING clause conditions can have aggregate functions. 

B. The HAVING clause conditions can use aliases for the columns. 

C. WHERE and HAVING clauses cannot be used together in a SQL statement. 

D. The WHERE clause is used to exclude rows before the grouping of data. 

E. The HAVING clause is used to exclude one or more aggregated results after grouping data. 

Answer: ADE


Q100. View the Exhibit and examine the structure of the ORDER_ITEMS table. 

You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. 

Which query would produce the desired output? 


A. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = MAX(unit_price*quantity) 

GROUP BY order_id; 

B. SELECT order_id 

FROM order_items 

WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) 

GROUP BY order_id; 

C. SELECT order_id 

FROM order_items 

WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items 

GROUP BY order_id); 

D. SELECT order_id 

FROM order_items 

GROUP BY order_id 

HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)) 

FROM order_items GROUP BY order_id); 

Answer: D


Q101. View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables. 


You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written: 

SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department" FROM employees m JOIN employees e 

ON (m.employee_id = e.manager_id) 

WHERE e.manager_id=100 

JOIN departments d 

ON (e.department_id = d.department_id); 

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

A. The statementwouldprovide the desired results. 

B. Thestatement wouldnotexecute because the ON clause iswritten twice. 

C. Thestatement wouldnot execute because the WHERE clause is wrongly placed. 

D. The statement wouldnot execute because the self join usestheON clauseinsteadof the USING clause. 

Answer: C


1Z0-047 practice exam

Updated oracle database sql expert 1z0-047 dumps:

Q102. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently? 

A. external table 

B. the MERGEcommand 

C. themultitableINSERT command 

D. INSERTusingWITHCHECK OPTION 

Answer: C


Q103. View the Exhibit and examine the details for the CATEGORIES_TAB table. 


Evaluate the following incomplete SQL statement: 

SELECT category_name ,category_description 

FROM categories_tab 

You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. 

Which two WHERE clause options can give you the desired result? (Choose two.) 

A. WHEREREGEXP_LIKE(category_description, 'hard+.s’); 

B. WHERE REGEXP_LIKE(category_description,‘^H|hard+.s’); 

C. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$'); 

D. WHEREREGEXP_LIKE (category_description, '[^Hlhard+.s]'); 

Answer: AB


Q104. Evaluate the following expression using meta character for regular expression: 

'[AAle|ax.r$]' 

Which two matches would be returned by this expression? (Choose two.) 

A. Alex 

B. Alax 

C. Alxer 

D. Alaxendar 

E. Alexender 

Answer: DE


Q105. Which statement is true regarding synonyms? 

A. Synonyms can be created for tables but not views. 

B. Synonyms are used to reference only those tables that are owned by another user. 

C. A public synonym and a private synonym can exist with the same name for the same table. 

D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid. 

Answer: C


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


1Z0-047 free practice exam

Real 1z0-047 oracle database sql expert:

Q107. View the Exhibit and examine the data in the CUST_DET table. 


You executed the following multitable INSERT statement: 

INSERT FIRST 

WHEN credit_limit >= 5000 THEN 

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) 

WHEN grade = THE 

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) 

WHEN gender = THE 

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) 

INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) 

ELSE 

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) 

SELECT * FROM cust_det; 

The row will be inserted in________. 

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied 

B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied 

C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied 

D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it 

Answer: A


Q108. Evaluate the following SQL statement: 

SELECT product_name || 'it's not available for order' 

FROM product_information 

WHERE product_status = 'obsolete' 

You received the following error while executing the above query: 

ERROR: 

ORA-01756: quoted string not properly terminated 

What would you do to execute the query successfully? 

A. Enclose the character literal string intheSELECT clause within thedoublequotation marks. 

B. Do not enclosethecharacter literal string in the SELECT clausewithinthesingle quotation marks. 

C. Use Quote (q) operator anddelimiterto allowtheuse of single quotation mark in the literal character string. 

D. Use escape charactertonegatethesingle quotationmarkinside theliteralcharacter string in the SELECT clause. 

Answer: C


Q109. View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries? 


A. displaying the total order value for sales representatives 161 and 163 

B. displaying the order total for sales representative 161 in the year 1999 

C. displaying the number of orders that have order mode online and order date in 1999 

D. displaying the number of orders whose order total is more than the average order total for all online orders 

Answer: D


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

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. 

Which SQL statement would you execute? 


A. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE m.manager_id=100; 

B. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE e.manager_id=100; 

C. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON e.employee_id = m.manager_id WHERE m.manager_id=100; 

D. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

WHERE m.employee_id = e.manager_id AND e.manager_id=100; 


Answer: B


Q111. Which three statements are true regarding single-row functions? (Choose three.) 

A. They can accept only one argument. 

B. They can be nested up to only two levels. 

C. They can return multiple values of more than one data type. 

D. They can be used in SELECT, WHERE, and ORDER BY clauses. 

E. They can modify the data type of the argument that is referenced. 

F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. 

Answer: DEF


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

You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied 

by QUANTITY), where the total price is greater than 50,000. 

You executed the following SQL statement: 

SELECT order_id, product_id, unit_price*quantity "Total Price" FROM order_items WHERE 

unit_price*quantity > 50000 NATURAL JOIN orders; Which statement is true regarding the execution of the statement? 


A. The statement would execute and provide the desired result. 

B. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause. 

C. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause. 

D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause. 

Answer: C