SQL and Relational Theory, 2nd Edition

SCALAR TYPES IN SQL

I turn now to SQL. SQL supports the following more or less self-explanatory system defined scalar types (it also allows users to define their own types, but as I’ve already said I’m more or less ignoring user defined types in this chapter):

     BOOLEAN     INTEGER          CHARACTER(n)
                 SMALLINT         CHARACTER VARYING(n)
                 BIGINT           CHARACTER LARGE OBJECT(n)
                 NUMERIC(p,q)     BINARY(n)
                 DECIMAL(p,q)     BINARY VARYING(n)
                 FLOAT(p)         BINARY LARGE OBJECT(n)

This isn’t an exhaustive list; other SQL system defined types include an “XML document” type (XML); a variety of “national character string types” (NATIONAL CHARACTER(n), etc.); and a variety of datetime types (DATE, TIME, TIMESTAMP, INTERVAL). However, I’ll ignore such types, mostly, for the purposes of this book. Points arising:

• A number of defaults, abbreviations, and alternative spellings—e.g., INT for INTEGER, CHAR for CHARACTER, VARCHAR for CHARACTER VARYING, CLOB for CHARACTER LARGE OBJECT—are also supported.

• As you can see, SQL, unlike Tutorial D, requires its various character string types to have an associated length specification.

• The same goes for the various BINARY types. Note: BINARY really means bit string, or (perhaps better) byte string; the associated length specification gives the corresponding length in octets.^[31] Also, while BINARY LARGE OBJECT can be abbreviated to BLOB, BINARY and BINARY VARYING can’t be abbreviated at all (contrary to expectations, perhaps).

• Strictly speaking, CHAR (for example) isn’t really a type as such—rather, ...