Question 1

Which statement is true about the INTERSECT operator used in compound queries?

Options :

A : It processes NULLS in the selected columns.

B : INTERSECT is of lower precedence than UNION or UNION ALL.

C : It ignores NULLS.

D : Multiple INTERSECT operators are not possible in the same SQL statement.

Answer: A

Question 2

In which three situations does a new transaction always start?

Options :

A :

When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

B :

When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

C : When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

D : When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

E :

When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

F : When issuing a DML statement after a DML statement filed in the same session.

Answer: A,C,E

Question 3

Examine this SQL statement:

Which two are true?

Options :

A : The subquery is not a correlated subquery.

B : The subquery is executed before the DELETE statement is executed

C : The DELETE statement executes successfully even if the subquery selects multiple rows

D : The subquery is executed for every row in the EMPLOYEES table.

E : All existing rows in the EMPLOYEES table are deleted

Answer: B,D

Question 4

View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE

CASCADE option. The SALES table contains data for the last three years. You want to remove all the

rows from the PRODUCTS table for which no sale was done for the last three years.

Which is the valid DELETE statement?

Options :

A : DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );

B : DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );

C : DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);

D : DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Answer: C

Question 5

Examine the data in the PRODUCTS table:

Examine these queries:

1. SELECT prod name, prod list

FROM products

WHERE prod 1ist NOT IN(1020) AND category _id=1;

2. SELECT prod name, | prod _ list

FROM products

WHERE prod list < > ANY (1020) AND category _id= 1;

SELECT prod name, prod _ list

FROM products

WHERE prod_ list < > ALL (10 20) AND category _ id= 1;

Which queries generate the same output?

Options :

A : 1 and 3

B : 1, 2 and 3

C : 2 and 3

D : 1 and 2

Answer: A

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: