Create, Alter, Rename, Truncate & Drop Table

1. CREATE TABLE

The CREATE TABLE command is used to create a new table with specified columns and data types.

CREATE TABLE student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Marks INT
);

• Defines table structure
• Column names must be unique

2. SHOW TABLES

Displays all tables in the selected database.

SHOW TABLES;

3. DESCRIBE TABLE

Shows the structure of a table.

DESC student;

4. ALTER TABLE

The ALTER TABLE command is used to modify the structure of an existing table.

4.1 Add Column

ALTER TABLE student
ADD Age INT;

4.2 Modify Column

ALTER TABLE student
MODIFY Name VARCHAR(100);

4.3 Drop Column

ALTER TABLE student
DROP Age;

5. RENAME TABLE

The RENAME command changes the table name.

RENAME TABLE student TO student_details;

6. TRUNCATE TABLE

The TRUNCATE command removes all records from a table but keeps the table structure.

TRUNCATE TABLE student_details;

• Deletes all rows
• Cannot be rolled back
• Faster than DELETE

7. DROP TABLE

The DROP TABLE command deletes the table completely.

DROP TABLE student_details;

• Deletes table and data permanently
• Structure is removed

8. Difference between TRUNCATE and DROP

TRUNCATE TABLE              DROP TABLE
-------------------------   -------------------------
Deletes all records         Deletes table completely
Structure remains           Structure removed
Faster                      Slower
Cannot be rolled back       Cannot be rolled back

Practice Questions

1. Write syntax for CREATE TABLE.
2. What is ALTER TABLE?
3. Differentiate TRUNCATE and DROP.
4. How to rename a table?
5. What is the use of DESCRIBE command?

Practice Task