NULLIF function behavior in PostgreSQL - Mini Project: Build & Apply

Understanding NULLIF Function Behavior in PostgreSQL

📖 Scenario: You are managing a small online store database. Sometimes, the discount applied to a product is the same as the product price, which means the final price should be considered as NULL to indicate no effective price.

🎯 Goal: Build a simple query using the NULLIF function to return NULL when the discount equals the product price, otherwise return the product price.

📋 What You'll Learn

Create a table called products with columns product_id (integer), product_name (text), price (numeric), and discount (numeric).

Insert exactly three rows into products with the following data: (1, 'Pen', 10, 5), (2, 'Notebook', 20, 20), (3, 'Eraser', 5, 0).

Write a SELECT query that uses NULLIF to return final_price which is NULL if price equals discount, otherwise returns price.

Ensure the query returns product_id, product_name, and final_price.

💡 Why This Matters

🌍 Real World

NULLIF is useful in databases to handle cases where certain values should be treated as missing or undefined, such as when discounts equal prices.

💼 Career

Understanding NULLIF helps in writing clean SQL queries for data cleaning, reporting, and business logic implementation in real-world database jobs.

1

Create the products table and insert data

Create a table called products with columns product_id as integer, product_name as text, price as numeric, and discount as numeric. Then insert these exact rows: (1, 'Pen', 10, 5), (2, 'Notebook', 20, 20), and (3, 'Eraser', 5, 0).

PostgreSQL

-- Create the products table and insert the three rows below
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Add a SELECT query skeleton

Write a SELECT query that selects product_id and product_name from the products table. Do not add any other columns yet.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

-- Write a SELECT query to get product_id and product_name from products
-- Your code here

Need a hint?

Use SELECT product_id, product_name FROM products to get these columns.

3

Use NULLIF to calculate final_price

Modify the SELECT query to add a column called final_price that uses NULLIF(price, discount) to return NULL when price equals discount, otherwise returns price.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

-- Add final_price column using NULLIF(price, discount) in the SELECT query
-- Your code here

Need a hint?

Use NULLIF(price, discount) AS final_price in the SELECT clause.

4

Complete the query with ordering

Add an ORDER BY product_id clause at the end of the SELECT query to sort the results by product_id.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price NUMERIC,
    discount NUMERIC
);

INSERT INTO products (product_id, product_name, price, discount) VALUES
(1, 'Pen', 10, 5),
(2, 'Notebook', 20, 20),
(3, 'Eraser', 5, 0);

SELECT product_id, product_name, NULLIF(price, discount) AS final_price FROM products
-- Add ORDER BY product_id here
-- Your code here

Need a hint?

Use ORDER BY product_id at the end of the query.