GREATEST and LEAST in MySQL - Mini Project: Build & Apply

Using GREATEST and LEAST Functions in MySQL

📖 Scenario: You work at a small online store that tracks product prices from different suppliers. You want to find the highest and lowest prices for each product to make better buying decisions.

🎯 Goal: Build a simple MySQL table with product prices from three suppliers. Then write queries using GREATEST and LEAST functions to find the highest and lowest prices for each product.

📋 What You'll Learn

Create a table named product_prices with columns product_name (VARCHAR) and three price columns: price_supplier1, price_supplier2, price_supplier3 (all DECIMAL).

Insert exactly three products with their prices from each supplier.

Write a SELECT query that uses GREATEST to find the highest price among the three suppliers for each product.

Write a SELECT query that uses LEAST to find the lowest price among the three suppliers for each product.

💡 Why This Matters

🌍 Real World

Stores and businesses often compare prices from multiple suppliers to get the best deals. Using GREATEST and LEAST helps quickly find the best and worst prices.

💼 Career

Knowing how to use these functions is useful for data analysts and database developers who work with pricing data or any scenario needing comparisons across columns.

1

Create the product_prices table

Write a MySQL statement to create a table called product_prices with these columns: product_name as VARCHAR(50), and price_supplier1, price_supplier2, price_supplier3 as DECIMAL(6,2).

MySQL

-- Your code here to create the product_prices table

Need a hint?

Use CREATE TABLE with the exact column names and types.

2

Insert product price data

Insert these three products with prices into product_prices: ('Laptop', 900.00, 850.00, 870.00), ('Smartphone', 500.00, 520.00, 510.00), and ('Tablet', 300.00, 310.00, 305.00).

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);
-- Your code here to insert the three products

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Query highest price using GREATEST

Write a SELECT query to show product_name and the highest price among price_supplier1, price_supplier2, and price_supplier3 using the GREATEST function. Name the highest price column highest_price.

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);

INSERT INTO product_prices (product_name, price_supplier1, price_supplier2, price_supplier3) VALUES
('Laptop', 900.00, 850.00, 870.00),
('Smartphone', 500.00, 520.00, 510.00),
('Tablet', 300.00, 310.00, 305.00);

-- Your code here to select product_name and highest_price using GREATEST

Need a hint?

Use GREATEST with the three price columns inside the SELECT statement.

4

Query lowest price using LEAST

Write a SELECT query to show product_name and the lowest price among price_supplier1, price_supplier2, and price_supplier3 using the LEAST function. Name the lowest price column lowest_price.

MySQL

CREATE TABLE product_prices (
  product_name VARCHAR(50),
  price_supplier1 DECIMAL(6,2),
  price_supplier2 DECIMAL(6,2),
  price_supplier3 DECIMAL(6,2)
);

INSERT INTO product_prices (product_name, price_supplier1, price_supplier2, price_supplier3) VALUES
('Laptop', 900.00, 850.00, 870.00),
('Smartphone', 500.00, 520.00, 510.00),
('Tablet', 300.00, 310.00, 305.00);

SELECT product_name, GREATEST(price_supplier1, price_supplier2, price_supplier3) AS highest_price
FROM product_prices;

-- Your code here to select product_name and lowest_price using LEAST

Need a hint?

Use LEAST with the three price columns inside the SELECT statement.