SQL ALTER TABLE Tutorial

Example Table

We will use the following table named employees for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        name VARCHAR(50),
        department VARCHAR(50),
        salary DECIMAL(10, 2)
    );

    INSERT INTO employees (employee_id, name, department, salary) VALUES
    (1, 'Alice', 'HR', 50000.00),
    (2, 'Bob', 'IT', 60000.00),
    (3, 'Charlie', 'Finance', 55000.00),
    (4, 'David', 'IT', 62000.00),
    (5, 'Eve', 'HR', 48000.00);
    

Employees Table

employee_id	name	department	salary
1	Alice	HR	50000.00
2	Bob	IT	60000.00
3	Charlie	Finance	55000.00
4	David	IT	62000.00
5	Eve	HR	48000.00

Using SQL ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Example: Adding a Column

To add a column named email to the employees table:

    ALTER TABLE employees
    ADD email VARCHAR(100);
    

Result:

Query	ALTER TABLE employees ADD email VARCHAR(100);
Result	employee_id: 1, name: Alice, department: HR, salary: 50000.00, email: NULL employee_id: 2, name: Bob, department: IT, salary: 60000.00, email: NULL employee_id: 3, name: Charlie, department: Finance, salary: 55000.00, email: NULL employee_id: 4, name: David, department: IT, salary: 62000.00, email: NULL employee_id: 5, name: Eve, department: HR, salary: 48000.00, email: NULL

Example: Modifying a Column

To change the data type of the email column to TEXT:

    ALTER TABLE employees
    MODIFY email TEXT;
    

Result:

Query	ALTER TABLE employees MODIFY email TEXT;
Result	employee_id: 1, name: Alice, department: HR, salary: 50000.00, email: NULL employee_id: 2, name: Bob, department: IT, salary: 60000.00, email: NULL employee_id: 3, name: Charlie, department: Finance, salary: 55000.00, email: NULL employee_id: 4, name: David, department: IT, salary: 62000.00, email: NULL employee_id: 5, name: Eve, department: HR, salary: 48000.00, email: NULL

Example: Deleting a Column

To delete the email column from the employees table:

    ALTER TABLE employees
    DROP COLUMN email;
    

Result:

Query	ALTER TABLE employees DROP COLUMN email;
Result	employee_id: 1, name: Alice, department: HR, salary: 50000.00 employee_id: 2, name: Bob, department: IT, salary: 60000.00 employee_id: 3, name: Charlie, department: Finance, salary: 55000.00 employee_id: 4, name: David, department: IT, salary: 62000.00 employee_id: 5, name: Eve, department: HR, salary: 48000.00