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

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

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

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

Hint

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

Practice

1. What does the CAST() function do in MySQL?

easy

A. It changes the database user permissions.

B. It creates a new table in the database.

C. It converts a value from one data type to another.

D. It deletes rows from a table.

Solution

1. Step 1: Understand the purpose of CAST()

   The CAST() function is used to change the data type of a value, for example from string to integer.

2. Step 2: Compare with other options

   Options A, B, and C describe unrelated database operations, not type conversion.

3. Final Answer:

   It converts a value from one data type to another. -> Option C

4. Quick Check:

   CAST() = type conversion [OK]

Hint: CAST() changes data types, not structure or permissions [OK]

Common Mistakes:

• Thinking CAST() creates or deletes tables
• Confusing CAST() with permission commands
• Assuming CAST() modifies data values without type change

2. Which of the following is the correct syntax to convert a string '123' to an integer in MySQL?

easy

A. CAST('123' AS SIGNED)

B. CONVERT('123', TO INTEGER)

C. CAST('123' TO INTEGER)

D. CONVERT('123' AS INT)

Solution

1. Step 1: Recall correct CAST syntax

   In MySQL, the correct syntax is CAST(value AS type), so CAST('123' AS SIGNED) is valid.

2. Step 2: Check other options for syntax errors

   Options A, B, and D use incorrect syntax: A uses 'TO' instead of 'AS', B and D misuse CONVERT syntax, which is invalid in MySQL.

3. Final Answer:

   CAST('123' AS SIGNED) -> Option A

4. Quick Check:

   CAST(value AS type) = correct syntax [OK]

Hint: Use CAST(value AS type) format for conversions [OK]

Common Mistakes:

• Using TO instead of AS in CAST
• Using CONVERT with wrong syntax
• Mixing syntax from other SQL dialects

3. What is the result of this query?

SELECT CAST('2023-06-15' AS DATE) + INTERVAL 5 DAY AS new_date;

medium

A. 2023-06-20

B. 2023-06-10

C. 2023-06-15 00:00:00

D. Error: Invalid date operation

Solution

1. Step 1: Convert string to DATE type

   CAST('2023-06-15' AS DATE) converts the string to a date value representing June 15, 2023.

2. Step 2: Add 5 days interval

   Adding INTERVAL 5 DAY moves the date forward by 5 days, resulting in June 20, 2023.

3. Final Answer:

   2023-06-20 -> Option A

4. Quick Check:

   Date + 5 days = 2023-06-20 [OK]

Hint: Add intervals to dates after casting string to DATE [OK]

Common Mistakes:

• Forgetting to cast string to DATE before adding interval
• Adding interval to string without conversion
• Expecting time part in result

4. Identify the error in this query:

SELECT CONVERT('abc' AS UNSIGNED);

medium

A. Returns NULL

B. No error, returns 0

C. Runtime error: cannot convert 'abc' to number

D. Syntax error: wrong CONVERT syntax

Solution

1. Step 1: Check CONVERT syntax

   CONVERT(value, type) is correct syntax in MySQL, but 'AS' keyword is invalid here, so syntax error would occur.

2. Step 2: Analyze actual query

   The query uses CONVERT('abc' AS UNSIGNED), which is invalid syntax and causes syntax error, but if corrected to CONVERT('abc', UNSIGNED), it returns 0 because 'abc' cannot be converted to a number.

3. Final Answer:

   Syntax error: wrong CONVERT syntax -> Option D

4. Quick Check:

   CONVERT() syntax must be CONVERT(value, type) [OK]

Hint: Use CONVERT(value, type) without AS keyword [OK]

Common Mistakes:

• Using AS keyword inside CONVERT()
• Expecting NULL instead of 0 for invalid numeric conversion
• Confusing CONVERT syntax with CAST

5. You have a table sales with a column amount stored as VARCHAR. You want to calculate the total sales as a number. Which query correctly converts and sums the amounts?

hard

A. SELECT SUM(amount) FROM sales;

B. SELECT SUM(CAST(amount AS DECIMAL(10,2))) FROM sales;

C. SELECT SUM(CONVERT(amount AS CHAR)) FROM sales;

D. SELECT SUM(CAST(amount AS DATE)) FROM sales;

Solution

1. Step 1: Understand data type of amount

   amount is VARCHAR, so summing directly treats values as strings, causing wrong results or errors.

2. Step 2: Convert amount to numeric type before summing

   CAST(amount AS DECIMAL(10,2)) converts string to decimal number, allowing correct sum calculation.

3. Step 3: Check other options

   SELECT SUM(amount) FROM sales; sums strings (incorrect), C converts to CHAR (string again), D converts to DATE (invalid for amounts).

4. Final Answer:

   SELECT SUM(CAST(amount AS DECIMAL(10,2))) FROM sales; -> Option B

5. Quick Check:

   Convert strings to numbers before SUM [OK]

Hint: CAST strings to DECIMAL before SUM to avoid errors [OK]

Common Mistakes:

• Summing VARCHAR without conversion
• Casting to wrong type like DATE or CHAR
• Using CONVERT with wrong syntax