Database Reference
In-Depth Information
NOTE
In systems that permit the use of uppercase and lowercase letters in table and column names, you can avoid using spaces by
capitalizing the first letter of each word in the name and using lowercase letters for the remaining letters in the words. For
example, the name of the credit limit column would be CreditLimit. In systems that do not permit the use of spaces or mixed-
case letters, some programmers use an underscore to separate words. For example, the name of the credit limit column would
be CREDIT_LIMIT.
74
For each column in a table, you must specify the type of data that the column can store. Although the
actual data types will vary slightly from one implementation of SQL to another, the following list indicates
the data types you will often encounter:
INTEGER: Stores integers, which are numbers without a decimal part. The valid data range is
-
2147483648 to 2147483647. You can use the contents of INTEGER fields for calculations.
SMALLINT: Stores integers, but uses less space than the INTEGER data type. The valid data
range is
32768 to 32767. SMALLINT is a better choice than INTEGER when you are certain
that the field will store numbers within the indicated range. You can use the contents of
SMALLINT fields for calculations.
-
DECIMAL(p,q): Stores a decimal number p digits long with q of these digits being decimal
places. For example, DECIMAL(5,2) represents a number with three places to the left and two
places to the right of the decimal. You can use the contents of DECIMAL fields for calculations.
(Unlike other SQL implementations, Access doesn
t have a DECIMAL data type. To create num-
bers with decimals, you must use either the CURRENCY or NUMBER data type. Use the
CURRENCY data type for fields that will contain currency values; use the NUMBER data type for
all other numeric fields.)
'
CHAR(n): Stores a character string n characters long. You use the CHAR type for fields that
contain letters and other special characters and for fields that contain numbers that will not be
used in calculations. Because neither sales rep numbers nor customer numbers will be used in
any calculations, for example, both of them are assigned CHAR as the data type. (Some DBMSs,
such as Access, use TEXT rather than CHAR, but the two data types mean the same thing.)
DATE: Stores dates in the form DD-MON-YYYY or MM/DD/YYYY For example, May 12, 2013,
could be stored as 12-MAY-2013 or 5/12/2013.
EXAMPLE 1
Use SQL to create the Rep table by describing its layout.
The CREATE TABLE command for the Rep table is as follows:
CREATE TABLE Rep
(RepNum CHAR(2),
LastName CHAR(15),
FirstName CHAR(15),
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Commission DECIMAL(7,2),
Rate DECIMAL(3,2) )
;
