1z0-047 | What Simulation 1z0-047 pdf Is?


Q51. Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

Answer: D

Q52. View the Exhibit and examine the description of the ORDERS table. 

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.) 

A. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY") 

B. WHERE TO_CHAR(order_date,'MON DD YYYY} = 'JAN 20 2003' 

C. WHERE order_date > T0_CHAR(ADD_M0NTHS(SYSDATE,6),'M0N DD YYYY’) 

D. WHERE order_date IN (T0_DATE('0ct 21 2003','Mon DD YYYY’), T0_CHAR('N0V 21 2003','Mon DD YYYY’)) 

Answer: AB

Q53. Evaluate the following statement: INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; 

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? 

A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. 

C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

Answer: A

Q54. View the Exhibit and examine the structure of the ORDERS table. 

The ORDERS table belongs to the user OE. HR is another user in the database. 

Evaluate the commands issued by users OE and HR in the following order: 

Statement 1 by user OE: GRANT SELECT, 

UPDATE(customer_id, order_total) 

ON orders 

TOhr; 

Statement 1 by user HR: SELECT * FROM oe.orders; 

Statement 2 by user HR: UPDATE oe.orders 

SET order_totah 10000; 

Which statement is true regarding the above commands? 

A. Statement 1 by user OE would not work because the statement has to be issued by the DBA. 

B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update. 

C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully. 

D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns. 

Answer: C

Q55. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL? 

A. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price IS NULL; 

B. SELECT COUNT(list_price) 

FROM product_information 

WHERE list_price = NULL; 

C. SELECT COUNT(NVL(list_price, 0)) 

FROM product_information 

WHERE list_price IS NULL; 

D. SELECT COUNT(DISTINCT list_price) 

FROM product_information 

WHERE list_price IS NULL; 

Answer: C

Q56. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply. 

A. It is possible to retrieve data only in top-down hierarchy. 

B. Itis possible to retrieve data in top-down or bottom-up hierarchy. 

C. It is possible to remove an entire branch from the output of the hierarchical query. 

D. You cannot specify conditions when you retrieve data by using a hierarchical query. 

Answer: BC

Q57. View the Exhibit and examine the structure of the CUSTOMERS table. 

CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers. 

What is the error in the following MERGE statement? 

MERGE INTO customers c 

USING customer_vu cv 

ON (c.customer_id = cv.customer_id) 

WHEN MATCHED THEN 

UPDATE SET 

c.customer_id = cv.customer_id, 

c.cust_name = cv.cust_name, 

c.cust_email = cv.cust_email, 

c.income_level = cv. Income_level 

WHEN NOT MATCHED THEN 

INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) 

WHERE cv. Income_level >100000; 

A. The CUSTOMERJD column cannot be updated. 

B. The INTO clause is misplaced in the command. 

C. The WHERE clause cannot be used with INSERT. 

D. CUSTOMER VU cannot be used as a data source 

Answer: A

Q58. Which statement is true regarding the SESSION_PRIVS dictionary view? 

A. It contains the current object privileges available in the user session. 

B. It contains the current system privileges available in the user session. 

C. It contains the object privileges granted to other users by the current user session. 

D. It contains the system privileges granted to other users by the current user session. 

Answer: B

Q59. Evaluate the SQL statements: 

CREATE TABLE new_order 

(orderno NUMBER(4), 

booking_date TIMESTAMP WITH LOCAL TIME ZONE); 

The database is located in San Francisco where the time zone is -8:00. 

The user is located in New York where the time zone is -5:00. 

A New York user inserts the following record: 

INSERT INTO new_order 

VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?); 

Which statement is true? 

A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000' 

B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'. 

C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000' 

D. When the San Francisco user selectstherow, booking_dateis displayed 

as 007-05-103.00.00.000000-8:00' 

Answer: C

Q60. Evaluate the following SELECT statement and view the Exhibit to examine its output: 

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS 

Which two statements are true about the output? (Choose two.) 

A. In the second column, indicates a check constraint. 

B. The STATUS column indicates whether the table is currently in use. 

C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint. 

D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table. 

Answer: AD