SQL CREATE TABLE

The CREATE TABLE statement in SQL is used to create a new table in a database. A table is defined with a unique name and consists of columns, where each column has a specific name and data type.
The structure of a table defines how data is stored and accessed within the database.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);
        

- table_name: The name of the table you want to create. It must be unique within the database.
- datatype: Specifies the type of data (e.g., INTEGER, VARCHAR, DATE) the column will store.
- constraint: Defines rules for the column, such as PRIMARY KEY, NOT NULL, UNIQUE, etc.

Example

Let us create a table called Employees with the following structure:
- ID: An integer that uniquely identifies each employee (Primary Key).
- Name: The employee's full name (String).
- Age: The employee's age (Integer).
- Department: The department the employee belongs to (String).

-- Create Employees Table
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Department VARCHAR(50)
);

-- Insert Data into Employees Table
INSERT INTO Employees (ID, Name, Age, Department)
VALUES (1, 'Alice', 30, 'HR'),
       (2, 'Bob', 25, 'IT'),
       (3, 'Charlie', 35, 'Finance');

-- Display Table Content
SELECT * FROM Employees;
            

Explanation

- The CREATE TABLE statement creates the Employees table with columns for ID, Name, Age, and Department.
- The INSERT INTO statement populates the table with sample data for three employees.
- The SELECT * statement retrieves all rows and columns from the table, displaying the complete table content.

Best Practices

- Always define a PRIMARY KEY for tables to ensure each row is uniquely identifiable.
- Use appropriate datatypes for columns to maintain data integrity and efficiency.
- Use constraints such as NOT NULL or UNIQUE to enforce rules on data entry.