Page 98 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

ID Name Age
1 Christian 22
2 John Mark 22
3 Jayson 23
4 Nigel 22

ALTER EXAMPLE (ADD Column)
ALTER TABLE Persons ADD Email VARCHAR(255);
ID Name Age Email
1 Christian 22 Null
2 John Mark 22 Null
3 Jayson 23 Null
4 Nigel 22 Null

ALTER EXAMPLE (ADD Multiple Column)
ALTER TABLE Persons ADD (Email VARCHAR(255), ContactNo VARCHAR(255));
Now the “Persons” table would become as follow:
ID Name Age Email ContactNo
1 Christian 22 Null Null
2 John Mark 22 Null Null
3 Jayson 23 Null Null
4 Nigel 22 Null Null

ALTER EXAMPLE (DROP/DELETE Column)
ALTER TABLE Persons DROP COLUMN Email;
Now the “Persons” table would become as follow:
ID Name Age ContactNo
1 Christian 22 Null
2 John Mark 22 Null
3 Jayson 23 Null
4 Nigel 22 Null

ALTER EXAMPLE (MODIFY Column)
The following SQL Statement modifies the data type of ‘Name’ column from varchar to char in
‘Persons’ table.
ALTER TABLE Persons MODIFY COLUMN Name CHAR(50);

The following SQL Statement modifies the size of ‘ContactNo’ column from 255 to 25 in
‘Persons’ table.
ALTER TABLE Persons MODIFY COLUMN ContactNo VARCHAR(25);

Rules of ALTER Statement
SQL ALTER TABLE (ADD Column)
- When we add a column using ALTER TABLE statement then it automatically adds those
columns to the end of the table and updates values for each record in that column with
NULL.

SQL ALTER TABLE (DROP/DELETE Column)
- When we delete a column from a table, column and all the data it contains are deleted.
- You cannot delete a column that has a CHECK constraint. You must first delete the
constraint.

Page | 14