Data Definition Language – DDL

 

CREATE


The CREATE statement is used to create the main data storage objects:

            database           (not in all database systems, in particular, not in MS Access; can be complex when available)

            table                 (varying degrees of complexity)

            index

           

            Also available in other database systems:  CREATE  trigger, procedure, function, role, user)   

      

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