Exact Numeric Data

Exact Numeric Data
The exact numeric data type is used to define numbers with an exact representation.
The number consists of digits, an optional decimal point, and an optional sign. An exact
numeric data type consists of a precision and a scale. The precision gives the total number
of significant decimal digits; that is, the total number of digits, including decimal
places but excluding the point itself. The scale gives the total number of decimal places.
For example, the exact numeric value −12.345 has precision 5 and scale 3. A special case
of exact numeric occurs with integers. There are several ways of specifying an exact
numeric data type:
NUMERIC [ precision [, scale] ]
DECIMAL [ precision [, scale] ]
INTEGER
SMALLINT
INTEGER can be abbreviated to INT and DECIMAL to DEC
NUMERIC and DECIMAL store numbers in decimal notation. The default scale is always
0; the default precision is implementation-defined. INTEGER is used for large positive
or negative whole numbers. SMALLINT is used for small positive or negative whole
numbers. By specifying this data type, less storage space can be reserved for the data.
For example, the maximum absolute value that can be stored with SMALLINT might be
32 767. The column rooms of the PropertyForRent table, which represents the number of
rooms in a property, is obviously a small integer and can be declared as:
rooms SMALLINT
The column salary of the Staff table can be declared as:
salary DECIMAL(7,2)
which can handle a value up to 99,999.99.
Approximate numeric data
The approximate numeric data type is used for defining numbers that do not have an exact
representation, such as real numbers. Approximate numeric, or floating point, is similar to
scientific notation in which a number is written as a manissa times some power of ten (the
exponent). For example, 10E3, +5.2E6, -0.2E-4. There are several ways of specifying an
approximate numeric data type:
FLOAT [precision]
REAL
DOUBLE PRECISION
The precision controls the precision of the mantissa. The precision of REAL and DOUBLE
PRECISION is implementation-defined.
Datetime data
The datetime data type is used to define points in time to a certain degree of accuracy.
Examples are dates, times, and times of day. The ISO standard subdivides the datetime data
type into YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR,
and TIMEZONE_MINUTE. The latter two fields specify the hour and minute part of the
time zone offset from Universal Coordinated Time (which used to be called Greenwich
Mean Time). Three types of datetime data type are supported:
DATE
TIME [timePrecision] [WITH TIME ZONE]
TIMESTAMP [timePrecision] [WITH TIME ZONE]
DATE is used to store calendar dates using the YEAR, MONTH, and DAY fields. TIME
is used to store time using the HOUR, MINUTE, and SECOND fields. TIMESTAMP is
used to store date and times. The timePrecision is the number of decimal places of accuracy
to which the SECOND field is kept. If not specified, TIME defaults to a precision
of 0 (that is, whole seconds), and TIMESTAMP defaults to 6 (that is, microseconds).
The WITH TIME ZONE keyword controls the presence of the TIMEZONE_HOUR and
TIMEZONE_MINUTE fields. For example, the column date of the Viewing table, which
represents the date (year, month, day) that a client viewed a property, is declared as:
viewDate DATE
Interval data
The interval data type is used to represent periods of time. Every interval data type
consists of a contiguous subset of the fields: YEAR, MONTH, DAY, HOUR, MINUTE,
SECOND. There are two classes of interval data type: year–month intervals and day–
time intervals. The year–month class may contain only the YEAR and/or the MONTH
fields; the day–time class may contain only a contiguous selection from DAY, HOUR,
MINUTE, SECOND. The format for specifying the interval data type is:
INTERVAL {{startField TO endField} singleDatetimeField}
startField = YEAR | MONTH | DAY | HOUR | MINUTE
[(intervalLeadingFieldPrecision)]
endField = YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
[(fractionalSecondsPrecision)]
singleDatetimeField = startField | SECOND
[(intervalLeadingFieldPrecision [, fractionalSecondsPrecision])]
In all cases, startField has a leading field precision that defaults to 2. For example:
INTERVAL YEAR(2) TO MONTH
represents an interval of time with a value between 0 years 0 months, and 99 years
11 months; and:
INTERVAL HOUR TO SECOND(4)
represents an interval of time with a value between 0 hours 0 minutes 0 seconds and
99 hours 59 minutes 59.9999 seconds (the fractional precision of second is 4).
Scalar operators
SQL provides a number of built-in scalar operators and functions that can be used to
construct a scalar expression: that is, an expression that evaluates to a scalar value. Apart
from the obvious arithmetic operators (+, −, *, /), the operators shown in Table 6.2 are
available.
Operator
BIT_LENGTH
OCTET_LENGTH
CHAR_LENGTH
CAST
||
CURRENT_USER
or USER
SESSION_USER
SYSTEM_USER
LOWER
UPPER
TRIM
POSITION
SUBSTRING
CASE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
EXTRACT
Meaning
Returns the length of a string in bits. For example,
BIT_LENGTH(X‘FFFF’) returns 16.
Returns the length of a string in octets (bit length divided by 8).
For example, OCTET_LENGTH(X‘FFFF’) returns 2.
Returns the length of a string in characters (or octets, if the string
is a bit string). For example, CHAR_LENGTH(‘Beech’) returns 5.
Converts a value expression of one data type into a value in
another data type. For example, CAST(5.2E6 AS INTEGER).
Concatenates two character strings or bit strings. For example,
fName || lName.
Returns a character string representing the current authorization
identifier (informally, the current user name).
Returns a character string representing the SQL-session
authorization identifier.
Returns a character string representing the identifier of the user
who invoked the current module.
Converts upper-case letters to lower-case. For example,
LOWER(SELECT fName FROM Staff WHERE
staffNo = ‘SL21’) returns ‘john’
Converts lower-case letters to upper-case. For example,