SQL Basics DDL, DML, DCL, TCL Commands: Tutorial, Examples, FAQs & Interview Tips

SQL Basics in DBMS

SQL stands for Structured Query Language. It is the standard language used to create, modify, query, secure, and manage data in relational database systems. SQL is used with DBMS products such as MySQL, PostgreSQL, Oracle, SQL Server, SQLite, and MariaDB.

SQL allows users to work with databases using simple commands such as CREATE, INSERT, SELECT, UPDATE, DELETE, JOIN, COMMIT, and ROLLBACK. It is declarative, which means you usually describe what result you want, and the DBMS decides how to execute it.

Simple idea: If a database is like a collection of organized tables, SQL is the language used to create those tables, add data, search data, change data, delete data, and control access.

Why SQL is Important

It is the most widely used language for relational databases.
It supports database creation, table creation, data insertion, querying, and updates.
It helps enforce data integrity using keys and constraints.
It supports joins, grouping, filtering, sorting, and subqueries.
It provides transaction control using commit, rollback, and savepoints.
It is used by developers, data analysts, DBAs, testers, and reporting teams.

SQL Command Categories

Category	Full Name	Common Commands	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE, RENAME	Defines and changes database structure.
DML	Data Manipulation Language	INSERT, UPDATE, DELETE, MERGE	Adds, modifies, and removes table data.
DQL	Data Query Language	SELECT	Retrieves data from one or more tables.
DCL	Data Control Language	GRANT, REVOKE	Controls user privileges and permissions.
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manages transactions and permanent changes.

Basic SQL Syntax Rules

SQL keywords are usually written in uppercase, but most DBMS products treat them as case-insensitive.
Table and column names should be meaningful, consistent, and easy to read.
String and date values are written inside single quotes, such as 'Asha' or '2026-05-21'.
SQL statements usually end with a semicolon.
Use comments with -- for single-line comments.
Use NULL for missing or unknown values, not an empty string or zero.

Common SQL Data Types

Data Type	Purpose	Example
`INT`	Whole numbers.	student_id INT
`DECIMAL(p, s)`	Exact decimal values.	salary DECIMAL(10,2)
`VARCHAR(n)`	Variable-length text.	name VARCHAR(100)
`CHAR(n)`	Fixed-length text.	gender CHAR(1)
`DATE`	Date value.	hire_date DATE
`DATETIME` or `TIMESTAMP`	Date and time value.	created_at TIMESTAMP
`BOOLEAN`	True or false value, depending on DBMS support.	is_active BOOLEAN

DDL: Creating Database Structure

DDL commands define or modify database objects such as databases, tables, constraints, and indexes. DDL changes table structure rather than table data.

ddl-basics.sql

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dept_id INT,
    salary DECIMAL(10, 2) DEFAULT 0,
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

ALTER TABLE employees
ADD phone VARCHAR(20);

CREATE INDEX idx_employee_email
ON employees(email);

DROP TABLE old_employees;

SQL Constraints

Constraints are rules applied to table columns to protect data accuracy and consistency.

Constraint	Meaning	Example
`PRIMARY KEY`	Uniquely identifies each row and cannot be null.	emp_id INT PRIMARY KEY
`FOREIGN KEY`	References a key in another table.	dept_id references departments.
`NOT NULL`	Column must have a value.	emp_name VARCHAR(100) NOT NULL
`UNIQUE`	No duplicate values in the column.	email VARCHAR(100) UNIQUE
`CHECK`	Value must satisfy a condition.	salary CHECK (salary >= 0)
`DEFAULT`	Provides a value if none is supplied.	status DEFAULT 'ACTIVE'

DML: Insert, Update, and Delete

DML commands change the data stored inside tables. Use WHERE carefully with UPDATE and DELETE; without it, all matching rows may be changed or removed.

dml-basics.sql

INSERT INTO departments (dept_id, dept_name, location)
VALUES (1, 'Engineering', 'Pune');

INSERT INTO employees (emp_id, emp_name, email, dept_id, salary, hire_date)
VALUES (101, 'Asha Sharma', 'asha@example.com', 1, 75000, '2026-01-10');

INSERT INTO employees (emp_id, emp_name, email, dept_id, salary)
VALUES
    (102, 'Rahul Verma', 'rahul@example.com', 1, 68000),
    (103, 'Neha Singh', 'neha@example.com', 1, 72000);

UPDATE employees
SET salary = salary + 5000
WHERE emp_id = 101;

DELETE FROM employees
WHERE emp_id = 103;

DQL: SELECT Statement

SELECT is used to retrieve data from tables. It can filter rows, choose columns, sort output, group rows, join tables, and calculate aggregate values.

select-basics.sql

SELECT *
FROM employees;

SELECT emp_name, email, salary
FROM employees;

SELECT emp_name, salary
FROM employees
WHERE salary > 70000;

SELECT emp_name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000
ORDER BY salary DESC;

SELECT DISTINCT dept_id
FROM employees;

WHERE Clause Operators

