Basic Data Types

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour  2.  Defining Data Structures


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. Defining each field in the database with a data type eliminates much of the incorrect data found in a database due to data entry errors. Field definition (data type definition) is a form of data validation, which controls the type of data that may be entered into each given field.

graphics/note_icon.gif

Every implementation of SQL seems to have its own specific set of data types. The use of implementation-specific data types is necessary to support the philosophy of each implementation on how to handle the storage of data. However, the basics are the same among all implementations .


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

  • Character strings

  • Numeric strings

  • Date and time values

Fixed-Length Characters

graphics/newterm_icon.gif

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.

graphics/cautions_icon.gif

Be careful not to use a fixed-length data type for fields that may contain varying-length values, such as an individual's name . If you use the fixed-length data type inappropriately, problems such as the waste of available space and the inability to make accurate comparisons between data will eventually be encountered .


Variable Characters

graphics/newterm_icon.gif

SQL supports the use of varying-length strings, strings whose length is not constant for all data. The following is the standard for a 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 and MySQL use; both VARCHAR and VARCHAR2 are used by Oracle. The data stored in a character-defined column can be alphanumeric, which means that the data value may contain numeric characters.

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.

graphics/tip_icon.gif

Always use the varying-length data type for non-constant character strings to save database space.


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  ) 
graphics/newterm_icon.gif

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.

graphics/newterm_icon.gif

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) 
graphics/newterm_icon.gif

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. In this case, any numbers between 12.445 and 12.449 would be rounded to 12.45.

Integers

graphics/newterm_icon.gif

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

graphics/newterm_icon.gif

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 
graphics/note_icon.gif

The SECOND element can also be broken down to fractions of a second. The range is from 00.000 to 61.999, although some implementations of SQL may not support this range. The extra 1.999 seconds is used to accommodate for leap seconds.


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

graphics/newterm_icon.gif

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 stringsyou 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, 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' 

When using the NULL data type, it is important to realize that data is not required in a particular field. If data is always required for a given field, always use NOT NULL with a data type. If there is a chance that there may not always be data for a field, then it is better to use 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

graphics/newterm_icon.gif

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 to meet data storage needs. They are based on existing data types. User-defined data types can assist the developer by providing greater flexibility during database application development, because they maximize the number of possibilities for data storage. The CREATE TYPE statement is used to create a user-defined type.

For example, you can create a type as follows:

 graphics/mysql_icon.gif CREATE TYPE PERSON AS OBJECT (NAME       VARCHAR (30),  SSN        VARCHAR (9)); 

You can reference your user-defined type as follows:

 graphics/mysql_icon.gif CREATE TABLE EMP_PAY (EMPLOYEE   PERSON,  SALARY     DECIMAL(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

graphics/newterm_icon.gif

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. Constraints work in conjunction with data types, allowing you to further specify acceptable data for a field. The domain is used like the user-defined type.

You can create a domain as follows:

 graphics/mysql_icon.gif CREATE DOMAIN MONEY_D AS NUMBER(8,2); 

You can add constraints to your domain as follows:

 graphics/mysql_icon.gif ALTER DOMAIN MONEY_D ADD CONSTRAINT MONEY_CON1  CHECK (VALUE > 5); 

You can reference the domain as follows:

 graphics/mysql_icon.gif CREATE TABLE EMP_PAY (EMP_ID        NUMBER(9),   EMP_NAME      VARCHAR2(30),   PAY_RATE      MONEY_D); 
graphics/note_icon.gif

Note that some of the data types mentioned during this hour may not be available by name in the implementation of SQL that you are using. Data types are often named differently among implementations of SQL, but the concept behind each data type remains. Most, if not all, data types are supported by most relational databases.



Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net