SQL CREATE TABLE Statement

Sure! Creating a table in SQL involves defining the structure of the table, including its columns and their data types. Here's a detailed explanation along with examples:

Basic Syntax

The basic syntax for creating a table in SQL is:

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

• table_name: The name of the table.
• column1, column2, ...: The names of the columns in the table.
• datatype: The type of data the column can hold (e.g., INT, VARCHAR, DATE, etc.).
• constraints: Optional rules that can be applied to the data in the columns (e.g., PRIMARY KEY, NOT NULL, UNIQUE, etc.).

Example 1: Creating a Simple Table

Let's create a table named employees with four columns: id, first_name, last_name, and hire_date.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);

In this example:

• id is an integer and is the primary key, meaning it uniquely identifies each row in the table.
• first_name and last_name are variable character fields with a maximum length of 50 characters, and they cannot be null.
• hire_date is a date field.

Example 2: Creating a Table with Constraints

Let's create a table named products with some constraints.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

In this example:

• product_id is an integer and the primary key.
• product_name is a variable character field with a maximum length of 100 characters and cannot be null.
• price is a decimal field with 10 digits in total, 2 of which are after the decimal point, and it must be greater than 0.
• stock_quantity is an integer field with a default value of 0.
• category_id is an integer field that references the category_id column in the categories table (assuming the categories table exists).

Example 3: Creating a Table with Auto-Increment

Let's create a table named orders where the order_id column auto-increments.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example:

• order_id is an integer that auto-increments with each new row and is the primary key.
• order_date is a date field and cannot be null.
• customer_id is an integer field that references the customer_id column in the customers table (assuming the customers table exists).
• total_amount is a decimal field with 10 digits in total, 2 of which are after the decimal point.

These examples cover some of the common use cases and constraints you might encounter when creating tables in SQL.