Data Definition Language (DDL)

DDL encompasses commands used to define and modify database schema structures. These operations affect the organization and properties of database objects rather than the data itself. Key commands include:

• CREATE: Establishes new database objects
• ALTER: Modifies existing object structures
• DROP: Removes database objects permanently

Example syntax for table creation:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

Structure modification examples:

-- Add new column
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Rename column
ALTER TABLE users CHANGE email user_email VARCHAR(120);

-- Remove column
ALTER TABLE users DROP COLUMN last_login;

-- Remove entire table
DROP TABLE users;

Data Manipulation Language (DML)

DML focuses on managing data within existing database structures. Unlike DDL which modifies schema definitions, DML operates exclusively on stored data values. Primary operations include:

• INSERT: Adds new records to tables
• UPDATE: Modifies existing record values
• DELETE: Removes records from tables

Data manipulation examples:

-- Insert multiple records
INSERT INTO products (name, price, category) 
VALUES ('Laptop', 999.99, 'Electronics'),
       ('Mouse', 25.50, 'Accessories');

-- Update specific records
UPDATE inventory 
SET quantity = quantity - 10 
WHERE product_id = 123;

-- Delete matching records
DELETE FROM orders 
WHERE order_date < '2023-01-01';

Data Query Language (DQL)

DQL consists of commands designed for retrieving information from databases. The primary operation is data selection based on specified criteria.

Core query structure:

SELECT column_list 
FROM table_name 
WHERE conditions;

Query example:

SELECT employee_name, department, salary 
FROM staff 
WHERE salary > 50000 
ORDER BY salary DESC;

Data Control Language (DCL)

DCL manages access permissions and security settings for database users. These commands control who can perform specific actions within the database system.

Permission management commands:

• GRANT: Assigns specific privileges to users
• REVOKE: Removes previously assigned privileges

Access control examples:

-- Grant read access
GRANT SELECT ON customers TO analyst_role;

-- Grant full table access
GRANT ALL PRIVILEGES ON sales_data TO manager_user;

-- Remove update permission
REVOKE UPDATE ON inventory FROM warehouse_team;