You are currently viewing SQL 1.45 SQL ALTER TABLE

SQL 1.45 SQL ALTER TABLE

Sure, let’s dive into the basics of using SQL’s ALTER TABLE command, which is essential for modifying existing database tables. Here’s a detailed tutorial with sample codes and explanations:

1. Understanding ALTER TABLE:

The ALTER TABLE statement in SQL is used to modify an existing table. This modification can involve adding, modifying, or dropping columns, constraints, and indexes.

2. Syntax of ALTER TABLE:

The basic syntax of the ALTER TABLE statement is as follows:

ALTER TABLE table_name
    action;

Where:

  • table_name: The name of the table you want to alter.
  • action: The action you want to perform, such as adding, modifying, or dropping columns, constraints, or indexes.

3. Adding a New Column:

To add a new column to an existing table, you can use the following syntax:

ALTER TABLE table_name
    ADD column_name datatype [constraints];

Let’s say we have a table called employees and we want to add a new column called email:

ALTER TABLE employees
    ADD email VARCHAR(255);

4. Modifying a Column:

To modify an existing column in a table, you can use the following syntax:

ALTER TABLE table_name
    MODIFY column_name new_datatype [constraints];

For example, if we want to change the datatype of the email column to VARCHAR(100):

ALTER TABLE employees
    MODIFY email VARCHAR(100);

5. Dropping a Column:

To drop a column from a table, you can use the following syntax:

ALTER TABLE table_name
    DROP COLUMN column_name;

For example, if we want to drop the email column from the employees table:

ALTER TABLE employees
    DROP COLUMN email;

6. Sample Scenario:

Let’s say we have a table called students with the following structure:

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

Now, we want to add a new column called grade to store the grade of each student. We can do this using the ALTER TABLE statement:

ALTER TABLE students
    ADD grade VARCHAR(2);

This will add a new column grade to the students table.

7. Summary:

  • ALTER TABLE is used to modify existing database tables.
  • It allows you to add, modify, or drop columns, constraints, and indexes.
  • Syntax: ALTER TABLE table_name action.
  • Actions include adding, modifying, or dropping columns.
  • Sample scenarios demonstrated the addition of a new column to an existing table.

By following this tutorial and practicing with various scenarios, you should now have a good understanding of how to use SQL’s ALTER TABLE command. Feel free to experiment further to deepen your understanding!

Leave a Reply