SQL REFERENCE - Concepts of Database Management - page 348

Clause Description Required?

CREATE TABLE table name Indicates the name of table to be created. Yes

348

(column and data type list) Indicates the columns that make up the table along with their

corresponding data types (see the “Data Types” section).

Yes

FIGURE B-3 CREATE TABLE command

The following CREATE TABLE command creates the Rep table and its associated columns and data types:

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) )

;

Access version:

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 CURRENCY,

Rate NUMBER )

;

Note: 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.

CREATE VIEW

Use the CREATE VIEW command to create a view. Figure B-4 describes the CREATE VIEW command.

Clause Description Required?

CREATE VIEW view name AS Indicates the name of the view to be created. Yes

query Indicates the defining query for the view. Yes

FIGURE B-4 CREATE VIEW command

The following CREATE VIEW command creates a view named Housewares, which consists of the part num-
ber, description, on hand, and price for all rows in the Part table on which the Class is HW:

CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class = 'HW'
;

DATA TYPES

Figure B-5 describes the data types that you can use in a CREATE TABLE command.