SQL Basics | DDL: Data Definition Language | TABLE

A table is the basic structure for storing data in a database. A table is divided into columns and rows. Each row represents one record, and each column represents part of that record. For example, if a table stores customer data, its columns may include name, address, and date of birth. When defining a table, you specify each column name and the data type of that field.

Data can take many forms. It may be an integer, such as 1; a real number, such as 0.55; a string, such as 'sql'; a date and time, such as '2000-JAN-25 03:22:22'; or binary data. These forms are called data types. When defining a table, you must define the data type for each column. For example, the data type of a name field may be char(50), which represents a string of 50 characters. However, data types differ by database, so check the documentation for the database you are using when defining a table.

CREATE TABLE

The CREATE TABLE statement is used to create a table.

CREATE TABLE syntax

CREATE TABLE "table_name" (
    "field_name1" "data_type",
    "field_name2" "data_type",
    "field_name3" "data_type",
    ...
);

For example, to create customer data, enter SQL as shown below.

CREATE TABLE customer (
    first_name char(50),
    last_name char(50),
    address char(50),
    city char(50),
    country char(25),
    birth_date datetime
);

DROP TABLE

The DROP TABLE statement is used to delete a data table.

There are cases where a table must be removed from a database. If it is not removed when needed, it can cause serious problems because the database administrator (DBA) may not be able to manage the database efficiently. SQL provides the DROP TABLE statement to remove a table. The DROP TABLE statement is written as follows.

DROP TABLE "table_name";

To delete a table, enter the following.

DROP TABLE customer;

TRUNCATE TABLE

When you delete a table with DROP TABLE, the entire table disappears. In contrast, when you use TRUNCATE TABLE, all data in the table is removed, but the table itself remains.

TRUNCATE TABLE "table_name";

To delete the data in a table, enter the following.

TRUNCATE TABLE customer;