Some Additional DDL Commands

Some Additional DDL Commands:

(i) ALTER COMMAND

The ALTER command is used to alter the table structure like adding a column, renaming the existing column, change the data type of any column or size of the column or delete the column from the table. It is used in the following way :

ALTER TABLE <table-name> ADD <column-name><data type><size>;

To add a new column “Address” of type ‘char’ to the Student table, the command is used as ALTER TABLE Student ADD Address char;

To modify existing column of table, the ALTER TABLE command can be used with MODIFY clause like wise:

ALTER <table-name> MODIFY<column-name><datatype><size>;

ALTER TABLE Student MODIFY Address char (25);

The above command will modify the address column of the Student table to now hold 25 characters.

The ALTER command can be used to rename an existing column in the following way :

ALTER <table-name> RENAME old-column-name TO new-column-name;

For example to rename the column Address to City, the command is used as :

ALTER TABLE Student RENAME Address TO City;

The ALTER command can also be used to remove a column or all columns, for example to remove a particular column, the DROP COLUMN is used with the ALTER TABLE to remove a particular field, the command can be used as:

ALTER <table-name> DROP COLUMN <column-name>;

To remove the column City from the Student table, the command is used as :

ALTER TABLE Student DROP COLUMN City;

(ii) TRUNCATE command

The TRUNCATE command is used to delete all the rows from the table, the structure remains and the space is freed from the table. The syntax for TRUNCATE command is:

TRUNCATE TABLE table-name;

For example to delete all the records of the student table and delete the table the SQL statement is given as follows:

TRUNCATE TABLE Student;

The table Student is removed and the space is freed.

(iii) DROP TABLE command

The DROP TABLE command is used to remove a table from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP TABLE command. But there is a condition for dropping a table; it must be an empty table.

Remove all the rows of the table using DELETE command. The DELETE command is already explained.

To delete all rows, the command is given as :

DELETE * FROM Student;

Once all the rows are deleted, the table can be deleted by DROP TABLE command in the following way:

DROP TABLE table-name;

For example to delete the Student table:

DROP TABLE Student;

DELETE, TRUNCATE AND DROP statement:

DELETE : The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE : The TRUNCATE command is used to delete all the rows, the structure remains in the table and free the space containing the table.

DROP : The DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.