Free Oracle 1Z1-047 Exam Dumps Questions & Answers

View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?

• A.  SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information;
• B.  SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;
• C.  SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;
• D.  SELECT product_id, SYSDATE + warranty_period FROM product_information;

View the Exhibit and examine the description of the ORDER_ITEMS and PRODUCT_INFORMATION tables.
The ORDER_ITEM table has records pertaining to details for each product in an order. The PRODUCT_INFORMATION table has records for all the products available for ordering.
Evaluate the following SOL statement:
SELECT oi.order_id, pi.product_id
FROM orderjtems oi RIGHT OUTER JOIN product_information pi ON (oi. product_id=pi. Product_id);
Which statement is true regarding the output of this SOL statement?

• A.  The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table.
• B.  The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered.
• C.  The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.
• D.  The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table.

Which two statements are true about sequences created in a single instance database? (Choose two.)

• A.  DELETE <sequencename> would remove a sequence from the database.
• B.  CURRVAL is used to refer to the last sequence number that has been generated.
• C.  When a database instance shuts down abnormally, the sequence numbers that have been
  cached but not used would be available once again when the database instance is restarted.
• D.  When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
• E.  The numbers generated by a sequence can be used only for one table.

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 one direction, from right to left for calculating the subtotals
• B.  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
• C.  to find the groups forming the subtotal in a row
• D.  to create group-wise grand totals for the groups specified within a GROUP BY clause

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the
PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

• A.  The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
• B.  The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C.  The NEW_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
• D.  The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.