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

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

Answer

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

Answer

CASE salary WHEN > 50000 THEN 'High' END

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

The statement assigns grade 'A' to employees with a salary greater than 5000.

Answer

The statement assigns grade 'B' to employees with a salary greater than 10000.

Answer

The statement will produce an error.

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

It is used only for numeric data types.

Answer

It cannot handle NULL values.

Answer

It always returns a numeric value.

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

SELECT COALESCE(column1) FROM table_name;

Answer

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

Answer

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

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

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

Answer

It returns 'Other' for all department_id values.

Answer

It will produce an error.