Oracle 1z0-071 Exam With Confidence Using Practice 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.

References:

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

In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.

The corrected SQL statement should be:

SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;

References:

Oracle Documentation on GROUP BY clause: GROUP BY Clause

The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:

A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).

References:

Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.