SQL CREATE TABLE Statement

by

The SQL CREATE TABLE Statement

To create a table, you specify the table name and define columns along with their data types in sql.
The SQL CREATE TABLE is used to create a new table in a database.

Syntax of SQL CREATE TABLE statement

SQL CREATE TABLE

CREATE a table name.
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype,
….
);

Columns specify the names of the columns in the table.

The datatype specifies the type of data in a column. Datatypes like VARCHAR, CHAR, INTEGER, DATE, and more

NOTE: For more available data types, go to Data Types Reference.

Example of SQL CREATE TABLE

CREATE TABLE Employee (
EmployeeID int,
LastName varchar(250),
FirstName varchar(250),
Department VARCHAR(50),
City varchar(250)
);

The EmployeeID, LastName, FirstName, Department, and City columns are of data type INT and VARCHAR. It will hold characters, and the maximum length for these data type fields is between 50 and 250 characters.

OR

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(60),
LastName VARCHAR(60),
Email VARCHAR(100),
Birthdate DATE
);

Create a table using Another Table


A copy of an existing table can also be created using the CREATE TABLE statement.

If you create a new table using an existing table named Employees, the new table (Managers) will fill up with the existing values from the old table.

Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM existing_table
WHERE ……;

The following SQL creates a new table called Managers, which is a copy of (Existing table) the Employees table.

CREATE TABLE Managers AS
SELECT *
FROM Employees
WHERE Department = ‘Manager’;

SQL Drop Table Statement:
The DROP TABLE statement is used to drop an existing table from a database in SQL.

Syntax of SQL DROP TABLE Statement
DROP TABLE table_name;

NOTE: Before dropping a table, please take a backup of the table. Because dropping or Deleting a table from a database will lose the complete data of the table

SQL DROP TABLE Example
The below SQL statement drops the existing table managers.

DROP TABLE Managers;

OR

DROP TABLE Customers;

SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data from a table but not the table structure.

Syntax SQL TRUNCATE TABLE
TRUNCATE TABLE table_name;

TRUNCATE TABLE Managers;

Leave a Comment