SQL CREATE TABLE

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. To create a table in SQL, use this CREATE TABLE syntax:

Syntax:

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

Key Terms

• 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.

Example of SQL CREATE TABLE

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. 

Query:

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 the Created Table

After creating the table, you can use INSERT INTO command to add data to it. Here’s how to add some sample records into the Customer table:

Syntax:

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example Query

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 From Another Table

We can also create a new table based on the structure (and optionally the data) of an existing table. The following query creates a new table called SubTable that contains CustomerID and CustomerName from the existing Customer 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:

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

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.

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.

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.

S

Saylli Walve

Improve

Article Tags :

• Databases
• SQL
• SQL-Clauses-Operators