# Oracle SQL Fundamentals/Conditional Expressions

This lesson introduces conditional expressions.

## Objectives and Skills

Objectives and skills for the conditional expressions portion of Oracle SQL Fundamentals I certification include:
* Using Conversion Functions and Conditional Expressions
** Apply conditional expressions in a SELECT statement

## Readings

* Read Wikipedia: Null (SQL).
* Read Oracle: NVL.
* Read Oracle: NVL2.
* Read Oracle: NULLIF.
* Read Oracle: COALESCE.
* Read Oracle: DECODE.
* Read Oracle: CASE Statement.

## Multimedia

* YouTube: Oracle SQL Tutorial - Querying a table - Part 10(f) - Single Row Null Functions

## Activities

* Test null conditions using HR data.
** Run the following query: SELECT LAST_NAME, NVL(COMMISSION_PCT, 0) AS COMMISSION FROM EMPLOYEES;
** Run the following query: SELECT LAST_NAME, NVL2(TO_CHAR(COMMISSION_PCT), TO_CHAR(COMMISSION_PCT, '0.00'), ' None') AS COMMISSION FROM EMPLOYEES;
** Run the following query: SELECT NULLIF(HIRE_DATE, SYSDATE) AS NULLS_CURRENT_DATE FROM EMPLOYEES;
** Run the following query: SELECT COALESCE(NULL, NULL, 'Not Null', NULL) AS "COALESCE" FROM DUAL;
* Test conditional expressions using HR data.
** Run the following query: SELECT LAST_NAME, DECODE(SUBSTR(PHONE_NUMBER, 1, 3), '515', 'Iowa', '590', 'Unassigned', '603', 'New Hampshire', '650', 'California', '011', 'International') AS AREA FROM EMPLOYEES;
** Run the following query: SELECT LAST_NAME, CASE SUBSTR(PHONE_NUMBER, 1, 3) WHEN '515' THEN 'Iowa' WHEN '590' THEN 'Unassigned' WHEN '603' THEN 'New Hampshire' WHEN '650' THEN 'California' WHEN '011' THEN 'International' END AS AREA FROM EMPLOYEES;
** Run the following query: SELECT LAST_NAME, CASE WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 1 THEN 'Rookie' WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 5 THEN 'Junior' WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12 < 10 THEN 'Senior' ELSE 'Master' END AS STATUS FROM EMPLOYEES;
* Test null conditions using OE data.
** Use NLV to display when a customer has no account manager.
** Use NLV2 to display when an order has no sales rep.
** Use NULLIF to clear order mode for a direct order.
** Use COALESCE to select from a series of null and non-null values using DUAL.
* Test conditional expressions using OE data.
** Use DECODE to assign values to order status.
** Use CASE to display different messages based on customer status.
** Use CASE WHEN for order totals >= 10000 to display a message with a special premium support phone number they may call if they need any assistance.

## Lesson Summary

* NVL(expression, replacement) replaces a NA value or an empty string with a string.
* NVL2(expr1, expr2, expr3) returns one value when the value of a specified expression is not NA or an empty string, or another value when the value of the specified expression is an empty string or NA.
* NULLIF(expr1, expr2) compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.
* COALESCE(expr, expr, [...]) returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.
* DECODE(expr , search, result [, search , result]... [, default]) compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
* Simple CASE: CASE variable WHEN value THEN ... WHEN value THEN ... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.
* Searched CASE: CASE WHEN ... THEN ... WHEN ... THEN .... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.

## See Also

* Oracle: Database 2 Day Developers Guide - Selecting Table Data