1. sql
  2. /references
  3. /table-statements

Table Manipulation Statements in SQL


There are several statements in SQL that can be used to manipulate tables. Here are a few common ones:

  • CREATE TABLE - creates a new table in the database with the specified columns and data types.
CREATE TABLE table_name (
    column1 data_type constraint, 
    column2 data_type constraint, 
    column3 data_type constraint
  • ALTER TABLE - alters the structure of an existing table. This statement can be used to add, modify, or delete columns in a table, or to change the data type of a column.
ALTER TABLE table_name 
ADD COLUMN column_name data_type constraint;
ALTER TABLE table_name 
MODIFY COLUMN column_name new_data_type constraint;
ALTER TABLE table_name 
DROP COLUMN column_name;
  • TRUNCATE TABLE - removes all rows from a table. Unlike the DELETE statement, TRUNCATE TABLE does not generate any rollback information, so it cannot be undone.
TRUNCATE TABLE table_name;
  • RENAME TABLE - renames an existing table to a new name.
RENAME TABLE old_table_name 
TO new_table_name;
  • DROP TABLE - deletes a table from the database. This statement is used to remove a table and all its data permanently. Be careful when using this statement, as the data in the table will be lost permanently.
DROP TABLE table_name;


There are several table manipulation statements in SQL that you can use to create, modify, and delete tables and their data. Here are a few examples:

  • CREATE TABLE - For example, the following SQL creates a table named employees with four columns: id, first_name, last_name, and hire_date.
CREATE TABLE employees (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    hire_date DATE NOT NULL
  • ALTER TABLE - For example, the following SQL query adds a new column named salary to the employees table.
ALTER TABLE employees 
ADD COLUMN salary DECIMAL(10,2);

Please note that, statements are specific to the database management system and may have slight variations in syntax and functionality.

Best Practices

There are a number of best practices that you should keep in mind when working with table manipulation statements in SQL. Here are a few:

  • Always use the appropriate statement for the task at hand. For example, use the CREATE TABLE statement to create a new table, the ALTER TABLE statement to modify an existing table, and the DROP TABLE statement to delete a table.

  • Be mindful of the order of operations when working with multiple tables. For example, if you want to create a new table that references data in an existing table, you'll need to create the existing table first, and then create the new table that references it.

  • Use constraints to enforce data integrity. For example, you can use the FOREIGN KEY constraint to ensure that data in one table references data in another table correctly.

  • Use indexes to improve query performance. Indexes can help SQL quickly find and retrieve the data it needs, making queries faster and more efficient.

  • Be careful when using the DROP TABLE statement. Once a table is dropped, all data stored in it is permanently deleted, and there is no way to recover that data.

  • Before making any changes to a table structure, backup the data, or make sure you can revert the change if something goes wrong.