Free Oracle 1Z1-047 Exam Dumps Questions & Answers

View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';
Which statement is true regarding the execution of this SQL statement?

• A.  It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
• B.  It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
• C.  It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.
• D.  It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.

Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?

• A.  It would be created and would use an automatically created unique index.
• B.  It would be created only if a unique index is manually created first.
• C.  It would be created and remains in a disabled state because no index is specified in the command.
• D.  It would be created and would use an automatically created nonunique index.

Which three statements are true regarding the WHERE and HAVING clauses in a SQL
statement? (Choose three.)

• A.  The HAVING clause conditions can have aggregate functions.
• B.  The HAVING clause is used to exclude one or more aggregated results after grouping data.
• C.  WHERE and HAVING clauses cannot be used together in a SQL statement.
• D.  The HAVING clause conditions can use aliases for the columns.
• E.  The WHERE clause is used to exclude rows before the grouping of data.

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?

• A.  the multitable INSERT command
• B.  the MERGE command
• C.  INSERT using WITH CHECK OPTION
• D.  external table

The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?

• A.  Pivoting INSERT
• B.  Conditional ALL INSERT
• C.  Unconditional INSERT
• D.  Conditional FIRST INSERT