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.
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.
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);
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 |
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
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
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.
Given an integer A. Compute and return the square root of A. If A is…
Given a zero-based permutation nums (0-indexed), build an array ans of the same length where…
A heap is a specialized tree-based data structure that satisfies the heap property. It is…
What is the Lowest Common Ancestor? In a tree, the lowest common ancestor (LCA) of…
A Binary Search Tree (BST) is a type of binary tree that satisfies the following…