Our pass rate is high to 98.9% and the similarity percentage between our 1z0 047 pdf study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Oracle oracle database sql expert 1z0 047 exam in just one try? I am currently studying for the Oracle 1z0 047 dumps exam. Latest Oracle 1z0 047 dumps Test exam practice questions and answers, Try Oracle oracle database sql expert 1z0 047 Brain Dumps First.

Q1. Evaluate the following statement: 

INSERT ALL 

WHEN order_total < 10000 THEN 

INTO small_orders 

WHEN order_total > 10000 AND order_total < 20000 THEN 

INTO medium_orders 

WHEN order_total > 2000000 THEN 

INTO large_orders 

SELECT order_id, order_total, customer_id 

FROM orders; 

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? 

A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. 

C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

Answer: A


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

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. 

Which SOL statement would you execute to get the desired output? 

A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total" 

FROM EMPLOYEES; 

B. SELECTfirst_name,salary, salary*12+NVL((salary*commission_pct), 0) "Total" 

FROMEMPLOYEES; 

C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total" 

FROM EMPLOYEES; 

D. SELECTfirst_name, salary, salary*12+(salary*NVL2(commission_pct, 

salary,salary+commission_pct))"Total" 

FROM EMPLOYEES; 

Answer: B


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


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

You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written: 

WITH 

SELECT employee_id, last_name, salary 

FROM employees 

WHERE (department_id, salary) = ANY (SELECT* 

FROM dept_max) 

dept_max as (SELECT d.department_id, max(salary) 

FROM departments d JOIN employees j 

ON (d. department_id = j. department_id) 

GROUP BY d. department_id); 

Which statement is true regarding the execution and the output of this statement? 

A. The statementwouldexecute and give the desired results. 

B. Thestatement wouldnotexecute becausethe= ANY comparison operator is used instead of=. 

C. Thestatement wouldnot execute because themain queryblock usesthequery name beforeitis even created. 

D. The statement wouldnot execute because the commaismissing betweenthemain query block and the query name. 

Answer: C


Q5. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL? 

A. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price IS NULL; 

B. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price = NULL; 

C. SELECT COUNT(NVL(list_price, 0)) 

FROM product_information 

WHERE list_price IS NULL; 

D. SELECT COUNT(DISTINCT list_price) 

FROM product_information 

WHERE list_price IS NULL; 

Answer: C


Q6. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_jd, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_jd, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

Answer: A


Q7. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. 

Evaluate the following SQL statement: 

ALTER TABLE emp DROP COLUMN first_name; 

Which two statements are true regarding the above command? (Choose two.) 

A. The FIRST_NAME column would be dropped provided it does not contain any data. 

B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table. 

C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement. 

D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used. 

Answer: BD


Q8. View the Exhibit and examine the data in the PRODUCT_INFORMATION table. 

There are some products listed in the PRODUCT_INFORMATION table that have no value in the 

LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME 

for these products: 

SELECT product_name, list_price 

FROM product_information WHERE list_price = NULL; 

The query returns no rows. What changes would you make in the statement to get the desired result? 

A. Change the WHERE clause to WHERE list_price = 0 

B. Change the WHERE clause to WHERE list_price = ''. 

C. Change the WHERE clause to WHERE list_price IS NULL. 

D. In the WHERE clause, enclose NULL within single quotation marks. 

E. In the WHERE clause, enclose NULL within double quotation marks. 

Answer: C


Q9. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. 

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom. 

Which SQL statement would you execute to fulfill this requirement? 

A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period; 

B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period; 

C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE; 

D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM product_information WHERE warranty_period >SYSDATE; 

Answer: B


Q10. View the Exhibit and examine the data in the DEPARTMENTS tables. 

Evaluate the following SQL statement: 

SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 

Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.) 

A. ORDERBY 3; 

B. ORDER BY 'b' 

C. ORDER BY DEPT_ID; 

D. ORDER BY DEPT NAME; 

Answer: AC