Using NVL and DECODE — Exercise

Objective: Complete two Oracle SQL expressions using the NVL and DECODE functions.

Exercise Scoring

This exercise is worth 15 points:

• Six points for the correct NVL expression.
• Six points for the correct DECODE expression.
• Three points for briefly explaining how the two expressions handle unmatched or null values.

Relevant Schema

Both questions use the PRODUCT table from the PETSTORE course-project schema.

Instructions

Complete the missing expression in each query. Submit both expressions and briefly explain how each function determines its return value. SQL keywords and function names are not case-sensitive.

Question 1: Replace a Null Date

Complete the query with an NVL expression that returns LAST_UPDATE_DATE when it contains a value. When it is null, return the date January 1, 2000, using the ANSI date literal DATE '2000-01-01'.

SELECT product_id,
       ______________________________ AS effective_update_date
FROM   product
WHERE  product_id < 10
ORDER  BY product_id;

Question 2: Translate the Pet Flag

Complete the query with a DECODE expression that returns PET when PET_FLAG contains Y. For every other value, including null, return NOT A PET.

SELECT product_id,
       ______________________________ AS product_type
FROM   product
WHERE  product_id < 10
ORDER  BY product_id;