Oracle SQL Fundamentals/Conditional Expressions
This lesson introduces conditional expressions.
Objectives and Skills
editObjectives and skills for the conditional expressions portion of Oracle SQL Fundamentals I certification include:[1]
- Using Conversion Functions and Conditional Expressions
- Apply conditional expressions in a SELECT statement
Readings
edit- Read Wikipedia: Null (SQL).
- Read Oracle: NVL.
- Read Oracle: NVL2.
- Read Oracle: NULLIF.
- Read Oracle: COALESCE.
- Read Oracle: DECODE.
- Read Oracle: CASE Statement.
Multimedia
editActivities
edit- 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;
- Run the following query:
- 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;
- Run the following query:
- 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
edit- NVL(expression, replacement) replaces a NA value or an empty string with a string.[2]
- 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.[3]
- 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.[4]
- 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.[5]
- 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.[6]
- Simple CASE: CASE variable WHEN value THEN ... WHEN value THEN ... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.[7]
- Searched CASE: CASE WHEN ... THEN ... WHEN ... THEN .... [...] [ELSE ...] END; chooses from a sequence of conditions and runs a corresponding statement.[8]
See Also
editReferences
edit