QUESTION: 193

The PRODUCTS table has the following structure.

Evaluate the following two SQL statements:

SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ‘ ‘) FROM products;
SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;

Which statement is true regarding the outcome?

Both the statements execute and give different results
Only the second SQL statement executes successfully
Both the statements execute and give the same result
Only the first SQL statement executes successfully

Answer(s): A

Explanation:

Using the NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned. Syntax

NVL2(expr1, expr2, expr3)
In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null
Expr3 is the value that is returned if expr1 is null

Show Answer Next Question

QUESTION: 192

View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.

You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.

Which two DELETE statements are valid? (Choose two.)

Option A
Option B
Option C
Option D

Answer(s): B,D

Show Answer Next Question

QUESTION: 211

View the Exhibit and examine the structure of the BOOKS table.

The BOOKS table contains details of 100 books.

Examine the commands executed and their outcome:

Which statement is true?

Both ROLLBACK commands restore the 101 rows that were deleted.
Both ROLLBACK commands restore the 100 rows that were deleted.
The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.

Answer(s): C

Show Answer Next Question

QUESTION: 212

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?

DELTE and TRUNCATE statements can have a rollback done to restore data into a table.
DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.
DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table.
DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.

Answer(s): D

Show Answer Next Question

QUESTION: 213

View the Exhibit and examine the description of the EMPLOYEES table.

Evaluate the following SQL statement:

SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) “Review” FROM employees;

The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.

Which statement is true regarding this query?

The query would execute to give the desired output.
The query would not execute because date functions cannot be nested.
The query would execute but the output would give review dates that are Sundays.
The query would not execute because the NEXT_DAY function accepts a string as argument.

Answer(s): C

Show Answer Next Question

Free Oracle 1Z0-071 Exam Questions (page: 15)

QUESTION: 193

Explanation:

QUESTION: 192

QUESTION: 211

QUESTION: 212

QUESTION: 213

1Z0-071 Exam Discussions & Posts