CAST and CONVERT for type changes in SQL - Mini Project: Build & Apply

Using CAST and CONVERT for Type Changes in SQL

📖 Scenario: You are working with a sales database that stores product prices as text strings. To perform calculations, you need to convert these text prices into numbers.

🎯 Goal: Build a SQL query that converts text prices to numeric values using CAST and CONVERT functions.

📋 What You'll Learn

Create a table called products with columns product_id (integer) and price_text (varchar).

Insert three rows with product IDs 1, 2, 3 and prices '10.50', '20.75', '15.00' as text.

Write a query that uses CAST to convert price_text to a decimal number.

Write a query that uses CONVERT to convert price_text to a decimal number.

💡 Why This Matters

🌍 Real World

Converting data types is common when data is stored as text but needs to be used in calculations or comparisons.

💼 Career

Database developers and analysts often use CAST and CONVERT to ensure data is in the correct format for queries and reports.

1

Create the products table and insert data

Create a table called products with columns product_id as INTEGER and price_text as VARCHAR(10). Insert three rows with product_id values 1, 2, 3 and price_text values '10.50', '20.75', and '15.00'.

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a query using CAST to convert price_text to decimal

Write a SQL query that selects product_id and converts price_text to a decimal number using CAST(price_text AS DECIMAL(5,2)). Name the converted column price_cast.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

-- Write a SELECT query using CAST to convert price_text
-- Your code here

Need a hint?

Use CAST(column AS type) in the SELECT statement.

3

Add a query using CONVERT to convert price_text to decimal

Write a SQL query that selects product_id and converts price_text to a decimal number using CONVERT(DECIMAL(5,2), price_text). Name the converted column price_convert.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

SELECT product_id, CAST(price_text AS DECIMAL(5,2)) AS price_cast
FROM products;

-- Write a SELECT query using CONVERT to convert price_text
-- Your code here

Need a hint?

Use CONVERT(type, column) in the SELECT statement.

4

Complete the queries with ORDER BY clause

Add an ORDER BY product_id clause to both SELECT queries that use CAST and CONVERT to sort the results by product_id.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price_text VARCHAR(10)
);

INSERT INTO products (product_id, price_text) VALUES
(1, '10.50'),
(2, '20.75'),
(3, '15.00');

SELECT product_id, CAST(price_text AS DECIMAL(5,2)) AS price_cast
FROM products
-- Add ORDER BY clause here

SELECT product_id, CONVERT(DECIMAL(5,2), price_text) AS price_convert
FROM products
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY product_id at the end of each SELECT query.