SQL CREATE TABLE
Last Updated :
09 Apr, 2024
In the SQL database for creating a table, we use a command called CREATE TABLE.
SQL CREATE TABLE Statement
A Table is a combination of rows and columns. For creating a table we have to define the structure of a table by adding names to columns and providing data type and size of data to be stored in columns.
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
SQL CREATE TABLE Example
Let us create a table to store data of Customers, so the table name is Customer, Columns are Name, Country, age, phone, and so on.Â
CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age INT CHECK (Age >= 0 AND Age <= 99),
Phone int(10)
);
Output:
Â
Insert Data  into Table
To add data to the table, we use INSERT INTO, the syntax is as shown below:
Syntax:
//Below query adds data in specific column, (like Column1=Value1)//
Insert into Table_name(Column1, Column2, Column3)
Values (Value1, value2, value3);
//Below query adds data in table in sequence of column name(Value1 will beÂ
added in Column1 and so on)//
Insert into Table_name
Values (Value1, value2, value3);
//Adding multiple data in the table in one go//
Insert into Table_name
Values (Value01, value02, value03),
(Value11, value12, value13),
(Value21, value22, value23),
(ValueN1, valueN2, valueN3)
Example Query
This query will add data in the table named SubjectÂ
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
(2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
(3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
(4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
Output:
Create a Table Using 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;
Output:
- We can use * instead of column name to copy whole table to another table.
Note : In this query another table will be created with all the columns instead of just two columns.
Syntax :
CREATE TABLE new_table_name AS
  SELECT *
  FROM existing_table_name
  WHERE ….;
Query :
CREATE TABLE customer_copy AS SELECT * FROM customer;
Output :
- You can also use LIMIT to insert specific number of records from old table. In below example it will populate new table with first three records.
Syntax :
CREATE TABLE <new_table_name> AS SELECT * FROM <old_table_name> LIMIT <numeric_value>;
Query :
CREATE TABLE customer_copy AS SELECT * FROM customer LIMIT 3;
Output :
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...