Operator	Purpose	Example
`=`, `<>`, `>`, `<`	Comparison operators.	`salary > 50000`
`AND`, `OR`, `NOT`	Logical operators.	`dept_id = 1 AND salary > 60000`
`BETWEEN`	Checks range.	`salary BETWEEN 50000 AND 80000`
`IN`	Checks a list of values.	`dept_id IN (1, 2, 3)`
`LIKE`	Pattern matching.	`emp_name LIKE 'A%'`
`IS NULL`	Checks missing values.	`phone IS NULL`

Aggregate Functions and GROUP BY

Aggregate functions calculate a single result from multiple rows. GROUP BY groups rows before applying aggregate functions. HAVING filters grouped results.

group-by-having.sql

SELECT COUNT(*) AS total_employees
FROM employees;

SELECT
    dept_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary
FROM employees
GROUP BY dept_id;

SELECT dept_id, COUNT(*) AS employee_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 2;

ORDER BY, LIMIT, and Aliases

ORDER BY sorts result rows.
ASC means ascending order and DESC means descending order.
LIMIT restricts the number of rows in MySQL, PostgreSQL, and SQLite. SQL Server uses TOP or OFFSET FETCH.
AS gives a temporary alias to a column or table.

SQL Joins

A join combines rows from two or more tables based on related columns. Joins are essential in normalized relational databases.

Join Type	Meaning
INNER JOIN	Returns only matching rows from both tables.
LEFT JOIN	Returns all rows from left table and matching rows from right table.
RIGHT JOIN	Returns all rows from right table and matching rows from left table.
FULL OUTER JOIN	Returns rows when there is a match in either table, depending on DBMS support.
CROSS JOIN	Returns every combination of rows from both tables.

joins.sql

SELECT
    e.emp_id,
    e.emp_name,
    d.dept_name
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.dept_id;

SELECT
    d.dept_name,
    e.emp_name
FROM departments d
LEFT JOIN employees e
    ON d.dept_id = e.dept_id;

Subqueries

A subquery is a query inside another query. Subqueries are used when one query depends on the result of another query.

subqueries.sql

SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location = 'Pune'
);

DCL: Access Control

DCL commands control user permissions. These commands are commonly used by database administrators.

dcl-basics.sql

GRANT SELECT, INSERT, UPDATE
ON employees
TO app_user;

REVOKE UPDATE
ON employees
FROM app_user;

TCL: Transaction Control

TCL commands manage transactions. A transaction is a logical unit of work that should succeed completely or fail completely.

tcl-transaction.sql

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;

-- If something goes wrong, use:
-- ROLLBACK;

SQL Execution Order

SQL is written in one order but logically processed in another order. This is important for understanding WHERE, GROUP BY, and HAVING.

Logical Order	Clause	Purpose
1	FROM / JOIN	Choose source tables and combine rows.
2	WHERE	Filter individual rows.
3	GROUP BY	Create groups.
4	HAVING	Filter groups.
5	SELECT	Choose output columns and expressions.
6	ORDER BY	Sort final result.

Common SQL Mistakes

Mistake	Correct Approach
Using `UPDATE` or `DELETE` without `WHERE`.	Always check the condition first using `SELECT`.
Using `= NULL`.	Use `IS NULL` or `IS NOT NULL`.
Using `WHERE` to filter aggregate results.	Use `HAVING` after `GROUP BY`.
Selecting columns not in `GROUP BY` without aggregation.	Group by all non-aggregated selected columns, depending on DBMS rules.
Forgetting join condition.	Always define the relationship using `ON` to avoid accidental cross joins.
Confusing `DROP`, `DELETE`, and `TRUNCATE`.	`DROP` removes table structure, `DELETE` removes rows, `TRUNCATE` quickly removes all rows.

Interview and Exam Questions

Question	Short Answer
What is SQL?	SQL is Structured Query Language used to manage and query relational databases.
What is DDL?	Data Definition Language, used to define or modify database structure.
What is DML?	Data Manipulation Language, used to insert, update, and delete data.
What is DQL?	Data Query Language, mainly the SELECT command.
Difference between WHERE and HAVING?	WHERE filters rows before grouping; HAVING filters groups after grouping.
Difference between DELETE and TRUNCATE?	DELETE removes selected rows and can use WHERE; TRUNCATE removes all rows quickly and keeps structure.
What is a join?	A join combines rows from multiple tables based on related columns.
What is a subquery?	A query written inside another query.
What is a transaction?	A group of database operations treated as one logical unit of work.

Quick Revision Notes

SQL is used to manage relational databases.
DDL defines structure; DML changes data; DQL retrieves data.
DCL controls permissions; TCL manages transactions.
Primary keys identify rows; foreign keys connect tables.
SELECT can use WHERE, ORDER BY, GROUP BY, HAVING, joins, and subqueries.
WHERE filters rows; HAVING filters groups.
INNER JOIN returns matches; LEFT JOIN keeps all rows from the left table.
Use transactions for operations that must succeed or fail together.

Key Takeaways

SQL stands for Structured Query Language.
DDL commands create and modify database structure.
DML commands insert, update, and delete table data.
DQL retrieves data using SELECT.
DCL manages permissions using GRANT and REVOKE.
TCL manages transactions using COMMIT, ROLLBACK, and SAVEPOINT.
Constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT protect data integrity.
WHERE filters rows, while HAVING filters grouped results.
Joins combine data from multiple related tables.
Transactions ensure related database operations are saved or undone together.