Day 3: SQL Command Types – DDL, DML, DQL, DCL, TCL : Mastering

SQL Command Types

SQL commands are categorized into five key types based on their functionality:

• DDL (Data Definition Language): Manage database structures.
• DML (Data Manipulation Language): Modify and interact with data.
• DQL (Data Query Language): Retrieve data from the database.
• DCL (Data Control Language): Control access to the database.
• TCL (Transaction Control Language): Manage database transaction.

i.) Data Definition Language (DDL)
Used to define, alter, and remove database structures like tables, schemas, and indexes.

Key Commands:

• CREATE: Create new databases, tables, or other objects.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    grade INT
);

• ALTER: Modify existing database objects.

ALTER TABLE students ADD COLUMN email VARCHAR(100);

• DROP: Remove database objects permanently.

DROP TABLE students;

• TRUNCATE: Remove all data from a table but keep its structure.

TRUNCATE TABLE students;

ii.) Data Manipulation Language (DML)
Focuses on inserting, updating, and deleting data in tables.

Key Commands:

• INSERT: Add new data.

INSERT INTO employees (emp_id, name, department) VALUES (1, 'Alice', 'HR');

• UPDATE: Modify existing data.

UPDATE employees SET department = 'Finance' WHERE emp_id = 1;

• DELETE: Remove specific records.

DELETE FROM employees WHERE emp_id = 1;

iii.) Data Query Language (DQL)
Focuses on retrieving data using the SELECT statement.
Key Command:

• SELECT: Fetch data from the database.

SELECT * FROM employees WHERE department = 'HR';

iv.) Data Control Language (DCL)
Used to control access and permissions for the database.
Key Commands:

• GRANT: Give privileges to users.

GRANT SELECT, INSERT ON employees TO user1;

• REVOKE: Remove privileges from users.

REVOKE INSERT ON employees FROM user1;

v.) Transaction Control Language (TCL)
Used to manage database transactions, ensuring data consistency.
Key Commands:

• COMMIT: Save changes in a transaction.

COMMIT;

• ROLLBACK: Undo changes made in a transaction.

ROLLBACK;

• SAVEPOINT: Set a save point to rollback to.

SAVEPOINT: Set a save point to rollback to.

• SET TRANSACTION: Define transaction properties.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Challenge for you: High-Earning Employees with Department Insights
📌 Task:

Write a single SQL query to:

Retrieve employee names, their department names, and their salaries.
Calculate the department-wise average salary.
Only show employees earning more than the average salary of their department.
Sort the results in descending order of salary.
💡 Hint: Use JOIN, GROUP BY, HAVING, ORDER BY, and a subquery.