Type casting and conversion in MySQL - Mini Project: Build & Apply

Type Casting and Conversion in MySQL

📖 Scenario: You are managing a small online store database. Some product prices are stored as strings, but you need to perform calculations on them. To do this, you must convert these string prices into numbers.

🎯 Goal: Learn how to convert string data to numeric types in MySQL using type casting and conversion functions.

📋 What You'll Learn

Create a table with product names and prices stored as strings

Add a configuration variable to specify the conversion type

Write a query that converts the string prices to numeric values

Complete the query to calculate the total price of all products

💡 Why This Matters

🌍 Real World

In real databases, data may be stored as strings but need conversion for calculations, reporting, or filtering.

💼 Career

Database developers and analysts often convert data types to ensure correct data processing and accurate results.

1

Create the products table with string prices

Create a table called products with two columns: product_name as VARCHAR(50) and price_str as VARCHAR(10). Insert these exact rows: ('Pen', '1.20'), ('Notebook', '2.50'), ('Eraser', '0.75').

MySQL

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

Need a hint?

Use CREATE TABLE with VARCHAR columns and INSERT INTO with the exact values.

2

Set the conversion type variable

Create a variable called conversion_type and set it to the string 'DECIMAL(5,2)' to specify the numeric type for conversion.

MySQL

CREATE TABLE products (
  product_name VARCHAR(50),
  price_str VARCHAR(10)
);

INSERT INTO products (product_name, price_str) VALUES
('Pen', '1.20'),
('Notebook', '2.50'),
('Eraser', '0.75');

-- Create the conversion_type variable
-- Your code here

Need a hint?

Use SET to create a user-defined variable with the exact name and value.

3

Convert string prices to numeric values

Write a SELECT query that retrieves product_name and converts price_str to a numeric value using CAST(price_str AS DECIMAL(5,2)).

MySQL

CREATE TABLE products (
  product_name VARCHAR(50),
  price_str VARCHAR(10)
);

INSERT INTO products (product_name, price_str) VALUES
('Pen', '1.20'),
('Notebook', '2.50'),
('Eraser', '0.75');

SET @conversion_type = 'DECIMAL(5,2)';

-- Select product_name and cast price_str to numeric
-- Your code here

Need a hint?

Use CAST with the exact type DECIMAL(5,2) to convert price_str.

4

Calculate total price using converted values

Write a SELECT query that calculates the total sum of all prices converted to DECIMAL(5,2) using SUM(CAST(price_str AS DECIMAL(5,2))) and name the result total_price.

MySQL

CREATE TABLE products (
  product_name VARCHAR(50),
  price_str VARCHAR(10)
);

INSERT INTO products (product_name, price_str) VALUES
('Pen', '1.20'),
('Notebook', '2.50'),
('Eraser', '0.75');

SET @conversion_type = 'DECIMAL(5,2)';

SELECT product_name, CAST(price_str AS DECIMAL(5,2)) AS price_num FROM products;

-- Calculate the total price using SUM and CAST
-- Your code here

Need a hint?

Use SUM with CAST to add all converted prices and alias as total_price.