Question 1

Which is true about the & and && prefixes with substitution variables?

Options :

A : & can prefix a substitution variable name only in queries. DML

B :

An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice .

C :

The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

D :

An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.

E : Both & and && can prefix a substitution variable name in queries and DML statements.

Answer: E

Question 2

Examine the structure of the INVOICE table.

Which two SQL statements would execute successfully? (Choose two.)

Options :

A : SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;

B : SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;

C : SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;

D : SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;

Answer: A,C

Question 3

Examine the data in the CUST NAME column of the CUSTOMERS table:

CUST_NAME

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

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two

WHERE clauses give the required result?

Options :

A :

WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)

B :

WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')

C :

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'

D :

WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'

E :

WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'

Answer: B,C

Question 4

Which three statements are true about views in an Oracle database?

Options :

A :

A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query

B :

Rows inserted into a table using a view are retained in the table if the view is dropped

C :

Views can join tables only if they belong to the same schema.

D :

Views have no segment.

E :

Views have no object number.

F :

A view can be created that refers to a non-existent table in its defining query.

G :

Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error.

Answer: B,D,F,G

Question 5

You want to write a query that prompts for two column names and the WHERE condition each time It is

executed in a session but only prompts for the table name the first time it is executed. The variables used in

your

query are never undefined in your session . Which query can be used?

Options :

A :

SELECT &col1, &col2

FROM &&table

WHERE &condition;

B :

SELECT &col1, &col2

FROM “&table”

WHERE &condition;

C :

SELECT &&col1,&&col2

FROM &table

WHERE &&condition= &&cond;

D :

SELECT'&co11','&&co12'

FROM &table

WHERE'&&condition' ='&cond';

E :

SELECT&&col1, &&col2

FROM &table

WHERE &&condition;

Answer: A

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: