SQL - SPLessons

SQL Alter Table

Chapter 13

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Alter Table

SQL Alter Table

shape Description

SQL Alter Table statement is used to add, modify, or delete a columns from the existing table.

SQL Alter Table command performs adding of column or modifying column size and is divided into two types.

  • Add
  • Modify

shape Conceptual
figure

Add

shape Description

Using add command new columns can be included in the current SQL Alter Table.

shape Syntax

Alter table<table_name>

ADD(<column_name1> datatype(size),<column_name2> datatype(size));

Table_name => Any accurate table name.

Column_name =>The operation that can be performed on a column in the table.

shape Examples

By viewing the below example, the table can be altered by utilizing alter adding command.

sql> alter table student add(fee number(7,2));
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql>desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id     | int(11)      | YES  |     | NULL    |       |
| stu_name   | varchar(255) | YES  |     | NULL    |       |
| stu_result | varchar(255) | YES  |     | NULL    |       |
| branch     | varchar(255) | YES  |     | NULL    |       |
| fee        | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

sql> alter table student add(course varchar(255));
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql>desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id     | int(11)      | YES  |     | NULL    |       |
| stu_name   | varchar(255) | YES  |     | NULL    |       |
| stu_result | varchar(255) | YES  |     | NULL    |       |
| branch     | varchar(255) | YES  |     | NULL    |       |
| fee        | int(11)      | YES  |     | NULL    |       |
| course     | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

The above example tells that, how to add a new column_name like fee and course with size in  a student table.

Modify

shape Description

The structure of an existing table object can be modified using modify command.

shape Syntax

Alter table

modify( datatype(size));

Table_name => Any accurate table.

Column_name =>The operation that can be performed on a column in the table.

shape Examples

By viewing the below example, the concept of modify command can be easily understood.

sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id     | int(11)      | YES  |     | NULL    |       |
| stu_name   | varchar(255) | YES  |     | NULL    |       |
| stu_result | varchar(255) | YES  |     | NULL    |       |
| branch     | varchar(250) | YES  |     | NULL    |       |
| fee        | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

sql> alter table student modify branch varchar2(20);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id     | int(11)      | YES  |     | NULL    |       |
| stu_name   | varchar(255) | YES  |     | NULL    |       |
| stu_result | varchar(255) | YES  |     | NULL    |       |
| branch     | varchar2(20) | YES  |     | NULL    |       |
| fee        | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

sql> alter table student modify stu_name varchar2(20);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id     | int(11)      | YES  |     | NULL    |       |
| stu_name   | varchar2(20) | YES  |     | NULL    |       |
| stu_result | varchar(255) | YES  |     | NULL    |       |
| branch     | varchar2(20) | YES  |     | NULL    |       |
| fee        | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

The above example tells that, how to Modify a column_name branch and stu_name with size in student table.

Summary

shape Key Points

  • SQL Alter Table – Alter table will add or modify the structure of the table.