Alter and Truncate Command in SQL

SQL provides us with the flexibility to modify and delete a table after its creation. We have data that needs to be put in the table and, at the same time, data that is of no use after a particular time.

To keep our data updated, we need to take various measures, and table altering is one of those. Alter and Drop commands are applicable for the same.

Demo Database

Let us view the database that we will use here, using the SELECT query.

Syntax:

SELECT * FROM tableName ;

Query:

Select * from IBM_employee ;

We have 5 columns, namely:
  1. name_emp
  2. post_emp
  3. email
  4. age

  5. salary
What is an Alter Command in SQL?

Alter command is applicable to add, modify, or update columns in an existing table. It is also applicable to drop or delete a table. The basic syntax of the alter command is as follows:

Syntax:

ALTER TABLE tableName

operationcolumnNamedatatype;

We can perform the following tasks using the Alter Command:
  1. ADD Column in SQL

    Used to add a new column to the existing table.

    Syntax:

    ALTER TABLE tableName

    ADD columnNamedatatype ;

  2. MODIFY Column in SQL

    Used to modify the already existing columns in a database. We can modify one or multiple columns at once.

    Syntax:

    ALTER TABLE tableName

    MODIFY columnNamecolumnType;

  3. RENAME Column in SQL

    Rename is put with Alter to change the name of a column. This could be applicable due to various reasons, for example:

    • To make the column name meaningful.
    • Simplification of names for analysis.
  4. Let us rename our table and also the columns of our table.

    Renaming the Table in SQL
    Syntax:

    ALTER TABLE tableName

    RENAME TO newTableName

    Renaming The Column in SQL
    Syntax:

    ALTER TABLE table_name

    CHANGE COLUMN old_name TO new_name;

  5. DROP Column in SQL

    We use drop to remove the columns which are no longer required in the database. We should be very careful while we drop a column as data we delete once can’t be retrieved.

    Syntax:

    ALTER TABLE tableName

    DROP COLUMN columnName ;

  6. What is the Truncate Command in SQL?

    We use the Truncate command to delete the data stored in the table. The working of truncate command is similar to the working of Delete command without a where clause.

    Syntax:

    TRUNCATE TABLE tableName ;

    Difference between Truncate and Drop command in SQL?

    Executing the truncate command deletes all the data stored in the table or database. Whereas the Drop command is applicable to destroy the whole object it could be a table, a database, or a schema.

    Also, syntax differences exist. Syntax of both are as follows:

    SQL Truncate Syntax:

    TRUNCATE TABLE tableName ;

    Syntax of Drop:

    DROP TABLE tableName ;

    Some of the Restrictions on Alter table are as follows:
    • By using an alter table, we can’t change the name of the table.
    • We can’t modify the names of the columns.
    • We can’t alter a column already containing data.


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.