Lecture 14. SQL Standard Database Language 14.1. Data types

In this lecture, we briefly review the main features of the 1989 SQL language standard.

14.1. Data types

The following data types are supported in SQL / 89: CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION. These data types are classified into string types of characters, exact numbers, and approximate numbers.

CHARACTER belongs to the first class. The type specifier is CHARACTER (lenght), where lenght is the length of the strings of this type. Note that in SQL / 89 there is no variable-size string type, although in many implementations they are allowed. Literal character strings are depicted as a sequence of characters (for example, example).

Representatives of the second class of types are NUMERIC, DECIMAL (or DEC), INTEGER (or INT) and SMALLINT. The NUMERIC type specifier has the form NUMERIC [(precision [, scale]). Exact numbers that are represented with precision and scale are specified. Hereinafter, if the scale is omitted, then it is assumed to be 0, and if accuracy is omitted, then its default value is defined in the implementation.

The type specifier DECIMAL (or DEC) has the form NUMERIC [(precision [, scale]). Exact numbers are specified that are represented with a scale and accuracy equal to or greater than the precision value.

INTEGER specifies the data type of exact numbers with a scale of 0 and the precision defined in the implementation. SMALLINT specifies the data type of exact numbers with a scale of 0 and the precision defined in the implementation that is no greater than the precision of INTEGER numbers.

Literal values ​​of exact numbers in the general case are represented in the form

[+ | -] [. ].

Finally, the class of approximate numbers data types includes the types FLOAT, REAL, and DOUBLE PRECISION. The FLOAT type specifier has the form FLOAT [(precision)]. Specifies approximate numbers with binary precision equal to or greater than precision.

REAL specifies the data type of approximate numbers with the precision defined in the implementation. DOUBLE PRECISION specifies the data type of approximate numbers with an accuracy defined in the implementation greater than the accuracy of the REAL type.

The literal values ​​of approximate numbers in the general case are represented as E .

Note that although using the SQL language you can define a database schema containing data of any of the listed types, the ability to use this data in application systems depends on the programming language used. The entire set of data types can only be used if programmed on a PL / 1. Therefore, in some SQL implementations, data types with scale and accuracy are not supported at all.

Although the rules for embedding SQL into C programs are not defined in SQL / 89, most implementations that support such embedding have the following correspondence between SQL data types and C data types: CHARACTER corresponds to C strings; INTEGER matches long; SMALLINT matches short; REAL matches float; DOUBLE PRECISION conforms to double (this is the correspondence approved in the SQL / 92 standard).

Note also that in most SQL implementations, some additional data types are supported, for example, DATE, TIME, INTERVAL, MONEY. Some of these types are specified in the SQL / 92 standard, but in current implementations the syntactic and semantic properties of such types may differ.