Decimal and floating-point types in MySQL - Mini Project: Build & Apply

Working with Decimal and Floating-Point Types in MySQL

📖 Scenario: You are managing a small online store's database. You need to store product prices accurately, including cents, and also store product ratings which can have decimal values.

🎯 Goal: Create a MySQL table to store product information with prices using the DECIMAL type for exact values and ratings using the FLOAT type for approximate decimal values.

📋 What You'll Learn

Create a table named products with columns for id, name, price, and rating.

Use DECIMAL(10,2) for the price column to store prices with two decimal places.

Use FLOAT for the rating column to store approximate decimal values.

Set id as the primary key and auto-increment it.

Insert sample data with exact prices and decimal ratings.

💡 Why This Matters

🌍 Real World

Storing prices and ratings accurately is important for e-commerce and financial applications.

💼 Career

Understanding how to use decimal and floating-point types is essential for database developers and data analysts working with monetary and measurement data.

1

Create the products table with id and name columns

Write a CREATE TABLE statement to create a table named products with an id column as INT and primary key with auto-increment, and a name column as VARCHAR(100).

MySQL

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);
-- Your code here

Need a hint?

Use CREATE TABLE products and define id INT PRIMARY KEY AUTO_INCREMENT and name VARCHAR(100).

2

Add price and rating columns with correct data types

Modify the products table creation to add a price column with type DECIMAL(10,2) and a rating column with type FLOAT.

MySQL

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    rating FLOAT
);
-- Your code here

Need a hint?

Add price DECIMAL(10,2) and rating FLOAT columns separated by commas.

3

Insert sample products with prices and ratings

Write an INSERT INTO products statement to add these products: ('Laptop', 999.99, 4.5) and ('Headphones', 199.95, 4.2) with columns name, price, and rating.

MySQL

INSERT INTO products (name, price, rating) VALUES
    ('Laptop', 999.99, 4.5),
    ('Headphones', 199.95, 4.2);
-- Your code here

Need a hint?

Use INSERT INTO products (name, price, rating) VALUES followed by the two product tuples.

4

Query the products to see prices and ratings

Write a SELECT statement to retrieve all columns from the products table.

MySQL

SELECT * FROM products;
-- Your code here

Need a hint?

Use SELECT * FROM products; to see all data.