DROP TABLE and ALTER TABLE in SQL

Introduction

We use DROP TABLE to remove a whole table and its data. ALTER TABLE lets us change a table's structure without deleting it.

When you no longer need a table and want to delete it completely.

When you want to add a new column to store more information.

When you need to remove a column that is no longer useful.

When you want to change the type of data a column holds.

When you want to rename a table or a column for clarity.

Syntax

SQL

DROP TABLE table_name;

ALTER TABLE table_name
  ADD column_name datatype;

ALTER TABLE table_name
  DROP COLUMN column_name;

ALTER TABLE table_name
  RENAME TO new_table_name;

ALTER TABLE table_name
  RENAME COLUMN old_column_name TO new_column_name;

DROP TABLE deletes the table and all its data permanently.

ALTER TABLE changes the table structure but keeps the data.

Examples

Deletes the table named 'employees' and all its data.

SQL

DROP TABLE employees;

Adds a new column 'birthdate' to the 'employees' table.

SQL

ALTER TABLE employees ADD birthdate DATE;

Removes the 'birthdate' column from the 'employees' table.

SQL

ALTER TABLE employees DROP COLUMN birthdate;

Changes the table name from 'employees' to 'staff'.

SQL

ALTER TABLE employees RENAME TO staff;

Sample Program

This creates a 'students' table, adds an 'age' column, renames 'name' to 'full_name', then shows the table structure (empty rows).

SQL

CREATE TABLE students (
  id INT,
  name VARCHAR(50)
);

ALTER TABLE students ADD age INT;

ALTER TABLE students RENAME COLUMN name TO full_name;

SELECT * FROM students;

Important Notes

Be careful with DROP TABLE because it deletes all data permanently.

ALTER TABLE commands can vary slightly between database systems.

Always back up important data before dropping tables or altering columns.

Summary

DROP TABLE removes a table and all its data.

ALTER TABLE changes a table's structure without deleting data.

Use ALTER TABLE to add, drop, or rename columns and tables.