We provide real 1z0 047 dumps exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Oracle oracle 1z0 047 Exam quickly & easily. The 1z0 047 dumps PDF type is available for reading and printing. You can print more and practice many times. With the help of our Oracle oracle 1z0 047 dumps pdf and vce product and material, you can easily pass the 1z0 047 dumps exam.

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

A. DBA_TABLES 

B. DBA_COLUMNS 

C. USER_COLUMNS 

D. USER TAB COLUMNS 

Answer: D


Q22. Which statements are correct regarding indexes? (Choose all that apply.) 

A. When a table is dropped, the corresponding indexes are automatically dropped. 

B. For each DML operation performed, the corresponding indexes are automatically updated. 

C. Indexes should be created on columns that are frequently referenced as part of an expression. 

D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. 

Answer: ABD


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


Q24. Which statement is true regarding Flashback Version Query? 

A. It returns versions of rows only within a transaction. 

B. It can be used in subqueries contained only in a SELECT statement. 

C. It will return an error if the undo retention time is less than the lower bound time or SCN specified. 

D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows. 

Answer: D


Q25. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount" 

FROM orde_items oi JOIN orders o 

ON oi.order_id = o.order_id 

GROUP BY CUBE (o.customer_id, oi.product_id); 

Which three statements are true regarding the output of this SQL statement? (Choose three.) 

A. It would return the subtotals for the Order Amount of every CUSTOMER_ID. 

B. It would return the subtotals for the Order Amount for every PRODUCT_ID. 

C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. 

D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

Answer: ABD


Q26. Which three statements are true regarding single-row functions? (Choose three.) 

A. They can accept only one argument. 

B. They can be nested up to only two levels. 

C. They can return multiple values of more than one data type. 

D. They can be used in SELECT, WHERE, and ORDER BY clauses. 

E. They can modify the data type of the argument that is referenced. 

F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. 

Answer: DEF


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


Q28. Evaluate the following statement: 

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100); 

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table. 

Which option should be used to perform this task most efficiently? 

A. MERGE 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

Answer: A


Q29. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence: 

CREATE ROLE r1; 

GRANT SELECT, INSERT ON oe. orders TO r1; 

GRANT r1 TO scott; 

GRANT SELECT ON oe. orders TO scott; 

REVOKE SELECT ON oe.orders FROM scott; 

What would be the outcome after executing the statements? 

A. SCOTT would be able to query the OE.ORDERS table. 

B. SCOTT would not be able to query the OE.ORDERS table. 

C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1. 

D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1 

Answer: A


Q30. View the Exhibit and examine the data in the PRODUCT INFORMATION table. 

Which two tasks would require subqueries? (Choose two.) 

A. displaying the minimum list price for each product status 

B. displaying all supplier IDs whose average list price is more than 500 

C. displaying the number of products whose list prices are more than the average list price 

D. displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable 

E. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE 

Answer: CD