SQL Basics DDL, DML, DCL, TCL Commands

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.

NULL and Three-Valued Logic

NULL represents a missing or unknown value; it is not zero, an empty string, or the text NULL. Comparisons such as score = NULL do not become true or false. They become unknown, so a WHERE clause does not keep that row. Use IS NULL and IS NOT NULL when testing for missing values.

Unknown also affects compound conditions. TRUE AND UNKNOWN is unknown, while TRUE OR UNKNOWN is true. This explains results that otherwise look surprising when nullable columns participate in filters. Decide whether missing values belong in the result, then express that choice explicitly instead of relying on accidental behavior.

Filter Known and Missing Scores

SELECT student_id, score
FROM exam_results
WHERE score >= 50 OR score IS NULL;

Output

Returns passing rows and rows whose score has not been recorded; failing known scores are excluded.

Safe Data Changes

Treat UPDATE and DELETE as a selection problem before treating them as a write. Build a SELECT with the same WHERE clause, inspect the row count and key values, then run the change inside a transaction when the database and operation support transactional behavior. Recheck the result before COMMIT; use ROLLBACK when the affected set is wrong.

Applications should send user values through parameterized statements. Parameters separate data from SQL syntax, reduce quoting mistakes, and prevent input from changing the intended command structure. Parameters cannot normally replace identifiers such as table names, so dynamic identifiers require a strict allow-list rather than raw user input.

Write Review Sequence

State the business rule in plain language, including boundary values.
Run SELECT with the proposed predicate and inspect primary keys, not only the count.
Begin a transaction, execute the write, and query the changed rows again.
Commit only after the post-change state satisfies constraints and expectations.

Portable SQL Decisions

Core SELECT, INSERT, UPDATE, DELETE, joins, grouping, and transactions are broadly shared, but database products differ in identity columns, date functions, pagination, upsert syntax, data types, and DDL transaction behavior. Learn the relational idea first, then verify product-specific syntax in the manual for the database version you deploy.

Constraints belong in the database even when an application validates input. A NOT NULL, CHECK, UNIQUE, primary-key, or foreign-key rule protects every writer, including imports and maintenance scripts. Application validation can provide a friendly message; the constraint remains the final integrity boundary.

Need	SQL Tool	Check Before Use
Read selected rows	SELECT with WHERE	NULL and boundary behavior
Summarize groups	GROUP BY with aggregates	Non-grouped selected columns
Preserve all left rows	LEFT JOIN	Right-table filters in WHERE
Make several writes atomic	Transaction	Engine support and error handling
Enforce valid data	Constraints	Existing invalid rows and migration plan

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.

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.

Before you move on