SQL Scalar Data Types

SQL Scalar Data Types
Table 6.1 shows the SQL scalar data types defined in the ISO standard. Sometimes, for
manipulation and conversion purposes, the data types character and bit are collectively
referred to as string data types, and exact numeric and approximate numeric are referred
to as numeric data types, as they share similar properties. The SQL:2003 standard also
defines both character large objects and binary large objects, although we defer discussion
of these data types until Section 28.4.
Boolean data
Boolean data consists of the distinct truth values TRUE and FALSE. Unless prohibited by
a NOT NULL constraint, boolean data also supports the UNKNOWN truth value as the
NULL value. All boolean data type values and SQL truth values are mutually comparable
and assignable. The value TRUE is greater than the value FALSE, and any comparison
involving the NULL value or an UNKNOWN truth value returns an UNKNOWN result.
Character data
Character data consists of a sequence of characters from an implementation-defined character
set, that is, it is defined by the vendor of the particular SQL dialect. Thus, the exact characters
that can appear as data values in a character type column will vary. ASCII and EBCDIC
are two sets in common use today. The format for specifying a character data type is:
CHARACTER [VARYING] [length]
CHARACTER can be abbreviated to CHAR and
CHARACTER VARYING to VARCHAR.
When a character string column is defined, a length can be specified to indicate the
maximum number of characters that the column can hold (default length is 1). A character
string may be defined as having a fixed or varying length. If the string is defined to be a
fixed length and we enter a string with fewer characters than this length, the string is
padded with blanks on the right to make up the required size. If the string is defined to be
of a varying length and we enter a string with fewer characters than this length, only those
characters entered are stored, thereby using less space. For example, the branch number
column branchNo of the Branch table, which has a fixed length of four characters, is
declared as:
branchNo CHAR(4)
The column address of the PrivateOwner table, which has a variable number of characters
up to a maximum of 30, is declared as:
address VARCHAR(30)
Bit data
The bit data type is used to define bit strings, that is, a sequence of binary digits (bits), each
having either the value 0 or 1. The format for specifying the bit data type is similar to that
of the character data type:
BIT [VARYING] [length]
For example, to hold the fixed length binary string ‘0011’, we declare a column bitString,
as:
bitString BIT(4)