SQL Commands (DDL, DML, DQL, DCL, TCL) with Examples

SQL(Structured Query Language) is the standard language for managing and manipulating relational databases. SQL commands are the instructions used to communicate with a Relational Database Management System (RDBMS) to create database structures, manipulate data, and control access.

Whether you need to build a new table, update customer records, or retrieve a specific dataset, you will use specific subsets of SQL. This comprehensive guide covers the five main categories of SQL commands, such as DDL, DML, DQL, DCL, and TCL, complete with definitions, syntax, and practical code examples.

SQL Commands Cheat Sheet

A quick reference for the most common commands.

Data Definition Language (DDL)

Data Definition Language (DDL) commands define and manage the structure (schema) of your database. They allow you to create, modify, or delete database objects such as tables, indexes, and views.

Note: In most relational databases (like Oracle and MySQL), DDL commands are auto-committed. This means once you execute a DROP or TRUNCATE command, the change is permanent and cannot be undone using a ROLLBACK command.

1. CREATE:

Creates a new database or table.

Example:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Department VARCHAR(50)
);

2. ALTER:

Modifies the structure of an existing table (e.g., adding, deleting, or modifying columns).

Example:

ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);

3. DROP:

Deletes a table and all its data permanently.

Example:

DROP TABLE Employees;

4. TRUNCATE:

Deletes all data inside a table but keeps the structure.

Example:

TRUNCATE TABLE Employees;

5. RENAME:

Changes the name of a database object (like a table or column).

Example:

RENAME TABLE Employees TO Staff;

6. COMMENT:

Adds or modifies a comment on a database object (like a table or column).

Example:

COMMENT ON COLUMN Employees.Name IS 'Employee Name';

Essential SQL Constraints

When using the CREATE TABLE DDL command, you will often apply constraints to specify rules for the data in a table. Common constraints include:

• PRIMARY KEY: Uniquely identifies each record in a table (e.g., Employee ID). Cannot contain NULL values.
• FOREIGN KEY: A field in one table that refers to the PRIMARY KEY in another table, linking them together.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.

Data Manipulation Language (DML)

DML commands manage the data within your database. They allow you to add, edit, or remove records in the tables, working with the data inside the structure created by DDL.

7. INSERT:

Adds new records to a table.

Example:

INSERT INTO Employees (ID, Name, Age, Department) 
VALUES (1, 'John Doe', 30, 'HR');

8. UPDATE:

Modifies existing records in a table.

Example:

UPDATE Employees
SET Age = 31
WHERE ID = 1;

9. DELETE:

Removes records from a table.

Example:

DELETE FROM Employees
WHERE ID = 1;

DELETE vs. TRUNCATE vs. DROP: What’s the Difference?

One of the most common SQL interview questions is explaining the difference between these three deletion commands. Here is a quick comparison to help you choose the right one:

Data Control Language (DCL)

DCL commands manage security, access, and permissions. They control who can view or edit data, ensuring only authorized users can modify sensitive information.

10. GRANT:

This command gives permissions to users or roles. You can grant permissions such as SELECT (view), INSERT (add data), UPDATE (modify data), and DELETE (remove data).

Example:

GRANT SELECT, INSERT ON Employees TO user123;

11. REVOKE:

This command removes permissions from users or roles. It takes away rights that were previously granted. You can revoke permissions like SELECT, INSERT, UPDATE, or DELETE.

Example:

REVOKE SELECT, INSERT ON Employees FROM user123;

12. DENY:

While GRANT and REVOKE are universal standard SQL commands, DENY is specifically used in Microsoft SQL Server (T-SQL). It explicitly prevents a user or role from gaining a permission, overriding any permissions they might have inherited from other group memberships.

DENY DELETE ON Employees TO user123;

Data Query Language (DQL)

DQL commands are used to retrieve data from the database. It focuses only on reading information, not changing it.

The primary DQL command is SELECT. It can be combined with clauses like WHERE, JOIN, and HAVING to refine results and analyze data in detail.

