1z0-071 Practice Test

Which three tasks can be performed using SQL functions built into Oracle Database?

A.

displaying a date in a nondefault format

B.

finding the number of characters in an expression

C.

substituting a character string in a text expression with a specified string

D.

combining more than two columns or expressions into a single column in the output

Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?

A.

When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

B.

When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table
are deleted but the table structure is retained.

C.

When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS
table whose BOOK_ID matches that of the deleted row in the BOOKS table are also
deleted.

D.

When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is
updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the
managers and the second column would have LAST_NAME of the employees

Which SQL statement would you execute?

A.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;

B.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100;

C.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100;

D.

SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100;

Evaluate the following statement.
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 200000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the
INSERT statement?

A.

Each row is evaluated by the first WHEN clause and if the condition is false then the row
would be evaluated by the subsequent when clauses.

B.

All rows are evaluated by all the three WHEN clauses.

C.

Each row is evaluated by the first WHEN clause and if the condition is true, then the row
would be evaluated by the subsequent when clauses.

D.

The INSERT statement will return an error because the ELSE clause is missing.

View the exhibit and examine the structure of the CUSTOMERS table.

Which two tasks would require subqueries or joins to be executed in a single statement?

A.

finding the number of customers, in each city, whose credit limit is more than the
average credit limit of all the customers

B.

finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

C.

listing of customers who do not have a credit limit and were born before 1980

D.

 finding the number of customers, in each city, who’s marital status is 'married'.

E.

listing of those customers, whose credit limit is the same as the credit limit of customers
residing in the city 'Tokyo'.