SQL Basics: DDL, DML, DCL and TCL Commands with Examples

SQL (Structured Query Language) is the standard language of relational databases. You use it to define tables, put rows into them, ask questions of them, decide who may touch them, and group changes into transactions. It is declarative: you describe the result you want, and the DBMS's optimizer decides how to get it. The core is standardized (ISO/IEC 9075), and every product adds its own extensions; the examples here run on MySQL 8, and the places where MySQL differs from standard SQL are pointed out.

SQL command categories

1. DDL defines structure. CREATE TABLE writes the table's definition into the catalog; there are no rows yet. In MySQL a DDL statement also commits any open transaction.
2. DML changes the rows. INSERT adds two accounts; the structure is untouched. With autocommit on, as MySQL has by default, each statement is its own transaction.
3. DCL controls who may do what. GRANT records a privilege for the user intern: it may read accounts and nothing else. The table and its rows do not change.
4. TCL groups statements. START TRANSACTION opens a transaction, and the UPDATE that follows changes account 1 only inside it: under MySQL's default isolation level, other sessions still read 5000.
5. The second UPDATE moves the money into account 2, still inside the transaction. Halfway through, a ROLLBACK here would undo both changes at once.
6. COMMIT makes both changes permanent together. The total is unchanged, so no money was created or lost; a crash before COMMIT would have kept neither.
7. DQL reads. SELECT returns the committed rows as a result and changes nothing, which is why some books list it apart from DML.

Two practical points. MySQL runs in autocommit mode by default, so each statement is its own transaction unless you begin one explicitly. And in MySQL (and Oracle) a DDL statement causes an implicit commit: a CREATE or TRUNCATE in the middle of a transaction commits the work before it and cannot itself be rolled back. PostgreSQL, by contrast, can roll back most DDL.

DDL: creating and changing tables

