Data Definition
Language – DDL
CREATE
database (not in all database systems; can be
complex when available)
table (varying degrees of complexity)
index
Also available in other database
systems: CREATE trigger, procedure, function, role,
user)
CREATE TABLE
Used to
create the tables where data will be stored.
=========
Example:
1. Open the Orders table in design view.
2. Data type specified for each field (column)
Autonumber (Other databases: identity)
Text (char(n), varchar(n))
Number
Date/Time
Currency (money)
Additional data types not used in this
table:
Memo (long (Oracle, up to 2 GB text)
Yes/No (boolean)
OLE Object (blob, clob, raw)
Some additional data types in other
database systems:
Timestamp
Interval
Enum
Set
3. Select each field and notice its properties
in the Field Properties grid.
To view table structure and column
properties in other databases:
describe
tablename
and select [properties] from [system tables]
4. Close the Orders table.
Exercise:
Create a table to store personnel data, with a StaffID column as primary
key
1. Type this SQL statement in the SQL query
design window:
CREATE TABLE Personnel (
StaffID text(9)
CONSTRAINT StaffPK PRIMARY KEY,
LastName text(15)
not null,
FirstName text(15)
not null,
Birthday date,
Department text(12)
null);
2. Execute the statement. If Access reports syntax errors, find and
correct them.
3. Save the query as DefinePersonnel and close
it.
4. Run a query to select all records from the
new table:
SELECT * FROM Personnel;
The query returns one blank record (in
other databases: 0 rows). Close the query.
4. Open the new table in datasheet view – it is
empty and ready for data entry.
5. Change to design view and compare with the
SQL statement.
6. Choose View / Indexes and compare with the
constraint created on StaffID.