SQL TABLE Statements: Creating, Modifying, and Deleting Tables

This section covers the essential SQL commands for managing tables within a database: creating new tables, modifying existing ones (adding or deleting columns), and deleting tables completely.

CREATE TABLE

Creating a New Table

The CREATE TABLE statement is fundamental for building new tables in your database. You define the table's name and the columns it will contain, specifying the data type for each column.

Syntax


CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ...
);

Example: Creating the "Persons" Table

Syntax


CREATE TABLE Persons (
  PersonID INT,
  LastName VARCHAR(255),
  FirstName VARCHAR(255),
  Address VARCHAR(255),
  City VARCHAR(255)
);

Output

A new table named "Persons" is created with the specified columns and data types. The table is initially empty.

Creating a Table from Another Table

You can create a new table as a copy of an existing table (or a subset of an existing table) using CREATE TABLE ... AS SELECT. This is a very efficient way to duplicate data or create a new table with specific columns from an existing one.

Syntax


CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name;

Example: Copying Data into "TestTable"

Syntax


CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Output

A new table named "TestTable" is created, containing the 'customername' and 'contactname' columns and their data copied from the 'customers' table.

ALTER TABLE

Adding a Column

The ALTER TABLE statement lets you modify existing tables. You can add, delete, or modify columns, and add or remove constraints.

Syntax (Add Column)


ALTER TABLE table_name
ADD column_name datatype;

Example: Adding an Email Column

Syntax


ALTER TABLE Customers
ADD Email VARCHAR(255);

Output

Adds an 'Email' column to the 'Customers' table. Initially, the 'Email' column will contain NULL values for all existing rows.

Deleting a Column

Syntax (Drop Column)


ALTER TABLE table_name
DROP COLUMN column_name;

Example: Deleting the Email Column

Syntax


ALTER TABLE Customers
DROP COLUMN Email;

Output

Removes the 'Email' column from the 'Customers' table. The data in that column is permanently lost.

DROP TABLE

Deleting a Table

The DROP TABLE command permanently deletes a table from the database. Use this with extreme caution, as this action is irreversible (unless you have a backup).

Syntax


DROP TABLE table_name;

Example: Deleting the "Shippers" Table

Syntax


DROP TABLE Shippers;

Output

The "Shippers" table and all its data are permanently deleted.

TRUNCATE TABLE

Deleting Data from a Table

The TRUNCATE TABLE command removes *all* data from a table, but it leaves the table structure itself intact. This is generally faster than deleting rows one by one using DELETE but is still a destructive operation.

Syntax


TRUNCATE TABLE table_name;

Example: Truncating the "Categories" Table

Syntax


TRUNCATE TABLE Categories;

Output

All data is removed from the "Categories" table. The table structure remains.