[ Home ] [ Up ] [ DML Statements ] [ DDL Statements ] [ Transaction Statements ]

Data Definition Language

Schemas and Schema Objects

A database consists of a set of schemas, each of which contains, or owns, a set of schema objects, such as tables, etc.

Each schema object is created using a CREATE statement.

Creating Schema Objects

Each schema object typically has an associated CREATE statement which is used to create an instance of that object.

Creating a Table

To create a table, you use the CREATE TABLE statement. For example:

CREATE TABLE employees
{
  employeeID   INTEGER,
  name         CHARACTER(40),
  department   INTEGER,
  age          INTEGER,
  salary       DECIMAL(7,2)
};

which creates the Employees table specified earlier. Each column is specified using a name and a data type.

SQL Data Types

Here are some of the valid SQL data types:

Changing Schema Objects

Once a schema object has been created, it may be possible to change its attributes (depending on the type of object, and the support from the database vendor), using an ALTER statement.

Changing the Attributes of a Table

You can change some (usually not all) of the attributes of a table using the ALTER TABLE statement:

ALTER TABLE employees
ADD COLUMN last_review DATE;

ALTER TABLE departments
DROP COLUMN manager;

Removing Schema Objects

You can use a DROP statement to remove an existing schema object.

Removing a Table

You use the DROP TABLE statement to remove an existing table:

DROP TABLE employees;

Note: Do not confuse the DROP statement with the DELETE statement!