Conditional Expressions Flashcards

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

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

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.

What will be the output of the following SQL statement?

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.

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.

Which of the following is true about the DECODE function?

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

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.

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

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

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.

What will the following SQL statement return?

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

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.