Skip to content

DDL: Tables

In its simplest form, the definition of a table is simply a list of the columns that the table contains with their respective datatypes. The following statement could therefore be used to create the table STUDENT:

1
2
3
4
5
6
    CREATE TABLE student (
        matric_no     VARCHAR2(8),
        first_name    VARCHAR2(20),
        last_name     VARCHAR2(20),
        date_of_birth DATE
    );

Notice that each column definition consists of the column name and datatype and that the list is separated by commas. When writing DDL statements, it is useful to lay them out like the example so that they can be read easily. This helps to avoid simple mistakes like forgetting the comma between two columns or missing out the final bracket.

In this example, we can store first and last names of up to 20 characters each. If we discover that this is not sufficient, the table definition can be altered using a statement like the following:

1
    ALTER TABLE student MODIFY last_name VARCHAR2(25);

The ALTER TABLE statement can be used to make other changes to the table definition besides changing column datatypes. There are certain occasions where this is useful, and some of them will be illustrated in this week's practical exercises. However, in many cases it is quicker to remove the table and create it again with an updated definition. A table can be removed using a statement such as:

1
    DROP TABLE student;

Each of these statements can be used with further optional clauses which modify their behaviour. A full description of their syntax can be found in the Oracle SQL Language Reference.

Further reading

Create Table

The SQL CREATE TABLE Statement