Free Oracle 1Z1-047 Exam Dumps Questions & Answers

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.Unconditional INSERT
• B.Conditional ALL INSERT
• C.Pivoting INSERT
• D.Conditional FIRST INSERT

View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?

• A.The PRIMARY KEY is created and it would use an existing unique index.
• B.The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
• C.The PRIMARY KEY is created along with a new index.
• D.The PRIMARY KEY would be created in a disabled state because it is using an existing index.

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 nonunique index.
• B.It would be created and would use an automatically created unique index.
• C.It would be created only if a unique index is manually created first.
• D.It would be created and remains in a disabled state because no index is specified in the command.

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)

• A.The nested query executes first and then the outer query executes.
• B.The outer query executes only once for the result returned by the inner query.
• C.The nested query executes after the outer query returns the row.
• D.Each row returned by the outer query is evaluated for the results returned by the inner query.

Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?

• A.SELECT UPPER(product_name)
  FROM product_information
  WHERE product_jd = 2254;
• B.SELECT product_id
  FROM product_jnformation
  WHERE UPPER(product_name) IN ('LASERPRO', 'Cable);
• C.SELECT UPPER(product_name)
  FROM product_jnformation;
• D.SELECT product_jd, UPPER(product_name)
  FROM product_information
  WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;