13. SELECT:

The SELECT command retrieves data from a database using clauses such as WHERE, JOIN, and HAVING to refine and filter the results.

Example:

SELECT Name, Age FROM Employees WHERE Department = 'Sales';

Essential DQL Clauses and Aggregate Functions

1. The WHERE Clause

Used to filter records that fulfill a specified condition. Retrieve employees who work in Sales

SELECT Name, Age
FROM Employees
WHERE Department = 'Sales';

2. The ORDER BY Clause

Sorts the result set in ascending (ASC) or descending (DESC) order.Sort employees by highest salary.

SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC;

3. GROUP BY and HAVING Clauses

GROUP BY groups rows that have the same values into summary rows. HAVING is used instead of WHERE with aggregate functions. Find departments with an average salary greater than 55000.

SELECT Department, AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 55000;

4. SQL Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. The most common are:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Returns the average value of a numeric column.
• MAX() / MIN(): Returns the highest/lowest value.

Count the total number of employees in the company

SELECT COUNT(*) FROM Employees;

Find the highest salary in the IT department

SELECT MAX(Salary) FROM Employees WHERE Department = 'IT';

Other Queries

SELECT + WHERE + ORDER BY
SELECT Name, Salary FROM Employees WHERE Salary > 50000 ORDER BY Salary DESC;

SELECT + JOIN
SELECT Employees.Name, Departments. Name AS DeptName FROM Employees JOIN Departments ON Employees.DeptID = Departments.ID;

SELECT + GROUP BY + HAVING
SELECT Department, COUNT() AS TotalEmployees FROM Employees GROUP BY Department HAVING COUNT() > 10;

SELECT + DISTINCT
SELECT DISTINCT Department FROM Employees;

SELECT + ORDER BY + LIMIT
SELECT Name, Age FROM Employees ORDER BY Age DESC LIMIT 5;

Transaction Control Language (TCL)

TCL is a set of SQL commands used to manage transactions. A transaction is a group of SQL operations that should be treated as a single unit. TCL ensures that either all actions in a transaction are saved, or none are saved if something goes wrong.

The TCL commands are:

14. COMMIT

The COMMIT command permanently saves all changes made during the current transaction to the database. Once committed, the changes are visible to other users and cannot be rolled back.

-- Note: Syntax for starting a transaction varies by RDBMS. 
-- SQL Server uses BEGIN TRANSACTION, PostgreSQL uses BEGIN, and MySQL uses START TRANSACTION.
BEGIN TRANSACTION;
UPDATE employees SET salary = 5000 WHERE id = 1;
COMMIT;

In this example, the salary update is saved to the database.

15. ROLLBACK

The ROLLBACK command undoes all mistaken changes made during the current transaction.

Example:

BEGIN TRANSACTION;
UPDATE employees SET salary = 5000 WHERE id = 1;
ROLLBACK;

Here, the salary update is undone, and the data remains unchanged.

16. SAVEPOINT

The SAVEPOINT command sets a point within a transaction that you can roll back to if needed.

Example:

BEGIN TRANSACTION;
UPDATE employees SET salary = 5000 WHERE id = 1;
SAVEPOINT salary_update;
UPDATE employees SET salary = 6000 WHERE id = 2;
ROLLBACK TO salary_update;
COMMIT;

In this example, if the second update causes an issue, you can roll back to the salary_update point, keeping the first update intact.

17. SET TRANSACTION

The SET TRANSACTION command is used to set properties for the current transaction, such as isolation level.

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE employees SET salary = 5000 WHERE id = 1;
COMMIT;

This sets the isolation level to SERIALIZABLE, ensuring that no other transactions can access the data until the current transaction completes.

FAQs

Other SQL Resources:

• SQL Quiz
• SQL Exercise
• SQL Operators with Examples
• SQL Constraints with Examples
• SQL Projects Ideas for Beginners & Beyond
• SQL Interview Questions
• SQL Queries to Practice Data Analysis