close
The Wayback Machine - https://web.archive.org/web/20250114133715/https://www.geeksforgeeks.org/sql-create-table/
Open In App

SQL CREATE TABLE

Last Updated : 27 Nov, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

The SQL CREATE TABLE statement is a foundational command used to define and structure a new table in a database. By specifying the columns, data types, and constraints such as PRIMARY KEY, NOT NULL, and CHECK, helps you design the database schema.

In this article, we’ll learn the syntax, best practices, and practical examples of using the CREATE TABLE statement in SQL. We’ll also cover how to create tables from existing data and troubleshoot common errors.

SQL CREATE TABLE Statement

To create a new table in the database, use the SQL CREATE TABLE statement. A table’s structure, including column names, data types, and constraints like NOT NULL, PRIMARY KEY, and CHECK, are defined when it is created in SQL.

The CREATE TABLE command is a crucial tool for database administration because of these limitations, which aid in ensuring data integrity.

Syntax:

To create a table in SQL, use this CREATE TABLE syntax:

CREATE table table_name
(
Column1 datatype (size),
column2 datatype (size),
.
.
columnN datatype(size)
);

  • table_name: The name you assign to the new table.
  • column1, column2, … : The names of the columns in the table.
  • datatype(size): Defines the data type and size of each column.

Here table_name is name of the table, column is the name of column

SQL CREATE TABLE Example

Let’s look at examples of CREATE TABLE command in SQL and see how to create table in SQL.

CREATE TABLE in SQL and Insert Data

In this example, we will create a new table and insert data into it.

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:

table created

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 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 table and insert data

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;

Output:

create table from another table

Note: We can use * instead of column name to copy whole table to another table.

Important Points About SQL CREATE TABLE Statement

  • CREATE TABLE statement is used to create new table in a database.
  • It defines the structure of table including name and datatype of columns.
  • The DESC table_name; command can be used to display the structure of the created table
  • We can also add constraint to table like NOT NULL, UNIQUE, CHECK, and DEFAULT.
  • If you try to create a table that already exists, MySQL will throw an error. To avoid this, you can use the CREATE TABLE IF NOT EXISTS syntax.

Conclusion

The SQL CREATE TABLE statement is essential for setting up tables and defining data structures within a database. Knowing how to create a table in SQL with the CREATE TABLE SQL syntax helps you establish reliable tables with appropriate constraints.

Whether you’re creating an Employee table in SQL with primary keys or duplicating tables with CREATE TABLE AS SELECT, mastering this command is vital for managing data effectively.

In this guide, we covered SQL CREATE TABLE syntax with various examples, from basic to advanced usage. By applying these practices, you can confidently build tables in SQL for effective data organization and retrieval.

FAQs

1. How do you create a table in SQL?

In SQL, you can create a table by using the CREATE TABLE command, then the table name and column definitions. Each column’s data type and limitations can be specified.

2. How to create a query in SQL?

To create a query in SQL, you start with a SQL statement that retrieves or manipulates data, such as SELECT, INSERT, UPDATE, or DELETE.

3. How to create a duplicate table in SQL Server?

In SQL Server, you can create a duplicate table using the CREATE TABLE AS SELECT statement. This approach copies the structure and optionally the data from an existing table to a new table.

Get IBM Certification and a 90% fee refund on completing 90% course in 90 days! Take the Three 90 Challenge today.

Master Data Analysis using Excel, SQL, Python & PowerBI with this complete program and also get a 90% refund. What more motivation do you need? Start the challenge right away!


Next Article

Similar Reads

three90RightbarBannerImg