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  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.
 * 2) Run the following query:
 * 3) Run the following query:
 * 4) Run the following query:
 * 5) Run the following query:
 * 6) Test conditional expressions using HR data.
 * 7) Run the following query:
 * 8) Run the following query:
 * 9) Run the following query:
 * 10) Test null conditions using OE data.
 * 11) Use NLV to display when a customer has no account manager.
 * 12) Use NLV2 to display when an order has no sales rep.
 * 13) Use NULLIF to clear order mode for a direct order.
 * 14) Use COALESCE to select from a series of null and non-null values using DUAL.
 * 15) Test conditional expressions using OE data.
 * 16) Use DECODE to assign values to order status.
 * 17) Use CASE to display different messages based on customer status.
 * 18) 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.