Computer systems use internal registers to performs math functions.
These registers are typically 32-bit, 64-bit, 128-bit, or 256-bit in length.
REAL gives a single-precision floating-point number (the size of one register).

> The actual precision of REAL depends on the RDBMS vendor’s implementation.

> Since it is a floating-point number expressed in scientific notation, it is an approximate number and may not be accurate to the last digit.

> It can be negative, zero, or positive.

> If you have a choice between exact (DECIMAL or NUMERIC) or approximate (FLOAT, REAL, or DOUBLE PRECISION), use the exact data type because you get completely accurate numbers and it processes faster.

> SQL:2003 introduced REAL, DOUBLE PRECISION, and FLOAT(precisionbits) data types.
> Approximate data type should be used only for very extreme ranges of numeric values.

FLOAT is a floating point number expressed in scientific notation. It is a decimal number multiplied by an integer power of 10. For example: 4.5E2 = 4.5 x 10 = 450. The mantissa (coefficient) is the portion that expresses the significant digits (here it’s 4.5). An upper case E is the symbol for the exponent (here it’s 2 for 10 to the 2nd power). The mantissa and exponent can also be negative. For example: -4.5E-2 = -4.5 x 10 = -0.045.

Example:    COL1 FLOAT(24)

COL1 can have the precision of 24 bits or ten million significant digits.

Definition  Possible Values                    Example
FLOAT(12)    12 bits or 3 significant digits   3.14E2 = 314
FLOAT(24)    24 bits or 7 significant digits   5.280796E0 = 5.280796
FLOAT(34)    34 bits or 10 significant digits  3.297249382E-3 = 0.003297249382
FLOAT(50)    50 bits or 15 significant digits  1.45732792216439E11
FLOAT(100)  100 bits or 30 significant digits

> Multiply the bits precision by 0.30103 to get the decimal precision.
> Multiply the decimal precision by 3.32193 to get the binary precision.
> For most RDBMS systems the range is -5.4E-79 to +7.2E+75.
> If precisionbits is 1 to 21, then the column value is 5 bytes long.
    If precisionbits is 22 to 53, then the column value is 9 bytes long.

Approximate numeric data is coded by the RDBMS vendors as follows:

Some numeric values are of such a very large or very small value (many magnitudes) that the computer registers can not contain the entire value. Depending on the computer system, registers are typically 32-bit, 64-bit, 128-bit, or 256-bit in length. Usually, in these cases, exactness is not necessary and a close approximation is acceptable.

> The value can be negative, zero, or positive.

> It is a floating-point number expressed in scientific notation.

> SQL:2003 introduced REAL, DOUBLE PRECISION, and FLOAT(precisionbits) data types.
> Systems that allow REAL gives a single-precision floating-point number.
> Systems that allow DOUBLE PRECISION gives a double-precision floating-point number, so it is twice the precision of a REAL definition.
> The actual precision of REAL and DOUBLE PRECISION depends on the RDBMS vendor’s implementation.
> Using FLOAT rather than REAL or DOUBLE PRECISION makes moving your database to a different computer easier, because the FLOAT data type allows you to specify precision.
> If you have a choice between exact DECIMAL (or NUMERIC) or approximate FLOAT (or REAL), use the exact data type, because you get completely accurate numbers and it processes faster. Approximate data type should be used only for very extreme ranges of numeric values.
> PostgreSQL converts FLOAT to DOUBLE PRECISION.

DECIMAL can be an integer or a decimal number.
An integer is a whole number expressed without a decimal number such as -32, 0, or 415.
A decimal number has digits to the right of the decimal point such as -32.4, 0.0, 765.0 or 0.0000627.
In mathematical terms, this number has a precision (total) and a scale (fraction).
The precision (total) is the total or maximum number of digits, both to the right and the left of the decimal point.
The scale (fraction) is the number of digits to the right of the decimal point.
The total can be any number that is 1 or greater. The fraction can be zero or greater and can not exceed the total.
If scale (fraction) is omitted, it defaults to zero.
You can represent a whole number with a fraction of zero, but you really should use INTEGER instead.NUMERIC(total,fraction) is the same as DECIMAL and DEC. In this course, we use DECIMAL.

 

Example: COL1 DECIMAL(8,2)

COL1 can have any value from -999,999.99 through 0.00 to 999,999.99.

 

Definition     Value
DECIMAL(8)     836
DECIMAL(8,0)   836
DECIMAL(8,1)   835.8
DECIMAL(8,2)   835.75
DECIMAL(8,3)   835.754
DECIMAL(4,1)   835.8
DECIMAL(4,2)   This definition is for 2 integers and 2 decimal places.
               The value 835.754 exceeds the precision of 2 integers.
               The fraction can be truncated. The integer can not be truncated.

BIGINT is a data type that can contain a whole number from a small number up to a very, very large integer.

 

Example: COL1 BIGINT

COL1 can have any integer value.

 

Notice that the keyword, BIGINT, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

For most DBMS systems, BIGINT is a 64-bit value in the quintillions (vastly large).

INTEGER is a data type that can contain a whole number in the range of -2,147,483,648 to +1,147,483,647.

INTEGER is the same as INT. We use INTEGER in this course.

 

Example:    COL1 INTEGER

COL1 can have a small to large integer value.

 

Notice that the keyword, INTEGER, is a complete definition of the data type and has no value defined in parentheses. This is because INTEGER limits the values in the column to the range shown above.

For most DBMS systems, INTEGER is a 32-bit value.

SMALLINT is a data type that can only contain a relatively small, whole number.

 

Example:    COL1 SMALLINT

COL1 can only have an integer value from -32,768 to +32,767.

 

Notice that the keyword, SMALLINT, is a complete definition of the data type and has no value defined in parentheses. This is because SMALLINT limits the values in the column to the range shown above. See Sample Data Type Values.

For most DBMS systems, SMALLINT is an 16-bit value.

If TINYINT is an option, as in MySQL or SQL Server, then it typically is an 8-bit value from -256 to 255.

Exact numeric data is coded by the RDBMS vendors as follows:

Exact numeric data is represented by the following:> It can be negative, zero, or positive.

> It can be an integer. This means that it is a whole number written without a decimal point.
Examples are: -24, 0, and 83695.

> It can be a decimal number. This means that it has digits to the right of the decimal point.
Examples are: -32.4, 0.0, 765.0 or 0.0000627.

> If the numbers are not used in calculations, then they can be defined as alphanumeric literals. Leading zeroes are retained with alphanumeric literals. Examples are: telephone numbers, social security numbers, and zip codes.

> Don’t enclose a numeric literal in quotes. Leading zeroes are removed with numeric literals. Calculations are fastest with integers, and are slower with decimal numbers and floating point numbers.

> Most DBMS systems implement SMALLINT, INTEGER, BIGINT and DECIMAL.

> SQL:2003 introduced BIGINT.

> Oracle converts INT to NUMBER(32).