Online Access Free 1Z1-047 Exam Questions

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

• A.SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information);
• B.SELECT product_name
  FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price)
  FROM product_jnformation GROUP BY product_status);
• 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_information
  WHERE list_price = (SELECT AVG(list_price)
  FROM product_information);

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 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 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

View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables.
In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key.
Which view can have all the DML operations performed on it?

• A.CREATE VIEW V1
  AS SELECT order_id, product_id
  FROM order_items;
• B.CREATE VIEW V2
  AS SELECT order_id, line_item_id, unit_price*quantity total
  FROM order items;
• C.CREATE VIEW V3
  AS SELECT o.order_id, o.customer_id, i.product_id
  FROM orders o, order_items i
  WHERE o.order_id=i. order_id;
• D.CREATE VIEW V4(or_no, or_date, cust_id)
  AS SELECT order_id, order_date, customer_id
  FROM orders
  WHERE order_date < 30-mar-2007'
  WITH CHECK OPTION;

Which view would you use to display the column names and DEFAULT values for a table?

• A.USER_COLUMNS
• B.USER TAB COLUMNS
• C.DBA_TABLES
• D.DBA_COLUMNS

View the Exhibit and examine the structure of the
PRODUCTINFORMATION and INVENTORIES tables.
You want to display the quantity on hand for all the products available in the PRODUCTINFORMATION table that have the PRODUCT_STATUS as 'orderable'
QUANTITY_ON_HAND is a column in the INVENTORIES table. The following SQL statement
was written to accomplish the task:
SELECT pi. Product_id, pi.product_status, sum(i.quantity_on_hand)
FROM product_information pi LEFT OUTER JOIN inventories i
ON (pi. Product_id = i. product_id)
WHERE (pi.product_status = 'orderable")
GROUP BY pi.product_id, pi.product_status;
Which statement is true regarding the execution of this SQL statement?

• A.The statement would execute and produce the desired output.
• B.The statement would not execute because prefixing table alias to column names is not allowed with the ON clause.
• C.The statement would not execute because the WHERE clause is used before the GROUP BY clause.
• D.The statement would not execute because the WHERE clause is not allowed with LEFT OUTER JOIN.