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

1. Read Wikipedia: Null (SQL).
2. Read Oracle: NVL.
3. Read Oracle: NVL2.
4. Read Oracle: NULLIF.
5. Read Oracle: COALESCE.
6. Read Oracle: DECODE.
7. Read Oracle: CASE Statement.

Multimedia

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

Activities

1. Test null conditions using HR data.
   1. Run the following query:
      SELECT LAST_NAME, NVL(COMMISSION_PCT, 0) AS COMMISSION FROM EMPLOYEES;
   2. Run the following query:
      SELECT LAST_NAME, NVL2(TO_CHAR(COMMISSION_PCT), TO_CHAR(COMMISSION_PCT, '0.00'), ' None') AS COMMISSION FROM EMPLOYEES;
   3. Run the following query:
      SELECT NULLIF(HIRE_DATE, SYSDATE) AS NULLS_CURRENT_DATE FROM EMPLOYEES;
   4. Run the following query:
      SELECT COALESCE(NULL, NULL, 'Not Null', NULL) AS "COALESCE" FROM DUAL;
2. Test conditional expressions using HR data.
   1. 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;
   2. 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;
   3. 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;
3. Test null conditions using OE data.
   1. Use NLV to display when a customer has no account manager.
   2. Use NLV2 to display when an order has no sales rep.
   3. Use NULLIF to clear order mode for a direct order.
   4. Use COALESCE to select from a series of null and non-null values using DUAL.
4. Test conditional expressions using OE data.
   1. Use DECODE to assign values to order status.
   2. Use CASE to display different messages based on customer status.
   3. 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

References

← Conversion Functions

Oracle SQL Fundamentals

Aggregating Data →