14 Conditional Expressions

• ·         Compares an expression to each of the search values-DECODE
• ·         An if-then-else expression whose value depends on the truth-value of a Boolean expression.-conditional expression
• ·         Implements conditional processing within a SQL statement; it meets the ANSI standard.-CASE

1.  From the DJs on Demand d_songs table, create a query that replaces the 2-minute songs with “shortest” and the 10-minute songs with “longest”. Label the output column “Play Times”.

SELECT title,

CASE

WHEN TO_NUMBER(REPLACE(NVL(duration,'0 min'), ' min', '')) = 2 THEN 'Shortest'

WHEN TO_NUMBER(REPLACE(NVL(duration,'0 min'), ' min', '')) = 10 THEN 'Longest'

ELSE NVL(duration,'0 min')

END

as "Play Times"

FROM d_songs;

2.  Use the employees table and CASE expression to decode the department id. Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions:

If the department id is 10 then 1.25 * salary

If the department id is 90 then 1.5 * salary

If the department id is 130 then 1.75 * salary

Otherwise, display the old salary.

SELECT NVL(TO_CHAR(department_id), 'none') department_id , last_name, NVL(salary,0) salary,

CASE department_id

WHEN 10 THEN 1.25*NVL(salary,0)

WHEN 90 THEN 1.5*NVL(salary,0)

WHEN 130 THEN 1.75*NVL(salary,0)

ELSE NVL(salary,0)

END

as "New Salary"

FROM employees;

3.  Display the first name, last name, manager ID, and commission percentage of all employees in departments 80 and 90. In a 5th column called “Review”, again display the manager ID. If they don’t have a manager, display the commission percentage. If they don’t have a commission, display 99999.

SELECT first_name, last_name, manager_id, commission_pct,

CASE

WHEN commission_pct IS NULL and manager_id IS NULL THEN 99999

WHEN manager_id IS NULL THEN commission_pct

ELSE manager_id

END

as "Review"

FROM employees

WHERE department_id in (80, 90);

OR

SELECT first_name, last_name, manager_id, commission_pct,

COALESCE(manager_id,commission_pct ,99999)

as "Review"

FROM employees

WHERE department_id in (80, 90);