Online Access Free 1Z0-047 Practice Test

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) = (SELECT MAX(unit_price*quantity) FROM order_items
  GROUP BY order_id);
• B.SELECT order_id
  FROM order_items
  GROUP BY order_id
  HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(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
  WHERE(unit_price*quantity) = MAX(unit_price*quantity)
  GROUP BY order_id;

View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)

• A.GROUP BY
• B.START WITH
• C.CONNECT BY
• D.WHERE
• E.HAVING

Which statement best describes the GROUPING function?

• A.It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals.
• B.It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.
• C.It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators.
• D.It is used to set the order for the groups to be used for calculating the grand totals and subtotals.

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

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

View the Exhibit and examine the structure of the
PRODUCT INFORMATION table.
Which two queries would work? (Choose two.)

• A.SELECT product_name
  FROM product_information
  WHERE list_price = (SELECT AVG(list_price)
  FROM product_information);
• B.SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information);
• C.SELECT product_status FROM product_information GROUP BY product_status HAVING list_price > (SELECT AVG(list_price) FROM product_information);
• D.SELECT product_name
  FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price)
  FROM product_jnformation GROUP BY product_status);