Pass4sure offers free demo for 1z0 047 pdf exam. "Oracle Database SQL Expert", also known as oracle database sql expert 1z0 047 exam, is a Oracle Certification. This set of posts, Passing the Oracle oracle database sql expert 1z0 047 exam, will help you answer those questions. The oracle database sql expert 1z0 047 Questions & Answers covers all the knowledge points of the real exam. 100% real Oracle 1z0 047 pdf exams and revised by experts!
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1z0-047 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1z0-047 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1z0-047-exam-dumps.html
Q11. Evaluate the following statement:
CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100);
The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table.
Which option should be used to perform this task most efficiently?
A. MERGE
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Q12. 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)jFROM order_items
GROUP BY order_id);
Answer: D
Q13. Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. For each DML operation performed, the corresponding indexes are automatically updated.
C. Indexes should be created on columns that are frequently referenced as part of an expression.
D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
Answer: ABD
Q14. View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id
FROM employees START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)
A. Statement 2 would not execute because theWHEREclause condition is not
allowed in a statementthathas the START WITH clause.
B. Theoutput forstatement1 would displaytheemployee with MANAGER_ID108 and
all the employeesbelowhim or herin thehierarchy.
C. The output of statement 1 wouldneither display the employee with MANAGER_ID 108 nor any
employee below him or herinthe hierarchy.
D. The output for statement 2 would not display theemployee with MANAGER_ID 108 but it
would display all the employees belowhimor her in the hierarchy.
Answer: CD
Q15. View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id);
Which two statements are true regarding the output of this command? (Choose two.)
A. Theoutputwould display thetotal salaryforallthedepartments.
B. Theoutputwoulddisplay the total salaryforall theJOB_IDsinadepartment.
C. The output would display only the grand total ofthesalary for allJOB_IDsin a LOCATION_ID.
D. Theoutput would displaythegrandtotalof thesalaryfor only the groups specified in the GROUP BY clause.
Answer: AB
Q16. 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
Q17. 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
Q18. View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)
A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));
B. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));
C. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
D. DELETE FROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
Answer: ACD
Q19. The following are the steps for a correlated subquery, listed in random order:
1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4,2,1,3
B. 4,1,2,3
C. 2,4,1,3
D. 2,1,4,3
Answer: C
Q20. 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. SELECTfirst_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, salary FROM 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