SQL Run

SQL is based on the relational model but doesn’t implement it faithfully. One departure from the model is that in SQL, primary keys are optional rather than mandatory. Consequently, tables without keys will accept duplicate rows, rendering some data inaccessible. A complete review of the many disparities is beyond the scope of this book (see the “Learning database design” section in Chapter 2). The upshot of these discrepancies is that DBMS users, and not the DBMS itself, are responsible for enforcing a relational structure. Another result is that the Model and SQL terms in Table 2.1 in Chapter 2 aren’t interchangeable.

With that warning, it’s time to learn SQL. An SQL program is a sequence of SQL statements executed in order. To write a program, you must know the rules that govern SQL syntax. This chapter explains how to write valid SQL statements and also covers data types and nulls.

SQL Syntax

Figure 3.1 shows the syntax of an example SQL statement (don’t worry about the actual meaning, or semantics, of the statement).

Figure 3.1An SQL statement, with a comment.

Diagram: An SQL statement and comment with parts labeled sequentially

Comment.A comment is optional text that explains your program. Comments usually describe what a program does and how, or why code was changed. Comments are for humans—the DBMS ignores them. A comment is introduced by two consecutive hyphens and continues until the end of the line.
SQL statement.An SQL statement is a valid combination of tokens introduced by a keyword. Tokens are the basic indivisible particles of the SQL language; they can’t be reduced grammatically. Tokens include keywords, identifiers, operators, literals (constants), and punctuation symbols.
Clauses.An SQL statement has one or more clauses. In general, a clause is a fragment of an SQL statement that’s introduced by a keyword, is required or optional, and must be given in a particular order. SELECT, FROM, WHERE, and ORDER BY introduce the four clauses in this example.
Keywords.A keyword is a word that SQL reserves because it has special meaning in the language. Using a keyword outside its specific context (as an identifier, for example) causes an error. DBMSs use a mix of standard and nonstandard keywords; search your DBMS documentation for keywords or reserved words. The keywords in this example are SELECT, FROM, WHERE, ORDER, and BY.
Identifiers.Identifiers are words that you (or the database designer) use to name database objects such as tables, columns, aliases, indexes, and views. The identifiers in this example are au_fname, au_lname, authors, and state. For more information, see “Identifiers” later in this chapter.
Terminating semicolon.An SQL statement ends with a semicolon.

SQL is a free-form language whose statements can:

Be in uppercase or lowercase (SELECT, select, and sElEcT are considered to be identical keywords, for example)
Continue on the next line as long as you don’t split words, tokens, or quoted strings in two
Be on the same line as other statements
Start in any column

Despite this flexibility, you should adopt a consistent style (Figure 3.2). I use uppercase keywords and lowercase identifiers and indent each clause on its own line; see “About This Book” for my typographic and syntax conventions.

Figure 3.2There aren’t many rules about how to format an SQL statement. This statement is equivalent to the one in Figure 3.1.

select au_fname , AU_LNAME FROM authors WhErE state = 'NY' order bY AU_lnamE ;

Tips for SQL Syntax

The introductory keyword of an SQL statement is called a verb because it indicates an action to perform.
Distinguish between a SELECT statement, which is the entire statement from SELECT to semicolon, and a SELECT clause, which is the part of the SELECT statement that lists the output columns.
Some DBMSs support bracketed comments, which start with /*, continue over one or more lines, and end with */. You can nest a bracketed comment within another.

An expression is any legal combination of symbols that evaluates to a single data value. You can combine mathematical or logical operators, identifiers, literals, functions, column names, aliases, and so on. Table 3.1 lists some common expressions and examples. These expressions are covered in more detail later.

Table 3.1Types of Expressions
Type	Example
Case	`CASE WHEN n <> 0 THEN x/n ELSE 0 END`
Cast	`CAST(pubdate AS CHARACTER)`
Datetime value	`start_time + '01:30'`
Interval value	`INTERVAL '7' DAY * 2`
Numeric value	`(sales*price)/12`
String value	`'Dear '\|\|au_fname\|\|','`

Common Errors

Some common SQL programming errors are:

Omitting the terminating semicolon
Misspelling a keyword or identifier
Mismatched or unmatched parentheses or quotes
Listing clauses out of order
Not surrounding a string or datetime literal with single quotes
Surrounding a numeric literal or the keyword NULL with quotes
Mismatching a table and column (typing SELECT royalty_share FROM authors instead of SELECT royalty_share FROM title_authors, for example)

These errors usually are easy to catch and correct, even if your DBMS returns an obscure or unhelpful error message. Remember that the real error actually can occur well before the statement the DBMS flags as an error. For example, if you run

CREATE TABLE misspelled_name

then your DBMS will straightaway create a table with the bad name. Your error won’t show up until later, when you try to reference the table with, say,

SELECT * FROM correct_name

Tip: A common way to test and fix (debug) SQL programs is to use comments to temporarily stop SQL code from being executed. If you’re working on a long SQL statement and want to test only part of it, then you can comment out some of the code so that the DBMS sees it as comments and ignores it.

SQL Standards and Conformance

The ISO SQL technical committee has been revising the official SQL standard every few years since 1986. Each revision:

Introduces new elements to the language
Clarifies or updates the elements of earlier standards
Drops existing elements because new elements have superseded them or they never caught on among DBMS vendors or SQL programmers

