Numeric data types in SQL

The numeric data types in SQL are described below.

Exact numeric data types are used to store exact values and perform accurate calculations. Values are stored as literal representations of a number’s value. For example, INT, TINYINT, SMALLINT, MEDIUMINT, NUMERIC, etc.
Approximate numeric data types are used to save up storage cost and perform fast calculations. For example, REAL, DOUBLE, etc.

Integer

Type	Description
TINYINT	If signed, -128 to 127 is the allowable range. If unsigned, 0 to 255 is the allowable range. (1-byte storage) Example: Can be used in a bank database.
SMALLINT	If signed, -128 to 127 is the allowable range. If unsigned, 0 to 255 is the allowable range. Can be used in a bank database. (1-byte storage) Example: Can be used in a bank database.
MEDIUMINT	If signed, -8388608 to 8388607 is the allowable range. If unsigned, 0 to 16777215 is the allowable range. (3-byte storage) Example: Can be used in a bank database.
INT	If signed, -2147483648 to 2147483647 is the allowable range. If unsigned, 0 to 4294967295 is the allowable range. (4-byte storage) Example: Can be used in a bank database.
BIGINT	If signed, -9223372036854775808 to 9223372036854775807 is the allowable range. If unsigned, 0 to 18446744073709551615 is the allowable range. (8-byte storage) Example: Can be used in a bank database.

Floating point

Type	Description
FLOAT(m,d)	m: Total display length (default value = 10) d: Number of decimal places (default value = 2) The floating-point number cannot be unsigned and decimal precision can go to 24 places. (2-byte storage) Example: Can be used in a scientific research database.
DOUBLE(m,d) or REAL(m,d)	m: Total display length (default value = 16) d: Number of decimal places (default value = 4) The double-precision floating-point number cannot be unsigned and decimal precision can go to 53 places. (8-byte storage) Example: Can be used in a scientific research database.
DECIMAL(m,d) or NUMERIC(m,d)	m: Total display length d: Number of decimal places The unpacked floating-point number cannot be unsigned and in this, each decimal corresponds to one byte. Example: Can be used in a scientific research database.

DELIMITER &&
CREATE PROCEDURE numeric_datatypes()
BEGIN
  -- Integer
	DECLARE A TINYINT DEFAULT 122;
  DECLARE B SMALLINT DEFAULT 31767;
  DECLARE C MEDIUMINT DEFAULT -8288607;
  DECLARE D INT DEFAULT -2146483648;
  DECLARE E BIGINT DEFAULT 9223372036854765807;
  -- Floating point
  -- total digits 7 including decimals and upto 2 decimal places
  DECLARE F FLOAT(7,2) DEFAULT 12345.78909; 
  -- total digits 8 including decimals and upto 3 decimal places
  DECLARE G DOUBLE(8,3) DEFAULT 12345.78909;
  -- total digits 7 including decimals and upto 3 decimal places
  DECLARE H DECIMAL(7,3) DEFAULT 123.45;
  -- bool
  DECLARE I BOOL DEFAULT False;
  -- bit
  DECLARE J BIT DEFAULT 0;
  -- miscellaneous
  -- storing float value to int
  -- it will be rounded to integer
  DECLARE K INT DEFAULT 12345.78909; 
  SELECT A;
  SELECT B;
  SELECT C;
  SELECT D;
  SELECT E;
  SELECT F;
  SELECT G;
  SELECT H;
  SELECT I;
  SELECT J;
  SELECT K;
END&&
DELIMITER ;
call numeric_datatypes()

Numeric data types in SQL

Integer

Floating point

Boolean

Bit

Code

Type	Description
BOOL OR BOOLEAN	It is used to store true or false only. 1: True 0: False

Type	Description
BIT(m)	It is used to store bit values. m: number of bits per value that has a range of 1 to 64