Free Oracle 1Z0-047 Exam Dumps Questions & Answers

View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary:
SELECT last_name, departmentjd, salary450*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

• A.  Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
• B.  Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".
• C.  Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
• D.  Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".

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 statement would provide the desired results.
• B.  The statement would not execute because the self join uses the ON clause instead of the USING clause.
• C.  The statement would not execute because the WHERE clause is wrongly placed.
• D.  The statement would not execute because the ON clause is written twice.

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

• A.  It is possible to retrieve data in top-down or bottom-up hierarchy.
• B.  It is possible to remove an entire branch from the output of the hierarchical query.
• C.  You cannot specify conditions when you retrieve data by using a hierarchical query.
• D.  It is possible to retrieve data only in top-down hierarchy.

View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SOL statement would you execute to accomplish the task?

• A.  UPDATE deptd
  SET city = (SELECT city
  FROM locations I)
  WHERE d.location_id = l.location_id;
• B.  UPDATE dept d
  SET city = (SELECT city
  FROM locations I
  WHERE d.location_id = l.location_id);
• C.  UPDATE deptd
  SET city = ANY (SELECT city
  FROM locations I);
• D.  UPDATE dept d
  SET city = ALL (SELECT city
  FROM locations I
  WHERE d.location_id = l.location_id);

In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

• A.  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
• B.  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
• C.  to create group-wise grand totals for the groups specified within a GROUP BY clause
• D.  to find the groups forming the subtotal in a row