RelationalDBDesign RelationalDBDesign


Data Manipulation   «Prev  Next»

Decode NVL Functions - Exercise

Course project: Writing a query using DECODE and NVL


Objective: Create a query that incorporates both DECODE and NVL functions.

Exercise scoring

This auto-scored exercise is worth 15 points. When you have completed the simulation, click the Submit button to receive full credit and to review the exercise solution.

Download files

If you have not done so, download the PETSTORE schema files from the following link so you can view the database diagram and the data in the course project.
Pet Store Diagram



Instructions

In the following simulation exercise, you will be asked to perform a task or set of tasks you have learned in the previous lesson. The instructions will tell you what you need to do at each step but will not tell you exactly how.
There is a correct way to proceed through each screen of the simulation. If at any point you find that you cannot proceed, go back and re-read the lesson and then navigate through the simulation again.
You are going to see two queries. You must complete each query with a correctly coded function. The first query requires the NVL function. The second requires the DECODE function.
Review the query presented in each screen. The second line of each query is missing. Review the results listed in the question. Your job is to type in the missing part of the query using a function and a column as needed to get the results shown.

Hints

· Use the pet store data from the course project.
· Use ALL CAPITAL LETTERS in your code.

Here is an example question.
Query:
SELECT PRODUCT_ID, PKG
FROM PRODUCT 
WHERE PRODUCT_ID < 10 
Results: 
PRODUCT_ID PKG 
                ---------- ------------------ 
                 2 -1
                 3 -1
                 4 21
                 5 -1
                 6 20
                 7 20
                 8 -1
                 9 -1 

Your answer:
NVL(PACKAGE_ID,-1)

Completing your exercise

When you have completed the exercise, click the Submit button.