Tutorial 4 - 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. 

You can also insert values in the workers table and see them with following commands:

INSERT INTO workers (ID, Name, Position) VALUES (1, 'Alice Smith', 'Engineer'), (2, 'Bob Johnson', 'HR Manager'), (3, 'Charlie Brown', 'Accountant');

SELECT * FROM workers; 

Practice: Create a table persons with columns person_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, insert some rows in it and see the results.

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. 

You can see the altered table through:

SELECT * FROM employees;

Practice: Add a bonus INTEGER column to employees and see the results. 

2. Rename a column:   

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

You can see the altered table through:

SELECT * FROM employees;

Practice: Rename the salary column to pay column and see the results.

3. Rename a Table:  

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

You can see the altered table through:

SELECT * FROM staff;

Practice: Rename the departments table to divisions and see the results. 

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.