SQL Table Management (CREATE, ALTER, DROP) Explained with Examples + Practice Questions

When to Use Table Management

• Use CREATE to create new tables
• Use ALTER to modify existing tables
• Use DROP to delete tables

These commands are part of DDL (Data Definition Language) and are used in:

• Database design
• Backend development
• Schema changes

Example: Table Structure

Table: employees

CREATE TABLE

Used to create a new table.

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

With Constraints

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary INT DEFAULT 0
);

Key Points:

• Define column names and data types
• Use constraints like PRIMARY KEY, NOT NULL, DEFAULT
• Plan schema carefully

ALTER TABLE

Used to modify an existing table.

Add Column

sql
ALTER TABLE employees
ADD email VARCHAR(100);

Modify Column

sql
ALTER TABLE employees
MODIFY salary BIGINT;

Note: syntax may vary by database.

Rename Column

sql
ALTER TABLE employees
RENAME COLUMN name TO full_name;

Drop Column

sql
ALTER TABLE employees
DROP COLUMN email;

Key Points:

• Used for schema updates
• Syntax varies slightly across databases
• Always test before applying changes

DROP TABLE

Used to delete a table completely.

sql
DROP TABLE employees;

Important Warning:

• Deletes table structure and data permanently
• Cannot be undone

CREATE vs ALTER vs DROP

Key Concepts

• These are DDL commands
• Affect structure, not just data
• Often restricted in production environments
• Critical for database design

Practice Note

For safety, these exercises use schema inspection queries. When DDL is enabled later, you can swap to real CREATE/ALTER/DROP statements.