Create Table in MySQL

To create a table in MySQL, you use the CREATE TABLE statement followed by the table name and a list of columns with their data types and optional constraints. Here's the basic syntax:

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

Let's break down the components:

• CREATE TABLE: This is the SQL statement used to create a new table.

• table_name: This is the name of the table you want to create. Choose a descriptive name that reflects the purpose of the table.

• column1, column2, ..., columnN: These are the names of the columns in the table.

• datatype: This specifies the data type of each column. For example, INT, VARCHAR, DATE, etc.

• constraints: These are optional and define rules or limits for the data that can be stored in each column. Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, etc.

Here's an example of creating a simple table named students with three columns: student_id, name, and age:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

This statement creates a table named students with three columns:

1. student_id: An integer column serving as the primary key.
2. name: A variable character (string) column with a maximum length of 100 characters.
3. age: An integer column to store the age of the student.

After executing this SQL statement, the students table will be created in your MySQL database. You can then use it to store data by inserting rows into it using the INSERT INTO statement.

-- Create departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Insert sample data into departments table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

-- Create employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert sample data into employees table
INSERT INTO employees (employee_id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1),
(4, 'David', 3);

-- Create customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Insert sample data into customers table
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Smith');

-- Create products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

-- Insert sample data into products table
INSERT INTO products (product_id, product_name) VALUES
(101, 'Laptop'),
(102, 'Tablet'),
(103, 'Smartphone');

-- Create orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert sample data into orders table
INSERT INTO orders (order_id, customer_id, product_id, order_date) VALUES
(1, 1, 101, '2024-01-15'),
(2, 2, 102, '2024-01-16'),
(3, 1, 103, '2024-01-17'),
(4, 3, 101, '2024-01-18');