PRECISION

Example

CREATE TABLE employees (
    id INT NOT NULL,
    salary DECIMAL(6,2)
);

INSERT INTO employees (id, salary)
VALUES (1, 5000.99), (2, 3000.20);

SELECT * FROM employees;

Output

+------+---------+
| id   | salary  |
+------+---------+
|  1   | 5000.99 |
|  2   | 3000.20 |
+------+---------+

Explanation

The DECIMAL(6,2) data type used in the salary column declaration denotes a fixed-point number with 6 digits in total, out of which 2 are decimal places. When 5000.99 and 3000.20 are inserted into the salary column of the employees table, it follows this pattern.

Example

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    salary NUMERIC(8,2)
);

INSERT INTO employees (salary) VALUES (123456.78);

SELECT * FROM employees;

Output

 employee_id |  salary
-------------+---------
           1 | 123456.78

Explanation

In the above example, the salary field is specified as NUMERIC(8,2). The NUMERIC type is used to store numbers with a lot of digits. In NUMERIC(p,s), p determines the total number of digits (including digits to the right of the decimal point), and s determines the number of digits after the decimal point. So, NUMERIC(8,2) can store any number with upto 6 digits before the decimal and 2 digits after the decimal.

Example

DECLARE @Value decimal(5,2);
SET @Value = 123.456;
SELECT @Value;

Output

123.46

Explanation

In this instance, the decimal data type is defined with a total precision of 5 and a scale of 2. This means the variable @Value can hold a maximum of 5 digits in total, with 2 of these digits being to the right of the decimal point.

When the number 123.456 is set to @Value, it is rounded to fit the defined precision and scale, resulting in the output 123.46.

Example

CREATE TABLE suppliers
(
  supplier_id number(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  response_rate number(3, 2) NOT NULL,
  cost_ratio number(3, 2) NOT NULL
);

INSERT INTO suppliers
(supplier_id, supplier_name, response_rate, cost_ratio)
VALUES
(10001, 'Distributed Solutions', 0.95, 0.85);

SELECT * FROM suppliers;

Output

SUPPLIER_ID | SUPPLIER_NAME       | RESPONSE_RATE | COST_RATIO
10001       | Distributed Solutions| 0.95         | 0.85

Explanation

In the example above, the response_rate and cost_ratio columns are designed to hold a number in which there can be up to 3 digits in total and 2 of those digits can be decimals. This implements a precision of 3 and a scale of 2 for those field. The structure of the table “suppliers” ensures that both the response_rate and cost_ratio fields can keep consistent and clear numeric data.