The standard is enormous—thousands of pages of dense specifications—and no vendor conforms (or ever will conform) to the entire thing. Instead, vendors try to conform to a subset of the standard called Core SQL. This level of conformance is the minimal category that vendors have to achieve to claim that they conform to standard SQL. The SQL-92 revision introduced levels of conformance, and later standards have them too, so when you read a DBMS’s conformance statement, note which SQL standard it’s referring to and which level. In fact, SQL-92 often is thought of as the standard because it defined many of the most vital and unchanging parts of the language. Except where noted, the SQL elements in this book are part of SQL-92 as well as later standards. The lowest level of SQL-92 conformance is called Entry (not Core).

Your programs should follow the SQL standard as closely as possible. Ideally, you should be able to write portable SQL programs without even knowing which DBMS you’re programming for. Unfortunately, the SQL committee is not made up of language theorists and relational-model purists but is top-heavy with commercial DBMS vendors, all jockeying and maneuvering. The result is that each DBMS vendor devotes resources to approach minimal Entry or Core SQL conformance requirements and then scampers off to add nonstandard features that differentiate their products in the marketplace—meaning that your SQL programs won’t be portable. These vendor-specific lock-ins often force you to modify or rewrite SQL programs to run on different DBMSs.

Of the DBMSs covered in this book, PostgreSQL is the “purest” with respect to the SQL standard. Your DBMS might offer settings that make it better conform to the SQL standard. MySQL has ANSI mode, for example, and Microsoft SQL Server has SET ANSI_DEFAULTS ON.

Tip: For information about using SQL across different versions of the same DBMS, see “Older DBMSs and Backward Compatibility of SQL”.

Identifiers

An identifier is a name that lets you refer to an object unambiguously within the hierarchy of database objects (whether a schema, database, column, key, index, view, constraint, or anything created with a CREATE statement). An identifier must be unique within its scope, which defines where and when it can be referenced. In general:

Database names must be unique on a specified instance of a database server.
Table and view names must be unique within a given database (or schema).
Column, key, index, and constraint names must be unique within a given table or view.

These rules let you duplicate names for objects whose scopes don’t overlap. You can give the same name to columns in different tables, for example, or to tables in different databases.

Tip: For information about addressing database objects, see Table 2.2 in Chapter 2.

DBMS scopes vary in the extent to which they require identifier names to be unique. Microsoft SQL Server requires an index name to be unique for only its table, for example, whereas Oracle and Db2 require an index name to be unique throughout the database. Search your DBMS documentation for identifiers or names.

Standard SQL has the following identifier rules for names:

