Guide to SQL CREATE TABLE Statement and Table Operations

Introduction

Imagine a filing cabinet for data, with drawers for different categories of information. The “CREATE TABLE” statement in SQL is like building a new drawer in that cabinet. You give it a name and define how you’ll organize the information inside, like separate sections for names, dates, and amounts. SQL also lets you copy existing drawers to make new ones, which is helpful if you need a similar filing system but with some changes.

Overview

• The SQL CREATE TABLE statement allows you to set up new tables in a database, akin to creating a new drawer in a filing cabinet to store organized information.
• With CREATE TABLE, you specify the table name, columns, data types, and optional constraints, ensuring each section of your drawer (table) holds the right data.
• A practical example is creating an ‘Employees’ table with various columns, such as EmployeeID, FirstName, LastName, BirthDate, Salary, and DepartmentID, each with specific data types and constraints.
• SQL provides a suite of operations for managing tables, including querying data (SELECT), inserting records (INSERT INTO), updating records (UPDATE), deleting records (DELETE FROM), and altering table structures (ALTER TABLE).
• You can create new tables from existing ones using methods like CREATE TABLE … AS SELECT to copy structure and data or CREATE TABLE … LIKE to replicate only the structure.

Understanding the SQL CREATE TABLE Statement

The CREATE TABLE statement is pivotal in SQL, enabling users to establish the structure of a new table within a database. Let’s explore its syntax and components:

CREATE TABLE table_name (

    column1 datatype constraints,

    column2 datatype constraints,

    ...

    columnN datatype constraints

);

• CREATE TABLE: This is like instructing to build a new data organizer, similar to a filing cabinet drawer.
• table_name: This is the name you give to your organizer so you can easily refer to it later.
• () : Imagine this like the inside of your drawer, where you specify how things will be organized.
• Column 1, column 2, …: These are separate sections you create inside the drawer to hold specific kinds of information. 
• Datatype: This is like defining what information can go in each section of your organizer. Think of it like using folders for letters in one section and envelopes for bigger documents in another. Here are some common data types:
• INT: For whole numbers (like ages or counts).
• VARCHAR: For text with a variable length (like names or addresses).
• DATE: For storing dates.
• FLOAT: For numbers with decimal places (like prices or measurements).
• Constraints (optional): You can set rules for storing information in your organizer. They help keep things neat and organized:
• NOT NULL: This is like saying a section must always contain some information, like a name can’t be blank.
• UNIQUE: This ensures no two-column entries have the same value, like no duplicate social security numbers.
• PRIMARY KEY: This serves as a unique identifier for each row in the table, similar to an ID number assigned to each customer.

Create a Sample Table

Let’s create a sample table named Employees:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    BirthDate DATE,

    Salary DECIMAL(10, 2),

    DepartmentID INT

);

insert into employees values(1,'Abhi','Shek','2024-01-01',10.0,1)

insert into employees values(2,'Ron','Swanson','2023-01-01',5.0,2);

In this example, we have:

• EmployeeID is an integer column serving as the primary key.
• FirstName and LastName are VARCHAR columns for storing employee names with NOT NULL constraints.
• BirthDate is a DATE column for storing employee birth dates.
• Salary is a DECIMAL column to store salaries with a precision of 10 digits and 2 decimal places.
• DepartmentID is an integer column for recording department identifiers.

Performing Operations on Tables

Once a table is created, SQL offers various operations to manage data:

• Querying data: Retrieve information using SELECT statements.
• Inserting records: Populate tables using INSERT INTO statements.
• Updating records: Modify existing data with UPDATE statements.
• Deleting records: Remove unwanted data using DELETE FROM statements.
• Altering table structure: Modify the table schema using ALTER TABLE statements.

Creating Tables from Existing Tables

In SQL, you can create new tables based on existing tables to replicate or modify schema structures. There are two primary methods:

Using CREATE TABLE … AS SELECT

This method allows you to create a new table based on the result set of a SELECT query from an existing table:

CREATE TABLE new_table_name

AS

SELECT column1, column2, ...

FROM existing_table_name;

Example:

To create a table EmployeesBackup with the same structure and data as Employees:

CREATE TABLE EmployeesBackup

AS

SELECT *

FROM Employees;

Using CREATE TABLE … LIKE

This method creates a new table with the same structure as an existing table, but without copying data:

CREATE TABLE new_table_name

LIKE existing_table_name;

Example:

To create an empty table, EmployeesCopy with the same structure as Employees:

CREATE TABLE EmployeesCopy
LIKE Employees;

Conclusion

Mastering the CREATE TABLE statement and its variations in SQL is crucial for effective database management and application development. These statements allow for precise control over data structures and enable seamless replication and modification of table schemas. By understanding these fundamentals, SQL users gain essential skills for efficiently organizing and manipulating data within relational databases. Always ensure compatibility with your specific SQL dialect and database system when applying these operations.

Frequently Asked Questions

Abhishek Kumar 24 Jun, 2024

Hello, I'm Abhishek, a Data Engineer Trainee at Analytics Vidhya. I'm passionate about data engineering and video games I have experience in Apache Hadoop, AWS, and SQL,and I keep on exploring their intricacies and optimizing data workflows :)