Adding and Modifying Tables

Introduction

So far, we have read existing tables.

It is also possible to create new tables with a query.

You do this when you want to store information for which no existing table is available.

---

1. Creating a Table

We are going to create a new table. A table to create details of people attending a training.

For this, the CREATE TABLE statement is used.

In the example below, we do the following:

• Use the CREATE TABLE statement.
• Specify the name of the new table: trainees.
• Name the columns for the new table:
  • First the column name, then the data type.
  • Note: you create the columns between parentheses.

CREATE TABLE trainees (
  first_name VARCHAR (50),
  last_name VARCHAR (50),
  email_address VARCHAR (255)
);

• After executing this query, the table trainees is created.
  • VARCHAR stands for a data type with a variable number of characters.
  • The number between the parentheses indicates the maximum number of characters.
• You can retrieve data from it with the query below.
  • The table does not yet contain any rows.

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address

Columns: 3
Rows: 0

---

2. Deleting a Table

In addition to creating, you can also delete a table with a query.

For this, the DROP TABLE statement is used.

With the query below, we delete the table we just created:

DROP TABLE trainees;

• After executing this query, the table trainees is deleted.
• If we try to retrieve data with the query below, we get an error.
  • Logically, the table has been deleted.

SELECT
  *
FROM
  trainees;

Output:
relation "trainees" does not exist

---

3. Specifying Columns

We now know how to create and delete a table.

We have given columns a name and data type.

This can be further specified.

In the example below, we do the following:

• Again create the table trainees with some columns.
• We specify the columns in more detail:
  • We add NOT NULL to all columns.
    • NOT NULL indicates that the column cannot contain empty values.
  • We also add UNIQUE to the column email_address.
    • UNIQUE enforces that only unique values are allowed.
      • Each row must therefore contain a different email address.

CREATE TABLE trainees (
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email_address VARCHAR (255) UNIQUE NOT NULL
);

Specifications such as UNIQUE and NOT NULL are called constraints.

---

Summary

• CREATE TABLE creates a new table.
  • You specify:
    • The name of the table.
    • The new columns:
    • Name and data types.
    • Between parentheses.
  • Example:

CREATE TABLE trainees (
  first_name VARCHAR (50),
  last_name VARCHAR (50),
  email_address VARCHAR (255)
);

• DROP TABLE deletes a table.
• Columns can be further specified with constraints, including:
  • NOT NULL: no missing values.
  • UNIQUE: only unique values.

• Note: among other things, data types can differ in different SQL dialects. This can lead to differences in how you create tables in different dialects.