Free Oracle 1Z1-047 Exam Dumps Questions & Answers

Which two statements are true regarding operators used with subqueries? (Choose two.

• A.  =ANY and =ALL operators have the same functionality.
• B.  The NOT operator can be used with IN, ANY and ALL operators.
• C.  The <ANY operator means less than the maximum.
• D.  The IN operator cannot be used in single-row subqueries.
• E.  The NOT IN operator is equivalent to IS NULL

View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?

• A.  SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
  GROUP BY sales_rep_id;
• B.  SELECT sales_rep_id, COUNT(order_id) total_orders
  FROM orders;
• C.  SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
  GROUP BY sales_rep_id, total_orders;
• D.  SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
  WHERE sales_rep_id IS NOT NULL;

View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: SELECT order_id, product_id, unit_price
FROM order_jtems
WHERE unit_price = (SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per
ORDER_ID.
What correction should be made in the above SQL statement to achieve this?

• A.  Replace = with the IN operator.
• B.  Replace = with the >ALL operator.
• C.  Remove the GROUP BY clause from the subquery and place it in the main query.
• D.  Replace = with the >ANY operator.

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*12)+50 "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+(50*12) "Annual Compensation".

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)

• A.  CREATE INDEX ord_idx
  ON ord,ord_items(ord_no, ord_date,qty);
• B.  CREATE INDEX ord_idx
  ON ord(ord_no);
• C.  CREATE INDEX ord_idx
  ON ord_items(item_no);
• D.  CREATE INDEX ord_idx
  ON ord_items(ord_no);