Online Access Free 1Z0-047 Practice Test

View the Exhibit and examine the description of the ORDER_ITEMS table. The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a
UNIT_PRICE of more than 1,000 and has been ordered more than five times: SELECT product_id, COUNT(order_id) total, unit_price
FROM order_items
WHERE unit_price>1000 AND COUNT(order_id)>5
GROUP BY product_id, unit_price;
Which statement is true regarding this SQL statement?

• A.The statement would not execute because in the SELECT clause, the UNIT_PRICE column is placed after the column having the aggregate function.
• B.The statement would not execute because the aggregate function is used in the WHERE clause.
• C.The statement would execute and give you the desired result.
• D.The statement would not execute because the WHERE clause should have the OR logical operator instead of AND.

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,1,4,3
• D.2,4,1,3

View the Exhibit and examine the structure of the ORDERS table.
NEWJDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and
ORD_TOTAL that have the same data types and size as the corresponding columns in the
ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES(SELECT
order_id.order_date.customer_id.order_total FROM orders WHERE order_date > '31-dec-1999');
Why would the INSERT statement fail?

• A.because the WHERE clause cannot be used in a subquery embedded in an INSERT statement
• B.because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table
• C.because the VALUES clause cannot be used in an INSERT with a subquery
• D.because column names in NEW_ODRDERS and ORDERS tables do not match

View the Exhibit and examine the structure of the ORDERS table. Which UPDATE statement is valid?

• A.UPDATE orders
  SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
• B.UPDATE orders
  SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')l SET order_total =
  TO_NUMBER(NULL) WHERE order id = 2455;
• C.UPDATE orders
  SET order_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455;
• D.UPDATE orders
  SET order_date = '12-mar-2007'
  AND order_total = TO_NUMBER(NULL)
  WHERE order_id = 2455;

View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

• A.ORDER BY DEPT_ID;
• B.ORDER BY 'b';
• C.ORDER BY 3;
• D.ORDER BY DEPT NAME;