1Z0-071 Online Practice Questions and Answers

Examine the description of the CUSTOMERS table:

Which two SELECT statements will return these results: (Choose two.)

CUSTOMER_ NAME

Mandy Mary

A. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA*';

B. SELECT customer_name FROM customers WHERE customer_name = `*Ma*';

C. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma*';

D. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE `MA%';

E. SELECT customer_name FROM customers WHERE customer_name LIKE `%a%';

F. SELECT customer_name FROM customers WHERE customer_name LIKE `Ma%';

G. SELECT customer_name FROM customers WHERE customer_name LIKE `*Ma*';

Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

Examine this partial query:

SELECT cust_last_nare, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

A. WHERE UPPER(cust_last_name) IN (`AX', `B%') AND cust_credit_limit < 1000;

B. WHERE (UPPER(cust_last_name) LIKE `A%' OR UPPER(cust_last_name) LIKE `B%') AND ROUND(cust_credit_limit) < 1000;

C. WHERE UPPER(cust_last_name) BETWEEN UPPER(`A%' AND `B%') AND ROUND(cust_credit_limit) < 1000;

D. WHERE (INITCAP(cust_last_name) LIKE `A%' OR INITCAP(cust_last_name) LIKE `B%') AND cust_credit_limit < 1000;

E. WHERE (UPPER(cust_last_name) LIKE INITCAP(`A') OR UPPER(cust_last_name) LIKE INITCAP(`B')) AND ROUND(cust_credit_limit) < ROUND(1000);

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

A. SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;

B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

D. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;

E. SELECT product_id, expiry_date * 2 FROM products;

F. SELECT product_id, unit_price, unit_price + surcharge FROM products;

Examine this query:

SELECT SUBSTR (SYSDATE,1,5) `Result' FROM DUAL

Which statement is true?

A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

D. It executes successfully with an implicit data type conversion

Examine the description of the EMPLOYEES table:

For each employee in department 90 you want to display:

1.

their last name

2.

the number of complete weeks they have been employed

The output must be sorted by the number of weeks, starting with the longest serving employee first.

Which statement will accomplish this?

A. Option A

B. Option B

C. Option C

D. Option D

BOOK_SEQ is an existing sequence in your schema.

Which two CREATE TABLE commands are valid?

A. Option A

B. Option B

C. Option C

D. Option D

E. Option E

Which three statements are true about multiple row subqueries?

A. They can contain HAVING clauses.

B. Two or more values are always returned from the subquery.

C. They cannot contain subquery.

D. They can return multiple columns.

E. They can contain GROUP BY clauses.

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found.

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.

C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders

SET UNUSED (order_date);

Which statement is true?

A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.

C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

D. The DESCRIBE command would still display the ORDER_DATE column.

Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.)

A. It can be used with SET operators (UNION, INTERSECT etc.).

B. It cannot be used with the DISTINCT keyword.

C. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait.

D. The statement skips rows locked by other transactions.

E. It can be used with joins.