Oracle 1Z0-071 Exam Dumps

In Oracle SQL, the correct way to check for non-null values is to use the IS NOT NULL condition. Using != NULL or <> NULL is incorrect because NULL represents the absence of any value, and you cannot use equality or inequality operators to check for NULL.

C . SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL; This query will correctly filter out any customers with a NULL income level or credit limit and then calculate the due amount as 5% of their credit limit for the remaining customers.

Oracle Database SQL Language Reference 12c, especially sections on conditions and expressions that deal with NULL values.

A . Incorrect. An uncommitted transaction is not automatically committed if the user exits SQL*Plus. It is rolled back unless otherwise specified. B. Correct. A DML statement such as INSERT, UPDATE, or DELETE will implicitly start a new transaction if there is no current transaction running in the session. C . Incorrect. A user cannot see uncommitted updates made by another session; a session can only see its own uncommitted changes. D. Incorrect. A Data Definition Language (DDL) statement automatically commits any outstanding transactions in the session, not just the changes to the data dictionary. E. Correct. A session can always see its own uncommitted updates. This is because Oracle uses a multiversion consistency model, allowing each user to see a consistent view of the data including their own changes. F. Incorrect but close. If a session has an uncommitted transaction, then a DDL statement does issue a COMMIT before executing and starting a new implicit transaction, not just for the data dictionary updates but for all pending changes in the session.

This information can be verified in the Oracle Database SQL Language Reference and Oracle Database Concepts documentation, which discuss transaction management and the behavior of DML and DDL statements within transactions.

The WHERE clause in SQL filters the rows returned by the SELECT statement. The result of logical operators and conditions can change significantly depending on the use of parentheses.

Options A and E both use parentheses to ensure that borrowed_date = SYSDATE is evaluated with transaction_type ='RM' as one group and the member_id conditions as another group. The parentheses ensure that both conditions within each set of parentheses must be true for the rows to be included.

Option B is incorrect because it does not use parentheses to enforce the grouping of conditions, leading to potentially different results due to the way logical OR works.

Option C is incorrect because it has a syntax error; it is missing a parenthesis.

Option D is incorrect because it will return rows where either borrowed_date = SYSDATE AND transaction_type ='RM' is true or member_id IN ('A101','A102') is true, which is a broader condition than what's specified in options A and E.

A . True. The GROUP BY clause needs to include all non-aggregated columns from the SELECT list to provide the correct grouping for the output. The output shown in the image indicates that the data is grouped by channel_type, month, and country_code.

B, C, and D are incorrect because:

B includes a ROLLUP which would introduce subtotals that are not reflected in the output shown.

C specifies a CUBE, which would produce all possible combinations of groupings including the grand total, which is not shown in the output.

D specifies a ROLLUP on country_code only, which would not correctly group by channel_type and month.

In the case of SQL commands TRUNCATE and DELETE:

A . the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.

B . delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.

Incorrect options are:

C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.

D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.

E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.