1z0-071 Practice Test

Which two statements are true regarding roles? (Choose two.)

A.

A role can be granted to itself.

B.

A role can be granted to PUBLIC.

C.

A user can be granted only one role at any point of time.

D.

The REVOKE command can be used to remove privileges but not roles from other
users.

E.

Roles are named groups of related privileges that can be granted to users or other roles

Which three statements are true regarding the usage of the WITH clause in complex
correlated subqueries: (Choose three.)

A.

It can be used only with the SELECT clause.

B.

The WITH clause can hold more than one query.

C.

If the query block name and the table name are the same, then the table name takes
precedence.

D.

The query name in the WITH clause is visible to other query blocks in the WITH clause
as well as to the main query block

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
(Choose the best answer.)

You executed this UPDATE statement:
UPDATE
( SELECT order_date, order_total, customer_id FROM orders)
Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600);
Which statement is true regarding the execution?

A.

It would not execute because a subquery cannot be used in the WHERE clause of an
UPDATE statement.

B.

It would not execute because two tables cannot be referenced in a single UPDATE
statement.

C.

It would execute and restrict modifications to the columns specified in the SELECT
statement.

D.

It would not execute because a SELECT statement cannot be used in place of a table
name.

Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

A.

Use Quote (q) operator and delimiter to allow the use of single quotation mark in the
literal character string.

B.

Enclose the literal character string in the SELECT clause within the double quotation
marks.

C.

Do not enclose the character literal string in the SELECT clause within the single
quotation marks.

D.

Use escape character to negate the single quotation mark inside the literal character

Examine the SQL statement used to create the TRANSACTION table. (Choose the best
answer.)
SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for
START_DATE?

A.

INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))

B.

INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))

C.

INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')

D.

INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')