NULLIF function in MySQL - Mini Project: Build & Apply

Using the NULLIF Function in MySQL

📖 Scenario: You are managing a small online store database. You want to handle cases where the discount price is the same as the original price by showing NULL instead of the discount price.

🎯 Goal: Build a MySQL query that uses the NULLIF function to return NULL when the discount price equals the original price, otherwise return the discount price.

📋 What You'll Learn

Create a table called products with columns id, name, price, and discount_price

Insert three products with specific prices and discount prices

Write a SELECT query using NULLIF(discount_price, price) to show NULL when discount price equals price

Complete the query to select id, name, price, and the discount_price or NULL using NULLIF

💡 Why This Matters

🌍 Real World

Online stores often need to show discounts only when they differ from the original price. NULLIF helps handle this cleanly in queries.

💼 Career

Understanding NULLIF is useful for database developers and analysts to write clear queries that handle special cases like equal values.

1

Create the products table

Create a table called products with columns id as INT primary key, name as VARCHAR(50), price as DECIMAL(10,2), and discount_price as DECIMAL(10,2).

MySQL

-- Create the products table with the specified columns
-- Your code here

Need a hint?

Use CREATE TABLE with the exact column names and types.

2

Insert product data

Insert three rows into products with these exact values: (1, 'Laptop', 1000.00, 900.00), (2, 'Mouse', 25.00, 25.00), and (3, 'Keyboard', 45.00, 40.00).

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  discount_price DECIMAL(10,2)
);
-- Insert the three products with exact values below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with all three rows.

3

Write SELECT query using NULLIF

Write a SELECT query to get id, name, price, and use NULLIF(discount_price, price) as discount_price from the products table.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  discount_price DECIMAL(10,2)
);

INSERT INTO products (id, name, price, discount_price) VALUES
(1, 'Laptop', 1000.00, 900.00),
(2, 'Mouse', 25.00, 25.00),
(3, 'Keyboard', 45.00, 40.00);

-- Write SELECT query using NULLIF below
-- Your code here

Need a hint?

Use NULLIF(discount_price, price) in the SELECT list with alias discount_price.

4

Complete the query with ordering

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

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  discount_price DECIMAL(10,2)
);

INSERT INTO products (id, name, price, discount_price) VALUES
(1, 'Laptop', 1000.00, 900.00),
(2, 'Mouse', 25.00, 25.00),
(3, 'Keyboard', 45.00, 40.00);

SELECT id, name, price, NULLIF(discount_price, price) AS discount_price FROM products
-- Add ORDER BY id below
-- Your code here

Need a hint?

Use ORDER BY id at the end of the query to sort results.