Free 1Z0-071 Conditional Expressions Questions and Answers — Questions and Answers
Question 1: Which of the following is a valid use of the CASE expression in SQL?
- CASE salary WHEN > 50000 THEN 'High' ELSE 'Low' END
- CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END (Correct answer)
- CASE salary > 50000 THEN 'High' ELSE 'Low' END
- CASE salary WHEN > 50000 THEN 'High' END
Correct 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.
Question 2: What will be the output of the following SQL statement?
- The statement assigns grade 'A' to employees with a salary greater than 5000.
- The statement assigns grade 'B' to employees with a salary greater than 10000.
- 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. (Correct answer)
- The statement will produce an error.
Correct 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.
Question 3: Which of the following is true about the DECODE function?
- It is used only for numeric data types.
- It compares an expression to one or more values and returns a corresponding result. (Correct answer)
- It cannot handle NULL values.
- It always returns a numeric value.
Correct 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.
Question 4: How would you use the COALESCE function to return the first non-NULL value from a list of columns?
- SELECT COALESCE(column1, column2, column3) FROM table_name; (Correct answer)
- SELECT COALESCE(column1) FROM table_name;
- SELECT COALESCE(column1 AND column2 AND column3) FROM table_name;
- SELECT COALESCE(column1 OR column2 OR column3) FROM table_name;
Correct 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.
Question 5: What will the following SQL statement return?
- It returns 'Administration' if department_id is 10, 'Marketing' if department_id is 20, and 'Other' for all other values. (Correct answer)
- It returns 'Marketing' if department_id is 10, 'Administration' if department_id is 20, and 'Other' for all other values.
- It returns 'Other' for all department_id values.
- It will produce an error.
Correct 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.
Free 1Z0-071 Conditional Expressions Questions and Answers
Which of the following is a valid use of the CASE expression in SQL?