SQL Tutorial

SQL Introduction SQL Aggregate Functions SQL Aliases SQL And SQL Any All SQL Avg SQL Between SQL Case SQL Comments SQL Count SQL Delete SQL Distinct SQL Exists SQL GROUP BY SQL Having SQL In SQL INSERT INTO SQL Is Not Null SQL Join SQL Full Outer Join SQL Inner Join SQL Left Join SQL Right Join SQL Self Join SQL Like SQL Min Max SQL NOT Operator SQL Null SQL Operators SQL OR operator SQL ORDER BY SQL Select SQL Select Into SQL Top Limit Fetch SQL Stored Procedures SQL Sum SQL Union SQL Update SQL Where SQL Wildcards

SQL Database

SQL Alter Table SQL Auto increment SQL Backup Database SQL Check SQL Constraints SQL Create View SQL Create Database SQL Create Table SQL Data types SQL Dates SQL Default Constraint SQL Drop Database SQL Drop Table SQL Foreign Key SQL Hosting SQL Index SQL injections SQL Not NULL SQL PrimaryKey SQL Unique SQL Views

SQL Alter Table

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