Data
Definition
Language – DDL
CREATE
database
(not in all database systems, in particular, not in MS Access;
can be
complex when available)
table
(varying degrees of complexity)
index
Creating
Tables
Most
database systems allow data
selection from an existing table into a new table without writing a
CREATE
statement..
This
method is useful for
the following:
–
Creating
tables and quickly entering data at the same time
–
Making
backups or partial backups
–
Creating
archive tables
Exercise:
Create a copy of the Suppliers table and call it MySuppliers.
1. Type the following SQL statement in the SQL
design window:
SELECT * INTO MySuppliers
FROM Suppliers;
2. Click the View button (Datasheet View) to see
the results without actually executing the statement.
The query header should be "Query1: Make
Table Query" and the results should
show 29 rows.
3. Change to SQL view (not Design view) and
execute the statement. Click Yes when asked whether you want to continue (only
Access
gives this kind of warning!).
4. Save the query as
"DefineMySuppliers" and close it.
5.
Verify that the query appears on the Query list (note that the icon for
this DDL query is different from the icon for SELECT queries) and that
the new table, MySuppliers, is on the Table list.
6. Compare the new table with the original in
Datasheet and Design view. What, if any,
differences do you find?
Exercise:
Create an extract of the Categories table without pictures and
call it
CategoriesNoPix.
(1. Optional for this
class: Open the Categories table in design
view and
note the column names. If you were
creating such an extract in a work situation, you would need to know
the column names or look them up using data dictionary or other databse
tools. Close the table
before running the SELECT ... INTO ... FROM query.)
2. Type the following SQL statement in the SQL
design window:
SELECT CategoryID,
CategoryName, Description INTO
CategoriesNoPix
FROM Categories;
3. Switch to Datasheet View to see the results
without executing the statement.
4. Switch to SQL view (not Design view) and execute the
statement. Click Yes
when asked about continuing.
5. Save the statement as
"DefineCategoriesNoPix" and close the SQL window.
6. Compare the new table with the original.