SQL Reference - DECIMAL

Example

CREATE TABLE Products (
    ProductID INT,
    Price DECIMAL(5, 2)
);

INSERT INTO Products (ProductID, Price)
VALUES (1, 23.45),
       (2, 150.99),
       (3, 100.00);

SELECT * FROM Products;

Output

+-----------+-------+
| ProductID | Price |
+-----------+-------+
|         1 | 23.45 |
|         2 | 150.99|
|         3 | 100.00|
+-----------+-------+

Explanation

Here, DECIMAL(5,2) is used to store numbers with 5 digits, 2 of which are decimals. This is used in the ‘Price’ column. The output shows the products with their associated prices. With DECIMAL type, calculations are more precise than with FLOAT or DOUBLE. The example creates a ‘Products’ table, inserts rows into it, and then performs a SELECT query to display the contents.

Example

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    quantity integer NOT NULL,
    item_price DECIMAL(5, 2) NOT NULL
);

INSERT INTO orders(quantity, item_price)
VALUES (5, 19.99);

Output

INSERT 0 1

Explanation

In the above code, a table “orders” is created with ‘order_id’, ‘quantity’, and ‘item_price’ columns. The ‘item_price’ column is defined with DECIMAL(5, 2) type, meaning it can store a number with a total of 5 digits, where 2 digits can be after the decimal point. An example row is inserted into the “orders” table, with ‘quantity’ as 5, and ‘item_price’ as 19.99.

Example

CREATE TABLE Orders (
    OrderID INT,
    Price DECIMAL(10,2)
);

INSERT INTO Orders
VALUES (1, 23.45);

Output

SELECT * FROM Orders;

OrderID | Price
--------|-------
1       | 23.45

Explanation

In the example, the DECIMAL data type is used to define a column in a table. It is set with precision 10 and scale 2, meaning that it can store numbers with up to 10 digits, two of which are decimals. A record is inserted into the table where the price column is 23.45, which corresponds to the declared DECIMAL(10,2) type. This means the column ‘Price’ can hold numbers with up to 10 digits, including 2 digits after decimal point.

Example

CREATE TABLE Product (
    ID INT PRIMARY KEY,
    Price DECIMAL(5, 2)
);

INSERT INTO Product (ID, Price)
VALUES (1, 12.50);

Output

Table PRODUCT created.

1 row inserted.

Explanation

In the above examples, DECIMAL(5,2) is used to specify a decimal in an Oracle SQL statement. The number (5,2) after ‘DECIMAL’ represents the precision (total number of digits) and scale (number of digits after the decimal point) of the decimal, respectively. Thus, a decimal with 5 total digits, 2 of which are after the decimal point, is inserted into the table ‘Product’.

Example

CREATE TABLE Products (
    Product_ID INTEGER PRIMARY KEY,
    Product_price DECIMAL(5,2)
);

INSERT INTO Products (Product_price)
VALUES (12.99);

SELECT * FROM Products;

Output

Product_ID | Product_price
1          | 12.99

Explanation

In this example, a table called Products is created with two columns: Product_ID and Product_price. The Product_price column is assigned the DECIMAL data type with a precision of 5 and a scale of 2, which allows us to store numbers with up to 5 digits, up to 2 of them being after the decimal point. A value of 12.99 is then inserted into the Products table and all rows are selected to display the data inside.