The Decimal data type in SQL is used to store numbers with decimal precision. It is commonly used when dealing with financial data or any data that requires accurate decimal calculations. In this tutorial, we will learn how to use the Decimal data type in SQL.
Creating a Table with Decimal Data Type
To create a table with a column using the Decimal data type, you need to specify the precision and scale for the column. The precision represents the total number of digits that can be stored, while the scale represents the number of digits allowed after the decimal point.
Here’s an example:
CREATE TABLE products (
id INT,
name VARCHAR(50),
price DECIMAL(10, 2)
);
In this example, we have created a table called “products” with three columns: “id” of type INT, “name” of type VARCHAR(50), and “price” of type DECIMAL(10, 2). The “price” column can store up to 10 digits in total, with 2 digits allowed after the decimal point.
Inserting Data into a Table with Decimal Data Type
When inserting data into a table with a decimal column, you need to provide the value in the correct format. The value should be within the specified precision and scale.
INSERT INTO products (id, name, price) VALUES (1, 'Product A', 19.99);
In this example, we are inserting a row into the “products” table with an ID of 1, name ‘Product A’, and price of 19.99. Since our “price” column has a scale of 2, we need to provide two digits after the decimal point.
Performing Calculations with Decimal Data Type
One of the advantages of using the Decimal data type is its ability to perform accurate decimal calculations. When performing calculations involving decimal columns, SQL automatically handles the precision and scale for you.
SELECT price * 0.1 AS discount FROM products;
In this example, we are selecting the “price” column from the “products” table and multiplying it by 0.1 to calculate the discount. The result will be displayed with the appropriate precision and scale based on the column definition.
Conclusion
The Decimal data type in SQL is a powerful tool for storing and manipulating decimal numbers with precision. By specifying the precision and scale, you can control how many digits can be stored and how many digits are allowed after the decimal point.
In this tutorial, we learned how to create a table with a decimal column, insert data into it, and perform calculations involving decimal columns. Remember to always consider your data requirements when choosing the appropriate precision and scale for your columns.