Online 1Z0-071 Practice Test

Question 1

Examine the structure of the MEMBERS table:

NameNull?Type

------------------ --------------- ------------------------------

MEMBER_IDNOT NULLVARCHAR2 (6)

FIRST_NAMEVARCHAR2 (50)

LAST_NAMENOT NULLVARCHAR2 (50)

ADDRESSVARCHAR2 (50)

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM

members;

What is the outcome?

Options :

A : It fails because the alias name specified after the column names is invalid.

B : It fails because the space specified in single quotation marks after the first two column names is invalid.

C : It executes successfully and displays the column details in a single column with only the alias column heading.

D : It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

Question 2

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

Question 3

Examine the description of the EMPLOYEES table:

Examine this query:

Which line produces an error?

Options :

A : Line 7

B : Line 8

C : Line 3

D : Line 5

Answer: C

Question 4

Which two are true about the MERGE statement?

Options :

A : The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

B : The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

C : The WHEN MATCHED clause can be used to specify the inserts to be performed.

D : The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

E : The WHEN MATCHED clause can be used to specify the updates to be performed.

Answer: B,E

Question 5

You execute the SQL statement:

What is the outcome?

Options :

A : It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.

B : It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.

C : It fails because the condition for the CNAMES constraint is not valid.

D : It succeeds and an index is created for CITIZEN_ID.

Answer: A

EXPERTY DESIGNED 1Z0-071 PRACTICE TEST