CREATE TABLE departments (
  dept_id   CHAR(3)     PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE students (
  id    INT         PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(50) NOT NULL,
  dept  CHAR(3)     NOT NULL,
  marks INT         CHECK (marks BETWEEN 0 AND 100),
  city  VARCHAR(30) DEFAULT 'Pune',
  FOREIGN KEY (dept) REFERENCES departments (dept_id)
);

AUTO_INCREMENT is MySQL's way of generating ids; standard SQL (and PostgreSQL) writes GENERATED ALWAYS AS IDENTITY. ALTER TABLE changes an existing table:

ALTER TABLE students ADD COLUMN email VARCHAR(100) UNIQUE;
ALTER TABLE students MODIFY COLUMN city VARCHAR(50) DEFAULT 'Pune';  -- MySQL syntax
ALTER TABLE students RENAME COLUMN city TO home_city;                -- MySQL 8.0+
ALTER TABLE students DROP COLUMN email;
DROP TABLE IF EXISTS old_results;

MODIFY COLUMN is MySQL's form; standard SQL and PostgreSQL use ALTER COLUMN … TYPE and ALTER COLUMN … SET DEFAULT.

Constraints

Constraints are rules declared with the table and checked by the DBMS on every change:

MySQL enforces CHECK constraints from version 8.0.16; older versions parsed them and silently ignored them. The keys note covers primary and foreign keys and what ON DELETE CASCADE does.

DML: inserting, updating and deleting

INSERT INTO students (name, dept, marks, city) VALUES
  ('Asha',   'CSE', 91,   'Pune'),
  ('Vikram', 'CSE', 78,   'Delhi'),
  ('Meera',  'ECE', 85,   'Chennai'),
  ('Rohan',  'ME',  NULL, 'Pune'),
  ('Priya',  'ECE', 67,   'Delhi');

UPDATE students SET marks = marks + 2 WHERE dept = 'ECE';
DELETE FROM students WHERE marks IS NULL;

An UPDATE or DELETE without WHERE changes every row. MySQL's client offers a safe-updates mode that refuses such statements, and running them inside a transaction you can roll back is a good habit.

DELETE vs TRUNCATE vs DROP

SELECT: reading rows

After the inserts above (before the update and delete), students holds:

SELECT name, marks
FROM students
WHERE dept = 'CSE'
ORDER BY marks DESC;

WHERE filters rows, ORDER BY sorts them (ascending by default), and without ORDER BY the order of rows is not guaranteed at all. DISTINCT removes duplicate result rows, and LIMIT cuts the result:

SELECT DISTINCT city FROM students ORDER BY city;          -- Chennai, Delhi, Pune
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 2;          -- top two
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 2 OFFSET 2; -- next two

Rohan's NULL comes last here because MySQL sorts NULL as the smallest value: first in ascending order, last in descending. PostgreSQL does the opposite (NULLs sort as largest) and lets you write NULLS FIRST or NULLS LAST. LIMIT is MySQL and PostgreSQL syntax; standard SQL writes FETCH FIRST 2 ROWS ONLY, and SQL Server uses TOP.

Other filters: marks BETWEEN 70 AND 90 is inclusive at both ends (Vikram 78, Meera 85), and dept IN ('ECE', 'ME') matches any value in the list (Meera, Rohan, Priya).

A query is written SELECT first but processed in a fixed logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That order explains why a column alias works in ORDER BY but not in WHERE, and why WHERE filters rows while HAVING filters groups:

1. A query is written SELECT first, but processed in the numbered order. Step 1, FROM: the DBMS starts from every row of students, all 5 of them.
2. Step 2, WHERE marks IS NOT NULL keeps a row only when the condition is TRUE. Rohan has no marks, so his row goes; 4 rows remain. SELECT has not run yet, so WHERE cannot use the alias best.
3. Step 3, GROUP BY city collects the rows into 3 groups: Pune (1), Delhi (2), Chennai (1). From here on, each group is one row of the result.
4. Step 4, HAVING filters groups, not rows, and can use aggregates. Delhi's best is 78, not above 80, so that group goes.
5. Step 5, SELECT computes the output columns: one row per remaining group, city and MAX(marks) named best: Pune 91, Chennai 85.
6. Step 6, ORDER BY best DESC sorts the result, and may use the alias because SELECT has run. Pune 91 then Chennai 85: without ORDER BY nothing guarantees any order.
7. Step 7, LIMIT 1 keeps the first row of the sorted result: Pune, 91, the city with the best score among those above 80.

NULL and three-valued logic

NULL means a value is missing or unknown. It is not zero and not an empty string, and any comparison with it gives a third truth value, UNKNOWN. WHERE keeps a row only when its condition is TRUE.

So Rohan's row slips through both of two complementary filters, and = NULL matches nothing at all:

Arithmetic with NULL gives NULL (marks + 5 is NULL for Rohan), and COALESCE(marks, 0) substitutes a value for NULL. MySQL's <=> is a null-safe equality that returns 1 for NULL <=> NULL; standard SQL writes IS NOT DISTINCT FROM. NULL also explains the classic NOT IN trap with subqueries, covered in the joins note.

Pattern matching with LIKE

LIKE compares a string with a pattern: % matches any run of characters (including none) and _ matches exactly one.

In MySQL's default collation (utf8mb4_0900_ai_ci, case- and accent-insensitive) 'a%' also matches Asha; PostgreSQL's LIKE is case-sensitive and provides ILIKE. To match a literal % or _, escape it: LIKE '50\%%' in MySQL, or LIKE '50!%%' ESCAPE '!' in any SQL. A pattern that starts with % cannot use an ordinary index, so it scans the whole table.

DCL and TCL

DCL hands out and withdraws privileges:

GRANT SELECT, INSERT ON college.students TO 'intern'@'localhost';
REVOKE INSERT ON college.students FROM 'intern'@'localhost';

TCL groups statements so that they take effect together or not at all:

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to undo both

SAVEPOINT name marks a point inside a transaction that ROLLBACK TO SAVEPOINT name can return to. The transactions note explains what COMMIT and ROLLBACK guarantee.

Common mistakes

• Writing = NULL instead of IS NULL: the comparison is UNKNOWN and matches nothing.
• Expecting WHERE x > 80 and WHERE x <= 80 to cover every row: NULLs are in neither.
• Relying on row order without ORDER BY: it can change between runs and versions.
• Running UPDATE or DELETE without WHERE by accident: test the WHERE with a SELECT first.
• Assuming TRUNCATE can be rolled back in MySQL: it commits implicitly.
• Assuming CHECK constraints were always enforced in MySQL: only from 8.0.16.

Interview questions

What are the categories of SQL commands? DDL (CREATE, ALTER, DROP, TRUNCATE) for structure, DML (INSERT, UPDATE, DELETE) and DQL (SELECT) for data, DCL (GRANT, REVOKE) for permissions and TCL (COMMIT, ROLLBACK, SAVEPOINT) for transactions.

Why is TRUNCATE faster than DELETE? DELETE removes rows one at a time, logging each and firing triggers. TRUNCATE empties the table as a whole, much like dropping and recreating it, so it logs far less and skips per-row work. That is also why it cannot take a WHERE clause.

What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregates such as COUNT(*). The aggregation note works both.

What is the difference between a primary key and a UNIQUE constraint? A table has one primary key, which never allows NULL. It may have many UNIQUE constraints, which in MySQL allow any number of NULLs.

What does NULL + 10 return? NULL. Any arithmetic with NULL gives NULL, and any comparison gives UNKNOWN; use COALESCE or IFNULL to substitute a value.

Which rows does WHERE marks NOT BETWEEN 70 AND 90 return in the table above? Asha (91) and Priya (67). Rohan is excluded because his marks are NULL, and NOT applied to UNKNOWN is still UNKNOWN.

What does a DEFAULT constraint do when you insert NULL explicitly? It does nothing; the default is used only when the column is omitted from the INSERT. Inserting NULL explicitly stores NULL, or fails if the column is NOT NULL.

Is DDL transactional? It depends on the DBMS. MySQL and Oracle commit implicitly around DDL statements, so they cannot be rolled back; PostgreSQL runs most DDL inside transactions and can roll it back.

Next, combine tables: SQL Joins. These queries are the ground of the SQL (Basic) skill test.

Practise on real tables: Scholarship Shortlist by CGPA or Hackathon Wins, Canteen Orders Not Placed With the FEST50 Coupon (the NULL trap) and the rest of the SQL practice problems.