SQL CREATE TABLE

In SQL, creating a table is one of the most essential tasks for structuring your database. The CREATE TABLE statement defines the structure of the database table, specifying column names, data types, and constraints such as PRIMARY KEY, NOT NULL, and CHECK. Mastering this statement is fundamental to ensuring that our data is organized and easily accessible.

What is the SQL CREATE TABLE Statement?

The CREATE TABLE command in SQL is used to define a new table within a database. 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.

Whether we are creating tables for storing customer information, product catalogs, or employee records, the CREATE TABLE statement is our starting point. The CREATE TABLE command is a crucial tool for database administration because of these limitations, which help in ensuring data integrity.

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.

Practical Example: Creating a Table in SQL

Let’s walk through a practical example where we create a Customer table that stores customer data. We will define various columns such as CustomerID, CustomerName, Country, Age, and Phone with appropriate data types and constraints.

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:

Explanation of the Example:

• CustomerID is an integer and serves as the PRIMARY KEY, ensuring each record is unique.
• CustomerName, LastName, and Country are VARCHAR fields to store variable-length text.
• Age has a CHECK constraint, ensuring it’s within the range of 0 to 99.
• Phone is an integer field, although in real scenarios, a VARCHAR would often be used for storing phone numbers to allow for leading zeros and formatting.

Inserting Data into the Newly Created Table

After creating the table, you can use INSERT INTO command to add data into it. Here’s how to add some sample records into the Customer 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:

Note: If you're working with a large amount of data, consider using bulk inserts or importing data from external files to optimize performance.

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 CREATE TABLE AS SELECT command allows us to duplicate an entire table or select specific columns to form a new one. 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 ....;

In this example, we create a new table SubTable that contains just the CustomerID and CustomerName columns from the Customer table. This method is useful for creating backups or performing quick data migrations.

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

To ensure the smooth creation and management of your tables, keep these points in mind:

1. Adding Constraints: The CREATE TABLE statement can also define constraints like NOT NULL, UNIQUE, and DEFAULT. Example: Age INT NOT NULL

2. Handling Table Existence: If you attempt to create a table that already exists, SQL will throw an error. To avoid this, you can use the IF NOT EXISTS clause.

CREATE TABLE IF NOT EXISTS Customer (...);

3. Data Types and Sizes: Always define appropriate data types for each column (e.g., VARCHAR(50) for names and INT for IDs) to optimize performance and storage.

4. Viewing Table Structure: After creating a table, use the following command to view the structure of your table:

DESC table_name;

5. Modifying Table Structure: If you need to change the table’s structure after creation (e.g., renaming a column, adding a new column), use the ALTER TABLE statement.

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.