DDL (Data Definition Language) in SQL

DDL (Data Definition Language)

• DDL is a subset of SQL used for defining and managing the structure of a database
• DDL statements define, modify, or delete database objects such as tables, indexes, and users
• Common DDL commands include CREATE, ALTER, DROP, RENAME, and TRUNCATE
• CREATE is used to create database objects like tables, indexes, views, stored procedures, triggers, and users
• CREATE TABLE defines a new table, specifying column names, data types, and constraints
• Example: CREATE TABLE Employees (ID INT, Name VARCHAR(255), Salary DECIMAL(10, 2))
• CREATE INDEX creates an index on one or more columns of a table to improve query performance
• Example: CREATE INDEX NameIndex ON Employees (Name)
• ALTER modifies the structure of existing database objects
• ALTER TABLE is used to add, delete, or modify columns and constraints in a table
• Example: ALTER TABLE Employees ADD COLUMN Department VARCHAR(255)
• DROP removes database objects
• DROP TABLE deletes a table and its data
• Example: DROP TABLE Employees
• DROP INDEX removes an index
• Example: DROP INDEX NameIndex
• RENAME changes the name of a database object
• Example: RENAME TABLE Employees TO Staff
• TRUNCATE removes all rows from a table, but keeps the table structure intact
• TRUNCATE TABLE is faster than DELETE because it deallocates data pages
• Example: TRUNCATE TABLE Employees
• DDL commands are automatically committed, meaning changes are immediately permanent
• DDL primarily deals with the schema (structure) of the database

DML (Data Manipulation Language)

• DML is a subset of SQL used for accessing and manipulating data within a database
• DML statements retrieve, insert, update, or delete data in database tables
• Common DML commands include SELECT, INSERT, UPDATE, and DELETE
• SELECT retrieves data from one or more tables
• SELECT allows filtering using WHERE clauses, sorting using ORDER BY, and grouping using GROUP BY.
• Example: SELECT * FROM Employees WHERE Department = 'IT'
• INSERT adds new rows into a table
• INSERT specifies the table name and the values to be inserted into each column
• Example: INSERT INTO Employees (ID, Name, Salary) VALUES (1, 'John Doe', 50000)
• UPDATE modifies existing data in a table
• UPDATE includes a SET clause to specify which columns to update and a WHERE clause to identify the rows to be updated
• Example: UPDATE Employees SET Salary = 55000 WHERE ID = 1
• DELETE removes rows from a table
• DELETE requires a WHERE clause to specify which rows to delete; omitting the WHERE clause deletes all rows
• Example: DELETE FROM Employees WHERE Department = 'HR'
• DML commands can be part of a transaction, allowing multiple operations to be treated as a single unit of work
• Transactions can be committed (made permanent) or rolled back (undone)
• DML primarily deals with the data within the database schema

DDL vs DML

• DDL is for defining the database schema; DML is for manipulating the data within that schema
• DDL commands are auto-committed; DML commands can be part of a transaction
• DDL operations affect the structure of the database; DML operations affect the data stored in the database

Triggers

• Triggers are special stored procedures that automatically execute in response to certain events on a table
• Triggers are typically used for auditing, enforcing business rules, and maintaining data integrity
• Triggers are associated with a specific table and are activated by DML events (INSERT, UPDATE, DELETE)
• Triggers can be executed before or after the triggering event (BEFORE or AFTER)
• Triggers can be defined to execute once for each row affected by the triggering event (FOR EACH ROW) or once for the entire statement (FOR EACH STATEMENT)
• Syntax for creating a trigger involves specifying the trigger name, the triggering event, the table, and the trigger action
• Example: CREATE TRIGGER AuditEmployeeChanges AFTER UPDATE ON Employees FOR EACH ROW BEGIN -- Trigger logic END;
• BEFORE triggers can modify the data being inserted or updated before it is written to the table
• AFTER triggers cannot modify the data but can perform actions based on the data that was changed
• FOR EACH ROW triggers execute once for each row affected by the triggering event, allowing row-level operations
• FOR EACH STATEMENT triggers execute only once for the entire triggering statement, regardless of the number of rows affected
• Triggers can access the old and new values of the row being modified using OLD and NEW keywords
• OLD refers to the values of the row before the triggering event (available for UPDATE and DELETE)
• NEW refers to the values of the row after the triggering event (available for INSERT and UPDATE)
• Triggers can call other stored procedures or execute SQL statements to perform complex actions
• Triggers can be disabled or enabled using the ALTER TABLE statement
• Example: ALTER TABLE Employees DISABLE TRIGGER AuditEmployeeChanges;
• Common uses of triggers include:
  • Auditing data changes by logging them to an audit table
  • Enforcing business rules such as preventing updates that violate certain conditions
  • Maintaining data integrity by automatically updating related tables
  • Implementing complex security measures
• Triggers should be used judiciously as they can impact database performance and complicate debugging
• Overuse of triggers can lead to cascading effects and unintended consequences
• Proper design and testing are essential when implementing triggers to ensure they function correctly and efficiently