Databases Reference
In-Depth Information
NOTE
Unlike some other versions of SQL, Access SQL permits the use of spaces within table and column names. There is a restric-
tion, however, on the way such names are used in SQL commands. When you use a name containing a space in Access SQL,
you must enclose it in square brackets. For example, if the name of the CreditLimit column were changed to Credit Limit (with
a space between
Credit
and
Limit
), you would write the column as [Credit Limit] because the name includes a space.
72
NOTE
In systems that permit the use of uppercase and lowercase letters in table and column names, you can avoid using spaces by capi-
talizing 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 let-
ters, some programmers use an underscore to separate words. For example, the name of the credit limit column would be
CREDIT_LIMIT.
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 numbers 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 con-
tain letters and other special characters and for fields that contain numbers that will not be used
in calcualtions. Because neither sales rep numbers nor customer numbers will be used in any cal-
culations, 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, 2010, could
be stored as 12-MAY-2010 or 5/12/2010.
●
EXAMPLE 1
Use SQL to create the Rep table by describing its layout.
The general 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) )
;
