Database Reference
In-Depth Information
re describing a table that will be
named Rep. The table contains nine fields: RepNum, LastName, FirstName, Street, City, State, Zip, Commission,
and Rate. RepNum is a character field that is two positions in length. LastName is a character field with
15 characters. Commission is a numeric field that stores seven digits, including two decimal places. Similarly,
Rate is a numeric field that stores three digits, including two decimal places. Because many versions of SQL
require you to end a command with a semicolon, commands in this text will end with semicolons.
In this SQL command, which uses the data definition features of SQL, you
'
75
NOTE
In SQL, commands are free-format. No rule says that a specific word must begin in a particular position on the line. The previ-
ous SQL command could have been written 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) )
;
The manner in which the first CREATE TABLE command was written simply makes the command more readable. In general,
you should strive for such readability when you write SQL commands.
SIMPLE RETRIEVAL
The basic form of an SQL retrieval command is SELECT-FROM-WHERE. After the word SELECT, you list the
fields you want to display in the query results. This portion of the command is called the SELECT clause.
The fields will appear in the query results in the order in which they are listed in the SELECT clause. After
the word FROM, you list the table or tables that contain the data to display in the query results. This portion
of the command is called the FROM clause. Finally, after the word WHERE, you list any conditions that you
want to apply to the data you want to retrieve, such as indicating that the credit limit must be $10,000. This
portion of the command, which is optional, is called the WHERE clause.
There are no special formatting rules in SQL
the examples in this text include the SELECT, FROM, and
WHERE clauses on separate lines to make the commands more readable. In addition, this text uses a com-
mon style in which words that are part of the SQL language, called reserved words, appear in all uppercase
letters. All other words in commands appear in a combination of uppercase and lowercase letters.
—
EXAMPLE 2
List the number, name, and balance of all customers.
t need to put
any restrictions on the data to retrieve. Figure 3-2a shows the query to select the number, name, and balance
of all customers, using the SQL implementation in Access 2010.
Because you want to list all customers, you won
'
t need to use the WHERE clause
—
you don
'
