Oracle Updated 1z0-071 Exam Questions and Answers by jeremiah

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.

References:

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

In Oracle Database, object privileges are rights to perform a particular action on a specific object in the database. Here's why the other options are incorrect:

A. UPDATE can be granted on tables, views, and materialized views, but not sequences. B. DELETE cannot be granted on sequences because sequences do not store data that can be deleted. D. INSERT cannot be granted on sequences; sequences are used to generate numbers, not to be inserted into directly. C. REFERENCES allows the grantee to create a foreign key that references the table or the columns of the table. It is applicable only to tables and views. E. SELECT can indeed only be granted on tables and views (including materialized views). F. ALTER is an object privilege that can be granted on tables and sequences but not views.

For more details, one may refer to the Oracle Database SQL Language Reference documentation, which specifies the types of object privileges and the objects they apply to.

A. True. The DECODE function can evaluate conditions, but it is limited to equality checks. On the other hand, CASE can evaluate these and a broader range of conditions using different comparison operators.

D. True. DECODE is a function in Oracle SQL that allows for simple conditional query transformation. CASE is not a function but a statement that provides more flexibility and readability when handling conditional logic in SQL queries.

DECODE is more limited compared to CASE since it can't perform logical operations other than equality.

In Oracle SQL, roles can be granted privileges and other roles, and these privileges can then be passed on to users.

Statement A is correct: GRANT CREATE SEQUENCE TO manager, emp; successfully grants the CREATE SEQUENCE privilege to both the manager and emp roles.

Statement E is correct: GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; successfully grants two system privileges to the manager role.

Statements B, C, and D are incorrect for the following reasons:

B is incorrect because the correct syntax to grant object privileges includes separating privileges with commas, not spaces, and the object name should be in singular form (hr.employees).

C is incorrect because you cannot grant a role and a privilege in the same statement.

D is incorrect because the correct syntax for granting object privileges does not include the privilege CREATE TABLE before specifying the object (hr.employees).