SQL Basics DDL, DML, DCL, TCL Commands

SQL is a practical DBMS topic that becomes clear when you connect the definition to a small working example.

Use this page to understand what happens, why it happens, how to verify it, and what mistake usually breaks the concept.

After reading, practice SQL with a normal case, a boundary case, and a broken case so the idea becomes usable instead of memorized.

SQL Basics DDL DML DCL TCL Commands should be studied as a practical database design lesson, not as a label. Start by naming the input, the rule that changes the input, and the result a learner should be able to predict after reading the page.

In the dbms > sql-basics page, the notes should connect the definition with a working scenario, a mistake that beginners actually make, and the exact check that proves the fix. That makes the topic useful for coding, debugging, and interview revision.

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.

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.

Deep Study Notes for SQL

SQL should be learned as a practical DBMS skill, not only as a definition. Start by asking what problem the topic solves, what input or state it receives, what rule it applies, and what visible result proves it worked.

A strong explanation of SQL includes the normal case, a boundary case, and a failure case. When you practice, write down the before-state, the operation, the after-state, and the reason the result changed.

This lesson was expanded because the audit reported: limited checklist/practice/mistake/FAQ notes . The added notes below focus on clearer explanation, more examples, and concrete practice so the topic is easier to understand from the page itself.

Define the exact problem solved by SQL before looking at syntax.
Trace one small example by hand and describe every step in plain language.
Identify what changes when the input is empty, repeated, invalid, delayed, or larger than expected.
Connect the topic to a realistic project scenario instead of treating it as isolated theory.
Verify your answer with output, logs, query results, browser behavior, compiler feedback, or a state table.

Worked Explanation: Using SQL Correctly

Imagine you are adding SQL to a small learning project. The first step is to choose the smallest scenario that still shows the main idea. Avoid starting with a large production design; it hides the concept behind too many details.

Next, isolate the moving parts. Name the input, the rule, the output, and the possible error. This habit makes the topic easier to debug because you can see whether the problem is caused by bad data, wrong configuration, incorrect syntax, timing, permissions, or misunderstanding of the rule.

Finally, compare two versions: one correct version and one intentionally broken version. The broken version is valuable because it teaches you how the topic fails in real work, which is usually what interviews and debugging tasks test.

Normal case: show the expected behavior with simple, valid input.
Boundary case: test the smallest, largest, empty, repeated, or unusual value that still belongs to the topic.
Failure case: introduce one realistic mistake and explain the symptom it creates.
Repair step: change one thing at a time so you know exactly what fixed the problem.

SQL SQL lab setup

CREATE TABLE lesson_sql (
    id INT PRIMARY KEY,
    description VARCHAR(120),
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

INSERT INTO lesson_sql VALUES
(1, 'SQL normal case', 1000.00, 'active'),
(2, 'SQL boundary case', 0.00, 'review');

SELECT * FROM lesson_sql;

SQL reasoning query

BEGIN;
UPDATE lesson_sql
SET status = 'checked'
WHERE amount >= 0;

SELECT status, COUNT(*) AS rows_seen
FROM lesson_sql
GROUP BY status;
ROLLBACK;

-- Explanation: ROLLBACK lets you test the concept safely before committing changes.

Key Takeaways

State the purpose of SQL in one sentence before using it.
Create a tiny DBMS example that demonstrates the topic without unrelated code.
Test one normal input, one edge input, and one incorrect input for SQL.
Explain the result using before-state, operation, and after-state.
Add a verification step such as output, logs, query results, browser behavior, or compiler feedback.

Common Mistakes to Avoid

WRONG Memorizing SQL as a definition only.

RIGHT Pair the definition with a small working example and a failure example.

The fastest way to remember the topic is to explain why the output changes.

WRONG Copying syntax without checking the state before and after.

RIGHT Write the input state, apply the rule, then inspect the output state.

State tracing turns confusing behavior into a visible sequence.

WRONG Ignoring the error path for SQL.

RIGHT Create one intentionally broken version and document the symptom and fix.

A page is much easier to learn from when it explains both success and failure.

WRONG Memorizing SQL Basics DDL DML DCL TCL Commands without the situation where it is useful.

RIGHT Connect SQL Basics DDL DML DCL TCL Commands to a concrete database design task.

Purpose makes syntax easier to recall.

Practice Tasks

Build the smallest working demo for SQL and write what each line does.
Change one input or setting and predict the result before running it.
Break the example in a realistic way, then fix it and describe the repair.
Create a two-column note comparing when to use SQL and when another approach is better.
Explain SQL aloud as if teaching a beginner who knows basic DBMS only.

Frequently Asked Questions

What should I understand first in SQL?

Understand the problem it solves, the input or state it works on, and the visible result that proves the concept is working.

How should I practice SQL?

Use one tiny correct example, one boundary example, and one broken example. Compare the output or state after each change.

Why do beginners struggle with SQL?

They often memorize the term without tracing the behavior. Tracing makes the rule easier to remember and debug.

What should I remember first about SQL Basics DDL DML DCL TCL Commands?

Remember the problem it solves in database design, then attach the syntax or steps to that problem.

Previous Next

SQL Basics DDL, DML, DCL, TCL Commands