Page 2 - Simple Queries in SQL

Following table shows the required storage and range (maximum and minimum value for signed and
unsigned integer) for each floating-point type.

Type Length Minimum Value Maximum Value Minimum Value Maximum Value
in Bytes (Signed) (Signed) (Unsigned) (Unsigned)
FLOAT 4 -3.402823466E+38 -1.175494351E-38 1.175494351E-38 3.402823466E+38
DOUBLE 8 -1.7976931348623 -2.22507385850720 0, and 1.797693134862315
157E+308 14E- 308 2.22507385850720 7E+ 308
14E- 308

Fixed Point Types
Fixed-Point data types are used to preserve exact precision, for example with currency data. In
MySQL DECIMAL and NUMERIC types store exact numeric data values. MySQL stores
DECIMAL values in binary format.
In standard SQL the syntax DECIMAL(5,2), 5 is the precision and 2 is the scale(number of places after
decimal), be able to store any value with five digits and two decimals. Therefore the value range will
be from
-999.99 to 999.99.

DATE and TIME type
The date and time types represent DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a
range of valid values, as well as a “zero” value.
DATETIME, DATE, and TIMESTAMP Types are:

Types Description Display Format Range
DATETIME Use when you need values YYYY-MM-DD '1000-01-01 00:00:00' to
containing both date and time HH:MM:SS '9999-12-31 23:59:59'.
information.
DATE Use when you need only date YYYY-MM-DD '1000-01-01' to
information. '9999-12-31'.
TIMESTAMP Values are converted from the YYYY-MM-DD '1970-01-01 00:00:01' UTC
current time zone to UTC while HH:MM:SS to
storing and converted back from '2038-01-19 03:14:07' UTC
UTC to the current time zone
when retrieved.

TIME type
MySQL fetches and displays TIME values in 'HH:MM:SS' format or 'HHH:MM:SS' format. The range of
TIME values from '-838:59:59' to '838:59:59'. The hours part may be rather large because not only
the TIME type can be used to represent the time of day, i.e. less than 24 hours, but also the passed
time or a time of interval between two events.

YEAR type
The YEAR type is a 1-byte type used to store a year in a 2-digit or a 4-digit format. If the length is
specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is
specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

String Types
The string types are:
• CHAR
• VARCHAR