SQL: ALTER command

The ALTER command is a DDL command that can be used to change the structure of existing tables in the database by adding, modifying, renaming, or removing columns and constraints. Using the ALTER command, you can add columns, rename columns, delete columns, or change the data type of columns.

ALTER Command: Syntax

• Adding a Column:

ADD is used to insert new columns into an existing table. When we need to add more information, we don’t have to recreate the entire database; instead, ADD comes to our rescue.

ALTER TABLE table_name 
ADD column_name1 data_type,
    column_name2 data_type,
    ...      

Example:

Consider the student_details table( where Roll int, Name varchar(10)), which will be used to execute all the preceding commands.

Roll	Name
1	Ramesh
2	Fatima

Query to add another column “Marks” in the student_details table.

ALTER Table student_details ADD Marks INT;

Output:

Roll	Name	Marks
1	Ramesh
2	Fatima

Note: The values in column “Marks” are left blank because we added the column to the table and not inserted the values in it.

•  Modifying data type of column:

The ALTER command in SQL can be used to modify the existing columns of a table, and it is possible to modify multiple columns at once.

ALTER TABLE table_name
MODIFY column_name column_type;

Example:

Query to modify the Name column datatype from varchar(10) to varchar(25).

ALTER Table student_details MODIFY COLUMN Name varchar(25);

• Rename a Column:

We can use the ALTER command to rename an existing column in a database table.

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Example:

Query to rename the Roll column to Enrollment.

ALTER TABLE student_details RENAME COLUMN Roll TO Enrollment;

Output:

Enrollment	Name	Marks
1	Ramesh
2	Fatima

• Drop a Column:

To remove a column from an existing database table, use the ALTER TABLE command.

ALTER TABLE table_name DROP COLUMN column_name;

Example:

Query to drop column Marks.

ALTER TABLE student_details DROP COLUMN Marks;

Output:

Enrollment	Name
1	Ramesh
2	Fatima

Note: also read about SQL Data Types

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py