1z0-047 | All About Guaranteed 1z0-047 exam dumps
Q71. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables.
Evaluate the following MERGE statement:
MERGE INTO orders_master o USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?
A. The ORDERS_MASTER table would contain the ORDERJDs1and 2.
B. TheORDERS_MASTERtablewould containtheORDERJDs 1,2and3.
C. TheORDERS_MASTERtable would containtheORDERJDs 1,2 and 4.
D. The ORDERSMASTER table would containtheORDER IDs 1,2,3 and4.
Answer: C
Q72. 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
Q73. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?
A. Pivoting INSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: C
Q74. 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
Q75. View the Exhibit and examine the description of the EMPLOYEES table.
Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement:
SELECTfirst_name, salary, salary*!2+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees;
Which statement is true regarding the above query?
A. It would execute and give you the desired output.
B. It would not execute because the AS keyword is missing between the column name and the alias.
C. It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
D. It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.
Answer: A
Q76. 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
Q77. 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
Q78. View the Exhibit and examine the structure of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which statement is true regarding the output for this command?
A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
Answer: C
Q79. View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY PRIOR
Answer: E
Q80. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B. to create group-wise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
Answer: C