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 exercise is worth 15 points. When you have completed the exercise, click the Submit button to receive full credit and to review the exercise result.

Relevant Files: The PETSTORE schema files for the database diagram are shown below.
The diagram above represents an Entity-Relationship Diagram (ERD) for a database system. Below is a detailed analysis of the tables and their relationships:
1. CUSTOMER Table
- Stores customer information.
  - Primary Key: CUST_ID
  - Attributes:
    
    CUST_ID (NUMBER(10)) – Unique identifier for each customer.
    
    FIRSTNAME (VARCHAR2(20)) – Customer’s first name.
    
    LASTNAME (VARCHAR2(25)) – Customer’s last name.
    
    ADDRESS (VARCHAR2(32)) – Customer’s address.
    
    CITY (VARCHAR2(20)) – Customer’s city.
    
    STATE (VARCHAR2(2)) – Customer’s state.
    
    ZIP (VARCHAR2(9)) – Customer’s ZIP code.
2. CUSTOMER_SALE Table
- Stores sales transactions made by customers.
  - Primary Key: SALES_ID
  - Foreign Key: CUST_ID (References CUSTOMER table)
  - Attributes:
    
    SALES_ID (NUMBER(10)) – Unique sales transaction identifier.
    
    CUST_ID (NUMBER(10)) – Links to the CUSTOMER table.
    
    TOTAL_ITEM_AMOUNT (NUMBER(10,2)) – Total cost of items.
    
    TAX_AMOUNT (NUMBER(10,2)) – Tax applied to the sale.
    
    TOTAL_SALE_AMOUNT (NUMBER(10,2)) – Grand total of the sale.
    
    SALES_DATE (DATE) – Date of the transaction.
    
    SHIPPING_HANDLING_FEE (NUMBER(6,2)) – Additional fees for shipping.
3. SALE_ITEM Table
- Stores details of items sold in each transaction.
  - Composite Primary Key: (SALES_ID, PRODUCT_ID)
  - Foreign Keys:
    
    SALES_ID (References CUSTOMER_SALE table)
    
    PRODUCT_ID (References PRODUCT table)
  - Attributes:
    
    SALES_ID (NUMBER(10)) – Refers to a specific sale transaction.
    
    PRODUCT_ID (NUMBER(10)) – Identifies the product sold.
    
    SALE_AMOUNT (NUMBER(10,2)) – Amount for the specific product in that sale.
4. PRODUCT Table
- Stores product-related information.
  - Primary Key: PRODUCT_ID
  - Attributes:
    
    PRODUCT_ID (NUMBER(10)) – Unique identifier for each product.
    
    PRODUCT_NAME (VARCHAR2(30)) – Name of the product.
    
    PACKAGE_ID (NUMBER(10)) – Package identifier (if applicable).
    
    CURRENT_INVENTORY_COUNT (NUMBER(6)) – Stock availability.
    
    STORE_COST (NUMBER(10,2)) – Cost of the product in the store.
    
    SALE_PRICE (NUMBER(10,2)) – Selling price.
    
    LAST_UPDATE_DATE (DATE) – Last time the product details were updated.
    
    UPDATED_BY_USER (VARCHAR2(30)) – Who updated the product details.
    
    PET_FLAG (VARCHAR2(1)) – Indicates if the product is pet-related (e.g., 'Y' for yes, 'N' for no).
5. PET_CARE_LOG Table
- Logs product-related pet care information.
  - Primary Key: PRODUCT_ID
  - Foreign Key: PRODUCT_ID (References PRODUCT table)
  - Attributes:
    
    PRODUCT_ID (NUMBER(10)) – Identifies the product.
    
    LOG_DATETIME (DATE) – Date and time of log entry.
    
    CREATED_BY_USER (VARCHAR2(30)) – User who created the log.
    
    LOG_TEXT (VARCHAR2(500)) – Detailed log entry.
    
    LAST_UPDATE_DATETIME (DATE) – Last modification timestamp.
6. MONTHLY_SALES View
- Represents an aggregated summary of monthly sales.
  - Attributes:
    
    SALES_MONTH (TO_CHAR(SALES_DATE, 'YYYY-MM')) – Extracts the month and year from SALES_DATE.
    
    TOTAL_SALES (SUM(TOTAL_SALE_AMOUNT)) – Aggregates total sales for the month.
Relationships in the Database
1. CUSTOMER → CUSTOMER_SALE (One-to-Many)
  - A customer can have multiple sales transactions.
2. CUSTOMER_SALE → SALE_ITEM (One-to-Many)
  - Each sale transaction can contain multiple products.
3. SALE_ITEM → PRODUCT (Many-to-One)
  - Each sale item corresponds to a specific product.
4. PRODUCT → PET_CARE_LOG (One-to-Many)
  - A product (especially pet-related ones) can have multiple log entries.
Key Insights
- Normalization: The schema is well-structured, ensuring data consistency.
- Sales Analysis: The MONTHLY_SALES view is useful for reporting.
- Inventory Management: The CURRENT_INVENTORY_COUNT in the PRODUCT table helps track stock levels.
- Audit Trail: The UPDATED_BY_USER and LAST_UPDATE_DATETIME fields in tables ensure auditability.
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.