DDL (Data Definition Language)

SQL Data Definition Language (DDL) is used to define, modify, and manage database objects such as tables, indexes, and constraints. It includes commands like CREATE, ALTER, and DROP. DDL statements define the structure and properties of database objects, including their data types, constraints, and relationships. DDL commands are used to create new objects, modify existing ones, or remove them from the database schema. DDL statements are essential for database schema design and management, enabling users to define the organization and characteristics of data stored in a database.

CREATE

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, hire_date DATE );

In this example:

• CREATE TABLE is the DDL command used to create a new table named employees.
• employee_id, first_name, last_name, department_id, and hire_date are columns within the table.
• INT specifies the integer data type for employee_id and department_id.
• VARCHAR(50) specifies variable-length character strings for first_name and last_name with a maximum length of 50 characters.
• DATE specifies the date data type for hire_date.
• PRIMARY KEY constraint is applied to employee_id, making it unique for each row and ensuring it acts as the primary identifier for each employee.

 

ALTER

-- Adding a new column

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

 -- Modifying column data type

ALTER TABLE employees ALTER COLUMN department_id INT;

-- Adding a primary key constraint

ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

 -- Adding a foreign key constraint

ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Dropping a column

ALTER TABLE employees DROP COLUMN hire_date;

In this example:

• ADD COLUMN is used to add a new column named email to the employees table.
• ALTER COLUMN is used to modify the data type of the department_id column.
• ADD CONSTRAINT is used to add primary key and foreign key constraints to the table.
• DROP COLUMN is used to remove the hire_date column from the employees table.

 

DROP

DROP TABLE employees;

In this example:

• DROP TABLE is the DDL command used to delete the table named employees.
• Once executed, all data stored within the employees table will be permanently deleted, and the table structure will be removed from the database.