An ALTER TABLE statement can be used to delete, add, or modify the pre-existing columns in any table. Thus, the ALTER TABLE statement can also be used to add or drop different constraints on a table that is already existing.
In this article, we will dive deeper into the ALTER Statement in SQL according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.
Table of Contents
What is an ALTER Statement in SQL?
The ALTER TABLE can be used in order to add, drop, delete, or modify the columns in an already existing table. This statement can also be used to add or drop multiple constraints on an already existing table.
ADD – ALTER TABLE
The ADD can be used in order to add columns to an already existing table. We may need to add additional information or data sometimes, and in such a case, the creation of the whole database again is not at all required. It is exactly where ADD comes to our rescue.
Syntax
The syntax of ADD is:
ALTER TABLE name_of_table
ADD (name_of_column_1 datatype,
name_of_column_2 datatype,
…
name_of_column_n datatype);
DROP – ALTER TABLE
The DROP COLUMN can be used to drop a column in any given table or in order to delete the unwanted columns present in a table.
Syntax
The syntax of DROP is:
ALTER TABLE name_of_table
DROP COLUMN name_of_column;
ALTER TABLE-MODIFY
The MODIFY can be basically used in order to modify the pre-existing columns in any given table. One can modify multiple columns at once using this.
Syntax
The syntax of MODIFY goes like this (Note that the syntax may slightly vary in various databases:
Syntax for MySQL, Oracle, MariaDB
ALTER TABLE name_of_table
MODIFY name_of_column column_type;
Syntax of SQL Server
ALTER TABLE name_of_table
ALTER COLUMN name_of_column column_type;
Sample Table:
Employee
| ID_NO | NAME |
| 1 | Ramesh |
| 2 | Abhilash |
| 3 | Rahulya |
| 4 | Tanuja |
QUERY:
In order to ADD 2 columns, LANGUAGE and AGE, to the table “Employee”, the query would be:
ALTER TABLE Employee ADD (AGE number(3),LANGUAGE varchar(40));
OUTPUT:
| ID_NO | NAME | AGE | LANGUAGE |
| 1 | Ramesh | ||
| 2 | Abhilash | ||
| 3 | Rahulya | ||
| 4 | Tanuja |
In order to MODIFY the column LANGUAGE in the table Employee, we do this:
ALTER TABLE Employee MODIFY LANGUAGE varchar(20);
Once the above queries are run, the maximum size of the Language column would get reduced from 40 to 20.
Using the DROP column LANGUAGE in the table Employee,
ALTER TABLE Employee DROP COLUMN LANGUAGE;
OUTPUT:
| ID_NO | NAME | AGE |
| 1 | Ramesh | |
| 2 | Abhilash | |
| 3 | Rahulya | |
| 4 | Tanuja |
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments