SQL: Data Definition Language (DDL statements)

Data Definition Language (DDL) defines and reshapes the structure of a database, its schemas, tables, columns, constraints, indexes, views, sequences, and partitions. Unlike DML (which manipulates row data), DDL changes the blueprint those rows live in. Because DDL alters metadata, it can take stronger locks, rebuild indexes, or invalidate cached plans.

Common DDL Commands: 

CREATE TABLE

This command is used to create a new table in the database defining the table's structure, including columns and their data types.  

This statement will create a new table ‘workers’ with ‘ID’ as the primary key column and ‘Name’ and ‘Position’ as other columns.  

Practice: Create a table persons with columns person_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL.

DROP TABLE

This command deletes an existing table and all of its data from the database.  

This statement will drop table ‘employees’ from the Database.  

Practice: Drop the table departments.

TRUNCATE TABLE:

This command keeps the structure of the table but deletes the all the existing data of the table.  

TRUNCATE TABLE employees; 

ALTER TABLE:

This command is used to modify an existing table structure, such as adding, deleting, or modifying columns. It can also be used to modify the name of the database.  

1. Add a new column:   

This statement will add a new column ‘salary’ to the employees table. 

Practice: Add a salary INTEGER column to employees. 

2. Rename a column:   

This statement will rename the datatype of column ‘Name’ to ‘Fullname’. 

Practice: Rename the salary column to pay column.  

3. Rename a Table:  

This statement will rename an existing table ‘employees’ to 'staff'.  

Practice: Rename the departments table to divisions. 

FAQs 

1. When should I use DROP, TRUNCATE, or DELETE? 
Use DROP to remove the table itself, TRUNCATE to quickly empty all rows but keep the table, and DELETE to remove specific rows while keeping the rest.

2. Can ALTER TABLE rename columns or tables?
Yes. ALTER TABLE can add, remove, or modify columns, and it can rename a column or the entire table. Exact syntax varies by database.

3. Does TRUNCATE remove data permanently and reset identity values?
TRUNCATE clears all rows and often resets identity/auto-increment counters. The exact behavior can differ by database.

4. What’s the difference between DDL and DML?
DDL changes the structure of the database (tables, columns, indexes, views). DML changes the data inside those structures (insert, update, delete, select). 

5. Will DDL changes delete my data?

Usually no, most DDL changes only alter structure. But DROP removes the table and all its data, and TRUNCATE empties it. Always double-check before running DDL.