CyberArmy

SQL Data Structures

• Character Structures: SQL has both fixed and variable length character data structures.

Fixed-length characters are defined to have a certain fixed maximum length. It is important to note that fixed-length character data can be shorter than the defined maximum ("n") but may not exceed this. If there are fewer characters entered than the max allowed, spaces fill the remaining spots. Fixed length characters are useful, but can create problems when used unwisely (such as when used for a name field).

Syntax:

character(n) or char(n)

*n represents the maximum number of characters allowed*
Variable-length characters allow space saving in databases. They define a maximum allowable number of characters (n). However, the slots that remain when a value is entered that is less than "n" are not filled with spaces, instead they are ignored. This allows you to set the maximum value higher without wasting space in the database.

Syntax:

character varying(n) or character(n)

*n represents the maximum number of characters allowed*

• Numeric Data Structures:

ANSI SQL and most other flavors of SQL can handle numerous numeric data types, however, the names and support of features varies from implementation to implementation, so be careful.
One of the most useful numeric types is number. Number allows numeric data to be stored in whatever format it is input. number allows you define the number of digits, but these values may be positive, negative, zero, decimals, or integers.

Syntax:

number(n)

*n represents the number of digits in the value*
Integer data can be specified by the integer type. Because you are imputing integer data, integer takes no other XXXXX . Integer values may be any whole number both positive and negative.

Syntax:

integer
There are several ways to declare decimal data with ANSI SQL. I will cover the two most common: decimal and float.

Decimal values are fixed in both their total length and the number of digits (precision) to the right of the decimal (scale).

Syntax:

decimal(p,s)

*where p is the precision and s is the scale*
Example:

decimal(5,3) would allow a five digit number with 3 places to the right of the decimal and two to the left. Thus 50.3333 would be stored as 50.333 and 33.3456 would be stored as 33.346. The number 33 would be allowed and stored as simply 33 (or 33.) using this declaration.
Floating-point values are decimals that have a decimal point that "floats" just as in most other languages. The precision of the floating-point value may or may not be defined.

Syntax:

float *undefined precision*

float(p) *maximum precision is defined as p*
When defining precision with the float data type, defining it to be anywhere from 1 to 21 will have it treated as single precision, if it is defined to be 22-53 then it is treated as double precision. If precision is not defined, float is treated as double precision. Values greater than 53 will give errors more than likely.
The numeric data type:

If you wish to use data that need to be very precise (or wish to have greater control of the precision and scale of the data) the numeric type is useful. Using the numeric type both precision and the scale can be defined.

Syntax:

numeric(p,s)*defines both precision and scale*

numeric(p) *defines only precision, scale is 0*

numeric *precision and scale are only limited by the implementation's limits*

• Dates and Times:

SQL allows several ways to store dates and times. These data types are some of the most varied amongst the different versions of SQL, so be careful. The basic types are as follows:
The following self-explanatory types use the same syntax: date, time, interval, and timestamp.
type(p) 'value'
*where type is replaced by the type (such as time), the optional value p defines the precision of fractional parts of the second and 'value' represents the input value *

Note on value: value is a literal sting and therefore must be enclosed by single quotation marks, as it is not a numeric value.
Time can also take time-zone values (+/- from GMT)

time(p) without time zone 'value' *no time zone same as just time(p)*

time(p) with time zone 'value' *includes time zone specification*
Example:

time(3) '3:00:001'

time(2) '04:05:06 -8:00'

date '03 May 2004'
Most implementations will allow you to enter times and dates in most commonly used formats, however some are more limited and others require you to specify what formats to accept.

• Null data:

If allowed by the database, null data may be stored for missing values. Null data can be represented in two ways:

null or ''

However 'null' is not valid as SQL represents strings as values in single quotes.

• User defined data types:

SQL allows the creation of new types by using the create type command an existing data types.
Syntax:

create type name (attributes);
Example:
create type ID_Num

( SSN char(9));
This creates a new type called ID_Num, which consists of Social Security Number of the person which is of char type with 9 digits. The new type can now be used just as any other type (such as varchar).