SQL CREATE TABLE statement is used to create table in a database.
Let’s see the simple syntax to create the table.
CREATE TABLE Syntax:
CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);
Here table_name is name of the table, column is the name of column.
Users can define the table structure by specifying the column’s name and data type in the CREATE TABLE command.
The datatype parameter specifies the type of data the column can hold (e.g. VARCHAR(size), CHAR(size), INT, DATE, DATETIME, etc.).
The column parameters specify the names of the columns of the table.
This statement also allows to create table with constraints, that define the rules for the table.
SQL CREATE TABLE Example in MySQL:
Let’s look at some examples of CREATE TABLE command in SQL and see how to create table in SQL.
CREATE TABLE Employee Example
In this example, we will create table in SQL with primary key, named “Employee”.
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DOB DATE,
Age INT CHECK (Age >= 0 AND Age <= 85),
Country VARCHAR(50),
Address CHAR (25),
Phone INT(10),
Department VARCHAR(50) as Dept,
Salary DECIMAL (10,2)
);
Semicolon ‘;’ indicates the end of the SQL query.
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:
SQL> Describe Employee;
Now you have the Employee table available in your database and you can use to store required information related to students.
To add data to the table, we use INSERT INTO command, the syntax is as shown below:
Syntax:
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
Example Query
This query will add data in the table named Subject:
INSERT INTO Employee (EmployeeID, FirstName, LastName, DOB, Age, Country, Address, Phone, Department, Salary)
VALUES (1, ‘James’, ‘Mary’, ‘1996-01-01’, 28, ‘America’, ‘Maple Street’, 88888888, ‘Developer’, 50000),
(2, ‘Michael ‘, ‘Patricia’, 1994-05-07, 30, ‘Australia’, ‘8th Street’, 123456789, ‘Technician’, 40000),
(3, ‘Robert’, ‘Jennifer’, 1997-03-05, 27, ‘Canada’,’Main Street’, 099999999, ‘Analyst’, 70000),
(4, ‘John’, ‘Linda’, 2000-09-02, 24, ‘Austria’,’Main Avenue’, 777733333, ‘HR’, 65000),
(5, ‘David’, ‘Elizabeth’, 1999-08-23, 25, ‘Spain’,’Near Club’, 666666222, ‘Finance’, 85000);
Create Table From Another Table:
We can also use CREATE TABLE to create a copy of an existing table. In the new table, it gets the exact column definition all columns or specific columns can be selected.
If an existing table was used to create a new table, by default the new table would be populated with the existing values ??from the old table.
Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM existing_table_name
WHERE ….;
Query:
CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;
