Free 1Z0-071 Conditional Expressions Questions and Answers

Question 1

Which of the following is a valid use of the CASE expression in SQL?

Accepted Answer

CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END

Answer

The `CASE` expression in SQL allows for conditional logic, similar to if-then-else statements. The correct syntax for a searched `CASE` expression, which evaluates multiple conditions, is `CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END`. Option B correctly follows this structure by using `WHEN` followed by a boolean condition.

Question 2

What will be the output of the following SQL statement?

Accepted Answer

The statement assigns grade 'A' to employees with a salary greater than 10000, 'B' to those with a salary between 5001 and 10000, and 'C' to others.

Answer

Assuming a standard `CASE` statement structure, the conditions are evaluated sequentially. An employee with a salary greater than 10000 will first match `WHEN salary > 10000 THEN 'A'`. If that's false, the next condition `WHEN salary > 5000 THEN 'B'` is checked, meaning salaries between 5001 and 10000 will get 'B'. Finally, `ELSE 'C'` catches all remaining salaries (5000 or less). This logic correctly assigns grades based on the specified salary ranges.

Question 3

Which of the following is true about the DECODE function?

Accepted Answer

It compares an expression to one or more values and returns a corresponding result.

Answer

The `DECODE` function, specific to Oracle SQL, provides conditional logic by comparing an expression to a series of search values. If a match is found, it returns the corresponding result; otherwise, it returns an optional default value. It acts as a shorthand for a simple `CASE` statement, handling various data types and NULL values effectively.

Question 4

How would you use the COALESCE function to return the first non-NULL value from a list of columns?

Accepted Answer

SELECT COALESCE(column1, column2, column3) FROM table_name;

Answer

The `COALESCE` function is used to return the first non-NULL expression from a list of arguments. You simply provide the columns or expressions as a comma-separated list within the function's parentheses. The function then evaluates them from left to right and returns the first one that is not NULL, making `SELECT COALESCE(column1, column2, column3) FROM table_name;` the correct usage.

Question 5

What will the following SQL statement return?

Accepted Answer

It returns 'Administration' if department_id is 10, 'Marketing' if department_id is 20, and 'Other' for all other values.

Answer

Assuming a `CASE` or `DECODE` statement, the logic evaluates the `department_id` against specified values. If `department_id` is 10, it returns 'Administration'. If it's 20, it returns 'Marketing'. For any other `department_id` value not explicitly listed, the `ELSE` or default clause takes effect, returning 'Other'. This provides a clear conditional mapping for department names.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

1Z0-071 - Oracle Database SQL Certified Associate Practice Test

Free 1Z0-071 Conditional Expressions Questions and Answers