Can be up to 128 characters long
Must begin with a letter
Can contain letters, digits, and underscores (_)
Can’t contain spaces or special characters (such as #, $, &, %, or punctuation)
Can’t be reserved keywords (except for quoted identifiers)

Standard SQL distinguishes between reserved and non-reserved keywords. You can’t use reserved keywords as identifiers because they have special meaning in SQL. You can’t name a table “select” or a column “sum”, for example. Non-reserved keywords have a special meaning in only some contexts and can be used as identifiers in other contexts. Most non-reserved keywords actually are the names of built-in tables and functions, so it’s safest never to use them as identifiers either.

You can use a quoted identifier, also called a delimited identifier, to break some of SQL’s identifier rules. A quoted identifier is a name surrounded by double quotes. The name can contain spaces and special characters, is case sensitive, and can be a reserved keyword. Quoted identifiers can annoy other programmers and cause problems with third-party and even a vendor’s own tools, so using them usually is a bad idea.

Here’s some more advice for choosing identifier names:

Stick to the standard rules even if your DBMS has less restrictive ones (Oracle names can contain # and $ symbols, for example).
In some cases, your DBMS will be more restrictive than the standard (MySQL identifiers can be up to only 64 characters long, for example).
Use lowercase letters.
names_with_underscores are easier to read than nameswithoutthem.
Use consistent names and abbreviations throughout the database—pick either emp or employee and stick with it.

Although you can’t use (unquoted) reserved words as identifiers, you can embed them in identifiers. group and max are illegal identifiers, for example, but groups and max_price are valid. If you’re worried that your identifier might be a reserved word in some other SQL dialect, then just add an underscore to the end of the name (element_, for example); no reserved keyword ends with an underscore.

You can surround Microsoft SQL Server quoted identifiers with double quotes or brackets ([]); brackets are preferred. In Db2, you can use reserved words as identifiers (but doing so isn’t a good idea because your program won’t be portable). MySQL ANSI_QUOTES mode allows double-quoted identifiers. DBMSs have their own nonstandard keywords; search your DBMS documentation for keywords or reserved words.

In MySQL, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names.

The SQL standard directs DBMSs to convert identifier names to uppercase internally. So in the guts of your SQL compiler, the unquoted identifier myname is equivalent to the quoted identifier "MYNAME" (not "myname"). PostgreSQL doesn’t conform to the standard and converts to lowercase. To write portable programs, always quote a particular name or never quote it (don’t mix them). DBMSs aren’t consistent when it comes to case sensitivity, so the best practice is always to respect case for user-defined identifiers.

Data Types

Recall from “Tables, Columns, and Rows” in Chapter 2 that a domain is the set of valid values allowed in a column. To define a domain, you use a column’s data type (and constraints, described in Chapter 11). A data type, or column type, has these characteristics:

Each column in a table has a single data type.

A data type falls into one of categories listed in Table 3.2 (each covered in the following sections).

Table 3.2Categories of Data Types
Category	Stores These Data
Character string	Strings of characters
Binary large object	Binary data
Exact numeric	Integers and decimal numbers
Approximate numeric	Floating-point numbers
Boolean	Truth values: true, false, or unknown
Datetime	Date and time values
Interval	Date and time intervals

The data type determines a column’s allowable values and the operations it supports. An integer data type, for example, can represent any whole number between certain DBMS-defined limits and supports the usual arithmetic operations: addition, subtraction, multiplication, and division (among others). But an integer can’t represent a nonnumeric value such as 'john' and doesn’t support character operations such as capitalization and concatenation.
The data type affects the column’s sort order. The integers 1, 2, and 10 are sorted numerically, yielding 1, 2, 10. The character strings '1', '2', and '10' are sorted lexicographically, yielding '1', '10', '2'. Lexicographical ordering sorts strings by examining the values of their characters individually. Here, '10' comes before '2' because '1' (the first character of '10') is less than '2' lexicographically. For information about sorting, see “Sorting Rows with ORDER BY” in Chapter 4.
Some data types, such as binary objects, can’t be indexed (see Chapter 12).

You store literal values (constants) in character, numeric, boolean, datetime, and interval columns. Table 3.3 shows some examples; the following sections have more examples. Be sure not to confuse the string literal '2009' with the numeric literal 2009. The SQL standard defines a literal as any constant that isn’t null.

Table 3.3Examples of Literals
Literal	Examples
Character string	`'42', 'ennui', 'don''t', N'Jack'`
Numeric	`42, 12.34, 2., .001, -123, +6.33333, 2.5E2, 5E-3`
Boolean	`TRUE, FALSE, UNKNOWN`
Datetime	`DATE '2005-06-22', TIME '09:45:00', TIMESTAMP '2006-10-19 10:23:54'`
Interval	`INTERVAL '15-3' YEAR TO MONTH, INTERVAL '22:06:5.5' HOUR TO SECOND`

Tips for Data Types

Use the statements CREATE TABLE and ALTER TABLE to define and change a column’s data type; see Chapter 11.
Database designers choose data types carefully. The consequences of a poor data-type choice include the inability to insert values into a column and data loss if the existing data type must be changed.
The SQL:2003 standard dropped SQL-92’s bit-string data types (BIT and BIT VARYING) in favor of binary large objects. Bit strings held smaller binary-data items than BLOBs do.
The SQL standard leaves many data-type implementation details up to the DBMS vendor. Consequently, SQL data types don’t map directly to specific DBMS data types, even if the data types have identical names. You’ll find equivalent or similar DBMS data types in the tips of each of the following data-type sections. Some DBMS data types have synonyms that match the SQL standard’s data-type names.

Character String Types

Use character string data types to represent text. A character string, or simply string, has these characteristics:

It’s an ordered sequence of zero or more characters.
Its length can be fixed or varying.
It’s case sensitive ('A' comes before 'a' when sorted).
In SQL statements, a string is surrounded by single quotes.
It’s one of the types listed in Table 3.4.

Table 3.4Character String Types
Type	Description
CHARACTER	Represents a fixed number of characters. A string stored in a column defined as CHARACTER(length) can have up to length characters, where length is an integer greater than or equal to 1; the maximum length depends on the DBMS. When you store a string with fewer than length characters in a CHARACTER(length) column, the DBMS pads the end of the string with spaces to create a string that has exactly length characters. A CHARACTER(6) string 'Jack' is stored as 'Jack ', for example. CHARACTER and CHAR are synonyms.
CHARACTER VARYING	Represents a variable number of characters. A string stored in a column defined as CHARACTER VARYING(length) can have up to length characters, where length is an integer greater than or equal to 1; the maximum length depends on the DBMS. Unlike CHARACTER, when you store a string with fewer than length characters in a CHARACTER VARYING(length) column, the DBMS stores the string as is and doesn’t pad it with spaces. A CHARACTER VARYING(6) string 'Jack' is stored as 'Jack', for example. CHARACTER VARYING, CHAR VARYING, and VARCHAR are synonyms.
NATIONAL CHARACTER	This data type is the same as CHARACTER except that it holds standardized multibyte characters or Unicode characters. In SQL statements, NATIONAL CHARACTER strings are written like CHARACTER strings but have an N in front of the first quote: N'βæþ', for example. NATIONAL CHARACTER, NATIONAL CHAR, and NCHAR are synonyms.
NATIONAL CHARACTER VARYING	This data type is the same as CHARACTER VARYING except that it holds standardized multibyte characters or Unicode characters (see NATIONAL CHARACTER). NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and NCHAR VARYING are synonyms.
CLOB	The character large object (CLOB) type is intended for use in library databases that hold vast amounts of text. A single CLOB value might hold an entire webpage, book, or genetic sequence, for example. CLOBs can’t be used as keys or in indexes and support fewer functions and operations than do CHAR and VARCHAR. In host languages, CLOBs are referenced with a unique locator (pointer) value, avoiding the overhead of transferring entire CLOBs across a client–server network. CLOB and CHARACTER LARGE OBJECT are synonyms.
NCLOB	The national character large object (NCLOB) type is the same as CLOB except that it holds standardized multibyte characters or Unicode characters (see NATIONAL CHARACTER). NCLOB, NCHAR LARGE OBJECT, and NATIONAL CHARACTER LARGE OBJECT are synonyms.

Tips for Character Strings

Two consecutive single quotes represent one single-quote character in a string. Type 'don''t' to represent don't, for example. A double-quote character (") is a separate character and doesn’t need this special treatment.
The length of a string is an integer between 0 and length, inclusive. A string with no characters—'' (two single quotes with no intervening space)—is called an empty string or a zero-length string. An empty string is considered to be a VARCHAR of length zero.
DBMSs often can sort and manipulate fixed-length strings faster than variable-length ones.
Keep character columns as short as possible rather than giving them “room to grow” in the future. Shorter columns sort and group faster than longer ones.
The SQL:1999 standard introduced CLOB and NCLOB to the SQL language (but most DBMSs already had similar data types by then).
Table 3.5 lists character-string and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

Oracle treats empty strings as nulls; see “Nulls” later in this chapter.

In MySQL ANSI_QUOTES mode, string literals can be quoted only with single quotes; a string quoted with double quotes is interpreted as an identifier.

Table 3.5DBMS Character String Types
DBMS	Types
Access	short text, long text (in older versions: text, memo)
SQL Server	char, varchar, nchar, nvarchar
Oracle	char, varchar2, clob, nchar, nvarchar2, nclob
Db2	char, varchar, clob, nchar, nvarchar, nclob, graphic, vargraphic, dbclob
MySQL	char, varchar, nchar, nvarchar, tinytext, text, mediumtext, longtext
PostgreSQL	char, varchar, text

Unicode

Computers store characters (letters, digits, punctuation, control characters, and other symbols) internally by assigning them unique numeric values. An encoding determines the mapping of characters to numeric values; different languages and computer operating systems use many different native encodings. Standard U.S.-English strings use ASCII encoding, which assigns values to 128 (2⁷) different characters—not much, and not even enough to hold all the Latin characters used in modern European languages, much less all the Chinese ideographs.

Unicode is a single character set that represents the characters of almost all the world’s written languages. Unicode can encode up to about 4.3 billion (2³²) characters (using UTF-32 encoding). The Unicode Consortium develops and maintains the Unicode standard. The actual Unicode mappings are available in the latest online or printed edition of The Unicode Standard, available at unicode.org.

Binary Large Object Type

Use the binary large object (BLOB) data type to store binary data. A BLOB has these characteristics:

The type name is BLOB or BINARY LARGE OBJECT.
Unlike a CLOB, which stores a long character string, a BLOB stores a long sequence of bytes. The two data types are incompatible.
BLOBs are used mainly to store large amounts of multimedia data (graphics, photos, audio, or video, for example), scientific data (medical images or climate maps), or technical data (engineering drawings).
BLOBs can’t be used as keys or in indexes and support far fewer functions and operations than do other types. BLOBs can be compared for only equality (=) or inequality (<>) because it makes no sense for a BLOB to be “less than” another BLOB. BLOBs can’t be used with DISTINCT, in GROUP BY or ORDER BY clauses, or in column functions.
In host languages, BLOBs are referenced with a unique locator (pointer) value, avoiding the overhead of transferring entire BLOBs across a client/server network.

Tips for Binary Large Objects

DBMSs don’t attempt to interpret BLOBs; their meaning is up to the application.
A binary string literal is given in hexadecimal, or hex (base 16), format. The hexadecimal system uses the digits 0 through 9 and the letters A through F (uppercase or lowercase). One hex character is equivalent to 4 bits. In SQL statements, hex strings have an X in front of the first quote, with no intervening space. The hex string X'4B' corresponds to the bits 01001011 or the bit string B'01001011', for example.
The SQL:1999 standard introduced BLOB to the SQL language (but most DBMSs already had similar data types by then).
Table 3.6 lists BLOB and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

Table 3.6DBMS BLOB Types
DBMS	Types
Access	ole object, attachment
SQL Server	binary, varbinary
Oracle	raw, long raw, blob, bfile
Db2	binary, varbinary, blob
MySQL	binary, varbinary, tinyblob, blob, mediumblob, longblob
PostgreSQL	bytea

Exact Numeric Types

Use exact numeric data types to represent exact numerical values. An exact numerical value has these characteristics:

It can be a negative, zero, or positive number.
It’s an integer or a decimal number. An integer is a whole number expressed without a decimal point: −42, 0, 62262. A decimal number has digits to the right of the decimal point: −22.06, 0.0, 0.0003, 12.34.
It has a fixed precision and scale. The precision is the number of significant digits used to express the number; it’s the total number of digits both to the right and to the left of the decimal point. The scale is the number of digits to the right of the decimal point. Obviously, the scale can’t exceed the precision. To represent a whole number, set the scale equal to zero. See “Tips for Exact Numeric Types” for some examples.
It’s one of the types listed in Table 3.7.

Table 3.7Exact Numeric Types
Type	Description
NUMERIC	Represents a decimal number, stored in a column defined as NUMERIC(precision [,scale]). precision is greater than or equal to 1; the maximum precision depends on the DBMS. scale is a value from 0 to precision. If scale is omitted, then it defaults to zero (which makes the number effectively an INTEGER).
DECIMAL	This data type is similar to NUMERIC, and some DBMSs define them equivalently. The difference is that the DBMS can choose a precision greater than that specified by DECIMAL(precision [,scale]), so precision specifies the minimum precision, not an exact precision as in NUMERIC. DECIMAL and DEC are synonyms.
INTEGER	Represents an integer. The minimum and maximum values that can be stored in an INTEGER column depend on the DBMS. INTEGER takes no arguments. INTEGER and INT are synonyms.
SMALLINT	This data type is the same as INTEGER except that it might hold a smaller range of values, depending on the DBMS. SMALLINT takes no arguments.
BIGINT	This data type is the same as INTEGER except that it might hold a larger range of values, depending on the DBMS. BIGINT takes no arguments.

Tips for Exact Numeric Types

Table 3.8 shows how the number 123.89 is stored for different precision and scale values.

Table 3.8Precision and Scale Examples for 123.89
Specified As	Stored As
`NUMERIC(5)`	`124`
`NUMERIC(5,0)`	`124`
`NUMERIC(5,1)`	`123.9`
`NUMERIC(5,2)`	`123.89`
`NUMERIC(4,0)`	`124`
`NUMERIC(4,1)`	`123.9`
`NUMERIC(4,2)`	Exceeds precision
`NUMERIC(2,0)`	Exceeds precision

Don’t enclose a numeric literal in quotes.
Store numbers as strings if the numbers are not involved in arithmetic calculations. Store telephone numbers, credit-card numbers, postal codes, and U.S. Social Security numbers as strings, for example. This technique can save space and prevent data loss: if you store the postal code '02116' as a number instead of as a string, then you’ll lose the leading zero.
Calculations involving only integers are much faster than those involving decimal and floating-point numbers.
Table 3.9 lists exact-numeric and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Some DBMSs accept type names that they don’t implement, converting them to suitable, supported types; Oracle converts INT to NUMBER(32), for example.

DBMSs usually implement SMALLINT as 16-bit values (−32,768 through 32,767), INTEGER as 32-bit values (−2,147,483,648 through 2,147,483,647), and BIGINT as 64-bit values (quintillions). The SQL:2003 standard introduced BIGINT to the SQL language (but most DBMSs already had a similar data type by then).

Table 3.9DBMS Exact Numeric Types
DBMS	Types
Access	byte, decimal, integer, long integer
SQL Server	tinyint, smallint, int, bigint, bit, numeric, decimal, smallmoney, money
Oracle	number
Db2	smallint, integer, bigint, decimal, numeric
MySQL	tinyint, smallint, mediumint, int, bigint, decimal, numeric
PostgreSQL	smallint, integer, bigint, decimal, numeric

Approximate Numeric Types

Use approximate numeric data types to represent approximate numerical values. An approximate numerical value has these characteristics:

It can be a negative, zero, or positive number.
It’s considered to be an approximation of a floating-point (real) number.
It typically is used to represent the very small or very large quantities common in technical, scientific, statistical, and financial calculations.
It’s expressed in scientific notation. A number in scientific notation is written as a decimal number multiplied by an (integer) power of 10. An uppercase E is the exponentiation symbol: 2.5E2 = 2.5 × 10² = 250, for example. The mantissa is the portion that expresses the significant digits (2.5 here), and the exponent is the power of 10 (2 here). The mantissa and exponent each can have a sign: −2.5E−2 = −2.5 × 10⁻² = −0.025.
It has a fixed precision but no explicit scale. (The sign and magnitude of the exponent determine the scale intrinsically.) The precision is the number of (binary) bits used to store the mantissa. To convert from binary to decimal precision, multiply the precision by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. For example, 24 bits yields 7 digits of precision, and 53 bits yields 15 digits of precision.
It’s one of the types listed in Table 3.10.

Table 3.10Approximate Numeric Types
Type	Description
FLOAT	Represents a floating-point number, stored in a column defined as FLOAT(precision). precision is greater than or equal to 1 and expressed as the number of bits (not the number of digits); the maximum precision depends on the DBMS.
REAL	This data type is the same as FLOAT except that the DBMS defines the precision. REAL numbers usually are called single-precision numbers. REAL takes no arguments.
DOUBLE PRECISION	This data type is the same as FLOAT except that the DBMS defines the precision, which must be greater than that of REAL. DOUBLE PRECISION takes no arguments.

Tips for Approximate Numeric Types

Don’t enclose a numeric literal in quotes.
Table 3.11 lists approximate numeric and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Some DBMSs accept type names that they don’t implement, converting them to suitable, supported types; PostgreSQL converts float to double precision, for example.

Table 3.11DBMS Approximate Numeric Types
DBMS	Types
Access	single, double
SQL Server	float, real
Oracle	float, binary_float, binary_double
Db2	real, double, float, decfloat
MySQL	float, double
PostgreSQL	real, double precision

Boolean Type

Use the boolean data type to store truth values. A boolean value has these characteristics:

The type name is BOOLEAN.
The truth values are True, False, and Unknown, represented by the boolean literals TRUE, FALSE, and UNKNOWN. Truth values are described in “Combining and Negating Conditions with AND, OR, and NOT” in Chapter 4.
A null is equivalent to the Unknown truth value and, in practice, usually is used instead of Unknown (most DBMS boolean types don’t accept the literal UNKNOWN). See “Nulls” later in this chapter.

Tips for Boolean Values

Don’t enclose a boolean literal in quotes.
The SQL:1999 standard introduced BOOLEAN to the SQL language.
Table 3.12 lists boolean and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions. Where no boolean type is available, use the data type for storing single bits or small integers. SQL programmers often use these numeric values to represent truth values, where zero means false, 1 (or any nonzero number) means true, and null means unknown.

Table 3.12DBMS Boolean Types
DBMS	Types
Access	yes/no
SQL Server	bit
Oracle	number(1)
Db2	decimal(1)
MySQL	boolean, bit, tinyint(1)
PostgreSQL	boolean

Datetime Types

Use datetime data types to represent the date and time of day. A datetime value has these characteristics:

It’s specified with respect to UTC, or Universal Coordinated Time (formerly called Greenwich Mean Time or GMT). The SQL standard requires that every SQL session have a default offset from UTC that is used for the duration of the session; −8 hours is the time-zone offset of San Francisco, California, for example.
The rules of the Gregorian calendar determine how date values are formed. DBMSs reject values that they can’t recognize as dates.
Time values are based on a 24-hour clock, also called military time (use 13:00, not 1:00 p.m.).
Hyphens (-) separate the parts of a date, and colons (:) separate the parts of a time. A space separates a date and time when both are combined.
It’s one of the types listed in Table 3.13.

Table 3.13Datetime Types
Type	Description
DATE	Represents a date. A date stored in a column defined as DATE has three integer fields—YEAR, MONTH, and DAY—and is formatted yyyy-mm-dd (length 10) (2006-03-17, for example). Table 3.14 lists the valid values for the fields. DATE takes no arguments.
TIME	Represents a time of day. A time stored in a column defined as TIME has three fields—HOUR, MINUTE, and SECOND—and is formatted hh:mm:ss (length 8) (22:06:57, for example). You can specify fractional seconds with TIME(precision). precision is the number of fractional digits and is greater than or equal to zero. The maximum precision, which is at least 6, depends on the DBMS. HOUR and MINUTE are integers, and SECOND is a decimal number. The format is hh:mm:ss.ssss... (length 9 plus the number fractional digits) (22:06:57.1333, for example). Table 3.14 lists the valid values for the fields.
TIMESTAMP	Represents a combination of DATE and TIME values separated by a space. The TIMESTAMP format is yyyy-mm-dd hh:mm:ss (length 19) (2006-03-17 22:06:57, for example). You can specify fractional seconds with TIMESTAMP(precision). The format is yyyy-mm-dd hh:mm:ss.ssss... (length 20 plus the number fractional digits).
TIME WITH TIMEZONE	This data type is the same as TIME except that it adds a field, TIME_ZONE_OFFSET, to indicate the offset in hours from UTC. TIME_ZONE_OFFSET is formatted as INTERVAL HOUR TO MINUTE (see “Interval Types” later in this chapter) and can contain the values listed in Table 3.14. Append AT TIME ZONE time_zone_offset to the TIME to assign a value to the time zone (22:06:57 AT TIME ZONE -08:00, for example). Alternatively, you can append AT LOCAL to indicate that the time zone is the default for the session (22:06:57 AT LOCAL, for example). If the AT clause is omitted, then all times default to AT LOCAL.
TIMESTAMP WITH TIMEZONE	This data type is the same as TIMESTAMP except that it adds a field, TIME_ZONE_OFFSET, to indicate the offset in hours from UTC. The syntax rules are the same as those of TIME WITH TIME ZONE except that you must include a date (2006-03-17 22:06:57 AT TIME ZONE -08:00, for example).

Table 3.14Valid Values for Datetime Fields
Type	Description
YEAR	0001 to 9999
MONTH	01 to 12
DAY	01 to 31
HOUR	00 to 23
MINUTE	00 to 59
SECOND	00 to 61.999
TIME_ZONE_OFFSET	-12:59 to +13:00

Tips for Datetime Values

To get your system time, see “Getting the Current Date and Time” in Chapter 5.
You can compare two datetime values if they have the same fields; see “Filtering Rows with WHERE” in Chapter 4. See also “Performing Datetime and Interval Arithmetic” in Chapter 5.
The SECOND field in Table 3.14 can accept values up to 61.999... (instead of 59) to allow for the (rare) insertion of leap seconds into a particular minute to keep Earth’s clocks synchronized with sidereal time.
A datetime literal is a datetime type name, followed by a space, followed by a datetime value surrounded by single quotes—DATE 'yyyy-mm-dd', TIME 'hh:mm:ss', and TIMESTAMP 'yyyy-mm-dd hh:mm:ss', for example.
Standard SQL can’t handle B.C.E./B.C. (Before the Common Era/Before Christ) dates, but your DBMS might be able to do so.
Timestamps often are used to mark events associated with the row in which they appear. In MySQL, for example, a timestamp column is useful for recording the date and time of an UPDATE operation.
The data type TIME WITH TIME ZONE makes no sense because real-world time zones have no meaning unless they’re associated with a date (because the time-zone offset varies throughout the year). Favor TIMESTAMP WITH TIME ZONE.
Table 3.15 lists datetime and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

DBMSs let you enter date values in month-day-year, day-month-year, and other formats and time values based on a 12-hour (a.m./p.m.) clock. The format in which dates and times are displayed can differ from the format in which they’re entered.

In Microsoft Access, surround datetime literals with # characters instead of quotes and omit the data type name prefix. The standard SQL date DATE '2006-03-17' is equivalent to the Access date #2006-03-17#, for example.

In Microsoft SQL Server, omit the data type name prefix from datetime literals. The standard SQL date DATE '2006-03-17' is equivalent to the SQL Server date '2006-03-17', for example.

In Db2, omit the data type name prefix from datetime literals. The standard SQL date DATE '2006-03-17' is equivalent to the Db2 date '2006-03-17', for example.

Table 3.15DBMS Datetime Types
DBMS	Types
Access	date/time
SQL Server	date, datetime, datetime2, datetimeoffset, smalldatetime, time
Oracle	date, timestamp
Db2	date, time, timestamp
MySQL	date, datetime, timestamp, time, year
PostgreSQL	date, time, timestamp

Interval Types

DBMS conformance to standard SQL interval types is spotty or nonexistent, so you might not find this section to be useful in practice. DBMSs have their own extended data types and functions that calculate intervals and perform date and time arithmetic.

Use interval data types to represent sets of time values or spans of time. An interval value has these characteristics:

It stores the quantity of time between two datetime values. Between 09:00 and 13:30 is an interval of 04:30 (4 hours and 30 minutes), for example. If you subtract two datetime values, then you get an interval.
It can be used to increment or decrement a datetime value; see “Performing Datetime and Interval Arithmetic” in Chapter 5.
It has the same fields as datetime values (YEAR, HOUR, SECOND, and so on), but the number can have a + (forward) or - (backward) sign to indicate a direction in time. The field separators are the same as for datetime values.
It comes in two categories: year-month intervals and day-time intervals. A year-month interval expresses an interval as years and a whole number of months. A day-time interval expresses an interval as days, hours, minutes, and seconds.
It has a single-field or multiple-field qualifier. A single-field qualifier is specified as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. A multiple-field qualifier is specified as:

start_field TO end_field

start_field is YEAR, DAY, HOUR, or MINUTE, and end_field is YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. end_field must be a smaller time period than start_field.

A single-field column defined as INTERVAL HOUR could store intervals such as “4 hours” or “25 hours”, for example. A multiple-field column defined as INTERVAL DAY TO MINUTE could store intervals such as “2 days, 5 hours, 10 minutes”, for example.
A single-field column can have a precision that specifies the length (number of positions) of the field; INTERVAL HOUR(2), for example. The precision defaults to 2 if omitted. A SECOND field can have an additional fractional precision that specifies the number of digits to the right of the decimal point—INTERVAL SECOND(5,2), for example. The fractional precision defaults to 6 if omitted.

A multiple-field column can have a precision for start_field but not end_field (unless end_field is SECOND, in which case it can have a fractional precision)—INTERVAL DAY(3) TO MINUTE and INTERVAL MINUTE(2) TO SECOND(4), for example.
It’s one of the types listed in Table 3.16.

Table 3.16Interval Types
Type	Description
Year-month	These intervals contain only a year value, only a month value, or both. The valid column types are INTERVAL YEAR, INTERVAL YEAR(precision), INTERVAL MONTH, INTERVAL MONTH(precision), INTERVAL YEAR TO MONTH, or INTERVAL YEAR(precision) TO MONTH.
Day-time	These intervals can contain a day value, hour value, minute value, second value, or some combination thereof. Some examples of the valid column types are INTERVAL MINUTE, INTERVAL DAY(precision), INTERVAL DAY TO HOUR, INTERVAL DAY(precision) TO SECOND, and INTERVAL MINUTE(precision) TO SECOND(frac_precision).

Tips for Interval Types

An interval literal is the word INTERVAL followed by a space, followed by an interval value surrounded by single quotes, followed by the interval qualifier—INTERVAL '15-3' YEAR TO MONTH (15 years and 3 months) and INTERVAL '22:06:5.5' HOUR TO SECOND (22 hours, 6 minutes, and 5.5 seconds), for example.
See also “Working with Dates” in Chapter 15.
Table 3.17 lists interval and similar types for the DBMSs. See the DBMS documentation for size limits and usage restrictions.

Table 3.17DBMS Interval Types
DBMS	Types
Access	Not supported
SQL Server	Not supported
Oracle	interval year to month, interval day to second
Db2	Not supported
MySQL	Not supported
PostgreSQL	interval

Unique Identifiers

Unique identifiers are used to generate primary-key values to identify rows (see “Primary Keys” in Chapter 2). An identifier can be unique universally (large random numbers unique in any context) or only within a specific table (simple serial numbers 1, 2, 3,...). Table 3.18 lists unique-identifier types and attributes for the DBMSs. See the DBMS documentation for size limits and usage restrictions. The SQL standard calls columns with auto-incrementing values identity columns. See also “Generating Sequences” in Chapter 15.

Table 3.18Unique Identifiers (Types or Attributes)
DBMS	Types or Attributes
Standard SQL	IDENTITY
Access	autonumber, replication id
SQL Server	uniqueidentifier, identity
Oracle	rowid, urowid, sequences
Db2	rowid, identity columns and sequences
MySQL	auto_increment attribute
PostgreSQL	smallserial, serial, bigserial, uuid

UUIDs

A universally unique ID is called a Universally Unique Identifier (UUID) or a Globally Unique Identifier (GUID). When you define a column to have a UUID data type, your DBMS will generate a random UUID automatically in each new row, probably according to ISO/IEC 9834-8 (iso.org) or IETF RFC 4122 (ietf.org).

A UUID in standard form looks like:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

The letters actually are hexadecimal digits (a–f). Your DBMS might use an alternative form with uppercase hex digits, surrounding braces, or omitted hyphens. UUIDs aren’t technically guaranteed to be unique, but the probability of generating a duplicate ID is so tiny that they should be considered singular. For more information, read the Wikipedia article “Universally unique identifier”.

DBMSs provide functions that return UUIDs. Microsoft SQL Server has NEWID(), Oracle has SYS_GUID(), MySQL has UUID(), and PostgreSQL has gen_random_uuid(). Note that the Db2 function GENERATE_UNIQUE() returns a string that is not a UUID.

Other Data Types

The SQL standard defines other data types than the ones covered in the preceding sections, but some of them rarely are implemented or used in practice (ARRAY, MULTISET, REF, and ROW, for example). More useful are the extended (nonstandard) data types that are available in various DBMSs. Depending on your DBMS, you can find data types for:

Time intervals
Network and internet addresses
Links to files stored outside the database
Monetary (currency) amounts
Geographic and geometric (spatial) coordinates
Row version-stamping
Arrays and other collections
XML
JSON
Full text searching
Enumerated (enum) values from a specified list
Position in a hierarchy

User-Defined Types

Microsoft SQL Server, Oracle, Db2, and PostgreSQL let you create user-defined types (UDTs). The simplest UDT is a standard or built-in data type (CHARACTER, INTEGER, and so on) with additional check and other constraints. You can define the data type marital_status, for example, as a single-character CHARACTER data type that allows only the values S, M, W, D, or NULL (for single, married, widowed, divorced, or unknown). More-complex UDTs are similar to classes in object-oriented programming languages such as Java or Python. You can define a UDT once and use it in multiple tables, rather than repeat its definition in each table in which it’s used. Search your DBMS documentation for user-defined type. UDTs are created in standard SQL with the statement CREATE TYPE.

Nulls

When your data are incomplete, you can use a null to represent a missing or unknown value. A null has these characteristics:

In SQL statements, the keyword NULL represents a null.
A null is used for a value that might never be known, might be determined later, or is inapplicable. (Think of a null as a marker or flag for a missing value, rather than as a value itself.)
A null differs from zero, a string that contains only spaces, or an empty string (''). A null in the column price doesn’t mean that an item has no price or that its price is zero; it means that the price is unknown or has not been set. (Oracle is a special case with respect to empty strings; see the DBMS tip in this section.)
Nulls can appear in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY constraints (Chapter 11). You should not allow nulls in alternate keys.
To detect nulls, see “Testing for Nulls with IS NULL” in Chapter 4.
Nulls aren’t equal (or unequal) to each other. You can’t determine whether a null matches any other value, including another null, so the expressions NULL = any_value, NULL <> any_value, NULL = NULL, and NULL <> NULL are neither true nor false but unknown; see “Combining and Negating Conditions with AND, OR, and NOT” in Chapter 4.
Although nulls are never equal to each other, DISTINCT treats all the nulls in a particular column as duplicates; see “Eliminating Duplicate Rows with DISTINCT” in Chapter 4.
When you sort a column that contains nulls, the nulls will be either greater than or less than all the non-null values, depending on the DBMS; see “Sorting Rows with ORDER BY” in Chapter 4.
Nulls propagate through computations. The result of any arithmetic expression or operation that involves a null is null: (12*NULL)/4 is null; see Chapter 5.
Most aggregate functions, such as SUM(), AVG(), and MAX(), ignore nulls. COUNT(*) is an exception. See Chapter 6.
If the grouping column in a GROUP BY clause contains nulls, then all the nulls are put in a single group; see “Grouping Rows with GROUP BY” in Chapter 6.
Nulls affect the results of joins; see “Using Joins” in Chapter 7.
Nulls can cause problems in subqueries; see “Nulls in Subqueries” in Chapter 8.

Tips for Nulls

Use caution when interpreting results in which nulls are involved. Nulls cause so many problems and complications—some important ones are listed here—that some database experts urge users to minimize their use or not use them at all (and instead use default values or some other missing-data scheme). Nevertheless, you won’t become a competent SQL programmer without understanding nulls completely.
See also “Checking for Nulls with COALESCE” and “Comparing Expressions with NULLIF” in Chapter 5.
Nullable means that a column is allowed to contain nulls. Use the CREATE TABLE or ALTER TABLE statement (Chapter 11) to set a column’s nullability.
The term null value is inaccurate—a null indicates the lack of a value.
Don’t place the keyword NULL in quotes; your DBMS will interpret it as the character string 'NULL' rather than as a null.
You can get a null from a column that doesn’t allow nulls. The column au_id in the table authors doesn’t allow nulls, but the SELECT statement in Figure 3.3 returns a null for the maximum au_id.

Figure 3.3Getting a null from a column that isn’t nullable.

SELECT MAX(au_id) FROM authors WHERE au_lname = 'XXX'; MAX(au_id) ---------- NULL
If nulls appear in a column because actual values are not meaningful (rather than unknown), then you can split the column off into its own table with a one-to-one relationship with the other table. In Figure 3.4, the original table employees has the column commission, which specifies an employee’s sales commission. commission contains mostly nulls because most employees aren’t salespeople. To avoid the proliferation of nulls, move commission to its own table.

Figure 3.4Nulls are eliminated by splitting the original table (top) into a one-to-one relationship (bottom).
The display of nulls in results varies by DBMS. A null might appear as NULL, (NULL), <NULL>, -, or empty space, for example.

Oracle treats an empty string ('') as a null. This treatment might not continue to be true in future releases, however, and Oracle recommends that you do not treat empty strings the same as nulls in your SQL code. This behavior can cause conversion problems among DBMSs. In the sample database, for example, the column au_fname in the table authors is defined as NOT NULL. In Oracle, the first name of the author Kellsey (author A06) is a space (' '); in the other DBMSs, the first name is an empty string (''). For information about the sample database, see “The Sample Database” in Chapter 2.