Question 1

Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1.

Some sales data is duplicated in both tables.

You want to display the rows from the SALES1 table which are not present in the SALES2 table.

Which set operator generates the required output?

Options :

A : SUBTRACT

B : INTERSECT

C : UNION ALL

D : UNION

E : MINUS

Answer: A

Question 2

Which two statements are true about transactions in the Oracle Database server?

Options :

A : An uncommitted transaction commits automatically if the user exits SQL*Plus

B : Data Manipulation Language (DML) statements always start a new transaction.

C : A user can always see uncommitted updates made by the same user in a different session.

D :

A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL

E : A session can always see uncommitted updates made by itself.

F :

If a session has an uncommitted transaction, then a DDL statement issue a COMMIT before starting a new transaction.

Answer: A,B,E

Question 3

The PRODUCTS table has the following structure.

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ‘ ‘) FROM products;

SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;

Which statement is true regarding the outcome?

Options :

A : Both the statements execute and give different results

B : Only the second SQL statement executes successfully

C : Both the statements execute and give the same result

D : Only the first SQL statement executes successfully

Answer: A

Question 4

Which statement is true regarding external tables?

Options :

A : The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table.

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

C : The default REJECT LIMIT for external tables is UNLIMITED.

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

Answer: A

Question 5

View the Exhibit and examine the structure of the PRODUCT table.

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

Options :

A : display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

B : display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’

C : display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

D : display suppliers whose PROD_LIST_PRICE is less than 1000

E : display the minimum PROD_LIST_PRICE for each product status

Answer: A,C

Smartly Prepare Exam with Free Online 1Z0-071 Practice Test

Buying Options: