Online 1Z0-071 Practice Test

Question 1

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 need to be updated to reflect the latest information about the customers.

What is the error in the following MERGE statement?

Options :

A : The CUSTOMER_ID 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

Question 2

Which two statements are true regarding a SAVEPOINT? (Choose two.)

Options :

A :

Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.

B :

Only one SAVEPOINT may be issued in a transaction.

C :

A SAVEPOINT does not issue a COMMIT

D :

Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

E :

Rolling back to a SAVEPOINT can undo a DELETE statement

Answer: C,E

Question 3

View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at

the base location.

Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty

USING (faculty_id, location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)

FROM student NATURAL JOIN faculty

GROUP BY faculty_name;

Which statement is true regarding the outcome?

Options :

A : Only statement 2 executes successfully and gives the required result.

B : Only statement 1 executes successfully and gives the required result.

C : Both statements 1 and 2 execute successfully and give different results.

D : Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

Question 4

Which three statements are true about an ORDER BY clause?

Options :

A : An ORDER BY clause always sorts NULL values last.

B : An ORDER BY clause can perform a binary sort

C : An ORDER BY clause can perform a linguistic sort

D : By default an ORDERBY clause sorts rows in ascending order

E : An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

Answer: B,C,D

Question 5

View and Exhibit and examine the structure and data in the INVOICE table.

Which two statements are true regarding data type conversion in query expressions? (Choose two.)

Options :

A : inv_date = '15-february-2008' :uses implicit conversion

B : inv_amt = '0255982' : requires explicit conversion

C : inv_date > '01-02-2008' : uses implicit conversion

D : CONCAT(inv_amt, inv_date) : requires explicit conversion

E : inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: A,C

EXPERTY DESIGNED 1Z0-071 PRACTICE TEST