Basic Data Types

The following sections discuss the basic data types supported by ANSI SQL. Data types are characteristics of the data itself, whose attributes are placed on fields within a table. For example, you can specify that a field must contain numeric values, disallowing the entering of alphanumeric strings. After all, you would not want to enter alphabetic characters in a field for a dollar amount.

The very basic data types, as with most other languages, are

• Character strings

• Numeric strings

• Date and time values

Fixed-Length Characters

Constant characters,, those strings that always have the same length, are stored using a fixed-length data type. The following is the standard for an SQL fixed-length character:

CHARACTER(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Some implementations of SQL use the CHAR data type to store fixed-length data. Alphanumeric data can be stored in this data type. An example of a constant length data type would be for a state abbreviation because all state abbreviations are two characters.

Spaces are normally used to fill extra spots when using a fixed-length data type; if a field's length was set to 10 and data entered filled only five places, the remaining five spaces are recorded as spaces. The padding of spaces ensures that each value in a field is a fixed length.

Variable Characters

SQL supports the use of varying-length strings,strings whose length is not constant for all data. The following is the standard for an SQL varying-length character:

CHARACTER VARYING(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Common data types for variable-length character values are the VARCHAR and VARCHAR2 data types. VARCHAR is the ANSI standard, which Microsoft SQL Server uses; VARCHAR2 is used by Oracle and should be used in Oracle, because VARCHAR's usage in the future may change. The data stored can be alphanumeric.

Remember that fixed-length data types typically pad spaces to fill in allocated places not used by the field. The varying-length data type does not work this way. For instance, if the allocated length of a varying-length field is 10, and a string of five characters is entered, the total length of that particular value is only 5. Spaces are not used to fill unused places in a column.

Numeric Values

Numeric values are stored in fields that are defined as some type of number, typically referred to as NUMBER, INTEGER, REAL, DECIMAL, and so on.

The following are the standards for SQL numeric values:

BIT (n)

BIT VARYING (n)

DECIMAL (p,s)

INTEGER

SMALLINT

FLOAT(p)

REAL(s)

DOUBLE PRECISION(P)

p represents a number identifying the allocated, or maximum length, of the particular field for each appropriate definition.

s is a number to the right of the decimal point, such as 34.ss.

A common numeric data type in SQL implementations is NUMBER, which accommodates the direction for numeric values provided by ANSI. Numeric values can be stored as zero, positive, negative, fixed, and floating-point numbers. The following is an example using NUMBER:

NUMBER(5)

This example restricts the maximum value entered in a particular field to 99999.

Decimal Values

Decimal values are numeric values that include the use of a decimal point. The standard for a decimal in SQL follows, where the p is the precision and the s is the decimal's scale:

DECIMAL(p,s)

The precision is the total length of the numeric value. In a numeric defined DECIMAL(4,2), the precision is 4, which is the total length allocated for a numeric value.

The scale is the number of digits to the right of the decimal point. The scale is 2 in the previous DECIMAL(4,2) example.

34.33 inserted into a DECIMAL(3,1) is typically rounded to 34.3.

If a numeric value was defined as the following data type, the maximum value allowed would be 99.99:

DECIMAL(4,2)

The precision is 4, which represents the total length allocated for an associated value. The scale is 2, which represents the number of places, or bytes, reserved to the right side of the decimal point. The decimal point itself does not count as a character.

Allowed values for a column defined as DECIMAL(4,2) include the following:

12

12.4

12.44

12.449

The last numeric value, 12.449, is rounded off to 12.45 upon input into the column.

Integers

An integer is a numeric value that does not contain a decimal, only whole numbers (both positive and negative).

Valid integers include the following:

1

0

-1

99

-99

199

Floating-Point Decimals

Floating-point decimals are decimal values whose precision and scale are variable lengths and virtually without limit. Any precision and scale is acceptable. The REAL data type designates a column with single-precision, floating-point numbers. The DOUBLE PRECISION data type designates a column that contains double-precision, floating-point numbers. To be considered a single-precision floating point, the precision must be between 1 and 21 inclusive. To be considered a double-precision floating point, the precision must be between 22 and 53 inclusive. The following are examples of the FLOAT data type:

FLOAT

FLOAT(15)

FLOAT(50)

Dates and Time

Date and time data types are quite obviously used to keep track of information concerning dates and time. Standard SQL supports what are called DATETIME data types, which include the following specific data types:

DATE

TIME

INTERVAL

TIMESTAMP

The elements of a DATETIME data type consist of the following:

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

Be aware that each implementation of SQL may have its own customized data type for dates and times. The previous data types and elements are standards to which each SQL vendor should adhere, but be advised that most implementations have their own data type for date values, varying in both appearance and the way date information is actually stored internally.

A length is not normally specified for a date data type. Later in this hour, you learn more about dates, how date information is stored in some implementations, how to manipulate dates and times using conversion functions, and study practical examples of how dates and time are used in the real world.

Literal Strings

A literal string is a series of characters, such as a name or a phone number, that is explicitly specified by a user or program. Literal strings consist of data with the same attributes as the previously discussed data types, but the value of the string is known; the value of a column itself is usually unknown, because there is typically a different value for a column associated with each row of data in a table.

You do not actually specify data types with literal strings—you simply specify the string. Some examples of literal strings follow:

'Hello'

45000

"45000"

3.14

'November 1, 1997'

The alphanumeric strings are enclosed by single quotation marks, whereas the number value 45000 is not. Also notice that the second numeric value of 45000 is enclosed by quotation marks. Generally speaking, character strings require quotation marks, whereas numeric strings don't. You see later how literal strings are used with database queries.

NULL Data Types

As you should know from Hour 1, "Welcome to the World of SQL," a NULL value is a missing value or a column in a row of data that has not been assigned a value. NULL values are used in nearly all parts of SQL, including the creation of tables, search conditions for queries, and even in literal strings.

The following are two methods for referencing a NULL value:

• NULL (the keyword NULL itself)

• '' (single quotation marks with nothing in between)

The following does not represent a NULL value, but a literal string containing the characters N-U-L-L:

'NULL'

BOOLEAN Values

A BOOLEAN value is a value of either TRUE, FALSE, or NULL. BOOLEAN values are used to make data comparisons. For example, when criteria are specified for a query, each condition evaluates to either a TRUE, FALSE, or NULL. If the BOOLEAN value of TRUE is returned by all conditions in a query, data is returned. If a BOOLEAN value of FALSE or NULL is returned, data may not be returned.

Consider the following example:

WHERE NAME = 'SMITH'

This line might be a condition found in a query. The condition is evaluated for every row of data in the table that is being queried. If the value of NAME is SMITH for a row of data in the table, the condition returns the value TRUE, thereby returning the data associated with that record.

User-Defined Types

A user-defined type is a data type that is defined by the user. User-defined types allow users to customize their own data types based on existing data types. The CREATE TYPE statement is used to create a user-defined type.

For example, you can create a type as follows:

CREATE TYPE PERSON AS OBJECT
(NAME       VARCHAR2(30),
 SSN    VARCHAR2(9));

You can reference your user-defined type as follows:

CREATE TABLE EMP_PAY
(EMPLOYEE   PERSON,
 SALARY     NUMBER(10,2),
 HIRE_DATE      DATE);

Notice that the data type referenced for the first column EMPLOYEE is PERSON. PERSON is the user-defined type you created in the first example.

Domains

A domain is a set of valid data types that can be used. A domain is associated with a data type, so that only certain data is accepted. After a domain is created, you can add constraints to the domain. The domain is used like the user-defined type.

You can create a domain as follows:

CREATE DOMAIN MONEY_D AS NUMBER(8,2);

You can add constraints to your domain as follows:

ALTER DOMAIN MONEY_D 
ADD CONSTRAINT MONEY_CON1 
CHECK (VALUE > 5);

You can reference the domain as follows:

CREATE TABLE EMP_PAY
(EMP_ID        NUMBER(9),
 EMP_NAME      VARCHAR2(30),
 PAY_RATE      MONEY_D);