DDL and DML Commands

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

DDL and DML Commands

The two halves of SQL — statements that define structure, and statements that manipulate data.

🌍 Where this lives: the DDL/DML distinction has a consequence people learn the hard way: in most databases DDL cannot be rolled back. Run DROP TABLE orders in the wrong terminal and there is no ROLLBACK — you restore from backup. MySQL and Oracle even auto-commit before and after every DDL statement, so any open transaction is silently committed too. PostgreSQL is the notable exception (its DDL is transactional, which is why migration tools prefer it). That single difference explains why every production deploy checklist says "take a backup before migrating". Search "transactional DDL PostgreSQL vs MySQL".

The five SQL sub-languages

DDL — Data Definition Language (structure) CREATE make a new object ALTER modify an existing object DROP remove an object entirely TRUNCATE remove all rows, keep the structure RENAME change an object's name COMMENT attach documentation DML — Data Manipulation Language (the rows) INSERT add rows UPDATE modify rows DELETE remove rows MERGE insert-or-update (upsert) SELECT retrieve rows (some texts classify SELECT as DQL — Data Query Language — since it changes nothing) DCL — Data Control Language (permissions) GRANT give a privilege REVOKE take it away TCL — Transaction Control Language (atomicity) COMMIT make changes permanent ROLLBACK undo them SAVEPOINT a partial rollback point SET TRANSACTION set the isolation level THE CRITICAL PRACTICAL DIFFERENCE: can it be rolled back? DML (INSERT/UPDATE/DELETE) YES, always DDL (CREATE/DROP/ALTER) depends on the DBMS PostgreSQL YES — fully transactional DDL SQL Server YES (mostly) MySQL/MariaDB NO — implicit commit Oracle NO — implicit commit TRUNCATE PostgreSQL: yes; Oracle/MySQL: no If your database auto-commits DDL, an ALTER halfway through a migration script leaves the schema in a partly-migrated state with no way back. That is why migrations are written as small reversible steps with a backup taken first.

DDL — defining and changing structure

ddl.sql

-- ===== CREATE =====
CREATE TABLE department (
    code   CHAR(6)       PRIMARY KEY,
    name   VARCHAR(60)   NOT NULL UNIQUE,
    budget NUMERIC(12,2) NOT NULL DEFAULT 0
                         CHECK (budget >= 0)
);

CREATE TABLE student (
    roll      INTEGER      PRIMARY KEY,
    name      VARCHAR(60)  NOT NULL,
    dept_code CHAR(6)      NOT NULL REFERENCES department(code),
    marks     NUMERIC(5,2) CHECK (marks BETWEEN 0 AND 100),
    joined    DATE         NOT NULL DEFAULT CURRENT_DATE
);

-- CREATE TABLE AS (CTAS) — structure AND data from a query
CREATE TABLE toppers AS
    SELECT roll, name, marks FROM student WHERE marks >= 80;
-- NOTE: CTAS copies column types but NOT constraints,
-- defaults, or indexes. The copy has no primary key.

-- structure only, no rows
CREATE TABLE student_archive AS
    SELECT * FROM student WHERE 1 = 0;

-- ===== ALTER: the six common operations =====
ALTER TABLE student ADD COLUMN email VARCHAR(120);
ALTER TABLE student ALTER COLUMN name TYPE VARCHAR(120);
ALTER TABLE student ALTER COLUMN marks SET DEFAULT 0;
ALTER TABLE student ALTER COLUMN email SET NOT NULL;
    -- ^ FAILS if any existing row has email NULL
ALTER TABLE student RENAME COLUMN marks TO total_marks;
ALTER TABLE student DROP COLUMN email;

ALTER TABLE student
    ADD CONSTRAINT chk_pass CHECK (total_marks >= 0);
ALTER TABLE student DROP CONSTRAINT chk_pass;

-- ===== DROP: three levels of destruction =====
DROP TABLE toppers;                    -- fails if referenced
DROP TABLE IF EXISTS toppers;          -- no error if absent
DROP TABLE department CASCADE;         -- ALSO drops every
                                       -- dependent object:
                                       -- views, FK
                                       -- constraints in
                                       -- OTHER tables.
                                       -- Use with care.

-- ===== TRUNCATE vs DELETE — a favourite exam comparison
DELETE FROM student;            -- DML: row by row
TRUNCATE TABLE student;         -- DDL: deallocates pages

TRUNCATE vs DELETE — the full comparison: DELETE TRUNCATE ───────────────────────────────────────────────────────── category DML DDL WHERE clause yes no — all or nothing speed on 10M rows slow (row by row) fast (drops pages) logs each row yes no (minimal logging) fires triggers yes NO rollback always PostgreSQL yes; Oracle/MySQL no resets identity/ no yes (usually) sequence reclaims disk space not immediately immediately FK-referenced table blocked per row blocked entirely (unless CASCADE) WHY TRUNCATE SKIPPING TRIGGERS MATTERS: if you rely on a delete trigger to write an audit log or maintain a summary table, TRUNCATE silently bypasses it. Your audit trail then has a gap with no record that anything happened. VERIFIED BEHAVIOUR (PostgreSQL 18): CREATE TABLE t1 AS SELECT * FROM student; -- 4 rows BEGIN; DELETE FROM t1; SELECT COUNT(*) FROM t1; → 0 ROLLBACK; SELECT COUNT(*) FROM t1; → 4 ✔ fully restored DROP vs DELETE vs TRUNCATE, in one line each: DELETE — removes rows you choose; structure intact TRUNCATE — removes all rows; structure intact DROP — removes rows AND the structure itself

DML — INSERT, UPDATE, DELETE

dml.sql

-- ===== INSERT: four forms =====

-- 1. explicit column list (ALWAYS prefer this)
INSERT INTO student (roll, name, dept_code, marks)
    VALUES (101, 'Ram Bahadur', 'ACtE07', 87.5);

-- 2. multi-row, one statement — far faster than N
--    statements, because it is one round trip and one
--    transaction
INSERT INTO student (roll, name, dept_code, marks) VALUES
    (102, 'Sita Devi',    'ACtE07', 91.0),
    (103, 'Hari Prasad',  'AExE01', 55.0),
    (104, 'Gita Kumari',  'AExE01', 68.0);

-- 3. INSERT ... SELECT — copy from a query
INSERT INTO student_archive (roll, name, dept_code, marks, joined)
    SELECT roll, name, dept_code, marks, joined
    FROM   student WHERE joined < '2024-01-01';

-- 4. positional (no column list) — FRAGILE. Adding a column
--    to the table silently breaks every such statement.
-- INSERT INTO student VALUES (105,'X','ACtE07',70,'2026-01-01');

-- RETURNING — get back what was actually stored, including
-- generated defaults. Avoids a second round trip.
INSERT INTO student (roll, name, dept_code, marks)
    VALUES (105, 'Bikash Thapa', 'ACtE07', 94.5)
    RETURNING roll, joined;
--  roll |   joined
--  -----+------------
--   105 | 2026-08-05     <- the DEFAULT that was applied

-- UPSERT (MERGE) — insert, or update if the key exists
INSERT INTO student (roll, name, dept_code, marks)
    VALUES (105, 'Bikash Thapa', 'ACtE07', 96.0)
ON CONFLICT (roll) DO UPDATE
    SET marks = EXCLUDED.marks,
        name  = EXCLUDED.name;
-- EXCLUDED refers to the row that WOULD have been inserted.
-- Standard SQL spells this MERGE INTO ... WHEN MATCHED.

-- ===== UPDATE =====

-- ALWAYS write the WHERE first. An UPDATE with no WHERE
-- modifies EVERY row, and that mistake is unrecoverable
-- outside a transaction.
UPDATE student SET marks = marks + 5 WHERE roll = 103;

-- several columns at once
UPDATE student
   SET marks = LEAST(marks * 1.05, 100),
       name  = INITCAP(name)
 WHERE dept_code = 'ACtE07';

-- UPDATE with a subquery: give everyone below the average
-- a 5-mark grace
UPDATE student
   SET marks = marks + 5
 WHERE marks < (SELECT AVG(marks) FROM student)
   AND marks + 5 <= 100;

-- UPDATE ... FROM (PostgreSQL) — join-style update
UPDATE student s
   SET dept_code = d.code
  FROM department d
 WHERE d.name = 'Computer Engineering'
   AND s.dept_code IS NULL;

-- ===== DELETE =====
DELETE FROM student WHERE marks < 32;

DELETE FROM student
 WHERE dept_code IN (SELECT code FROM department
                     WHERE budget = 0);

-- the safe habit for any destructive statement:
--   1. write it as a SELECT first
--        SELECT * FROM student WHERE marks < 32;
--   2. inspect the rows
--   3. change SELECT * to DELETE
--   4. or wrap in BEGIN ... check ... COMMIT/ROLLBACK
BEGIN;
    DELETE FROM student WHERE marks < 32;
    -- inspect: SELECT COUNT(*) FROM student;
ROLLBACK;   -- or COMMIT if the count looks right

THE MOST EXPENSIVE TYPO IN SQL: UPDATE student SET marks = 0; -- no WHERE DELETE FROM student; -- no WHERE Both are valid SQL. Both affect every row. Outside a transaction, neither can be undone. THREE DEFENCES, in order of reliability: 1. autocommit OFF in your client, so every statement is inside a transaction until you COMMIT 2. write the WHERE clause BEFORE the SET or the table name 3. run it as a SELECT first and check the row count The mysql client has --safe-updates (also spelled -U/--i-am-a-dummy) which REFUSES an UPDATE or DELETE with no WHERE on an indexed column. It exists because this mistake is made often enough to warrant a flag. WHY multi-row INSERT is faster, quantified: 1000 separate INSERT statements = 1000 network round trips + 1000 transactions (if autocommit) + 1000 parse/plan cycles 1 INSERT with 1000 VALUES rows = 1 round trip, 1 transaction, 1 plan Typically 10-50× faster. For bulk loading, COPY (PostgreSQL) or LOAD DATA INFILE (MySQL) is faster still.

The RETURNING clause deserves more attention than it gets. Without it, inserting a row with a generated key requires a second query to discover what the key was — and in a concurrent system, "get the last inserted id" is a genuine source of race conditions. RETURNING makes it atomic and is supported by PostgreSQL, SQLite, MariaDB and Oracle (as RETURNING INTO).

Worked example — a complete session

session.sql

-- verified against PostgreSQL 18
CREATE TABLE student (roll INT PRIMARY KEY, name TEXT NOT NULL,
                      dept CHAR(6), marks NUMERIC(5,2));
INSERT INTO student VALUES
    (101,'Ram','ACtE07',87.5), (102,'Sita','ACtE07',91.0),
    (103,'Hari','AExE01',55.0), (104,'Gita','AExE01',68.0);

-- aggregate by department
SELECT dept, COUNT(*) AS n,
       ROUND(AVG(marks),2) AS avg_marks,
       MIN(marks) AS lowest, MAX(marks) AS highest
FROM   student
GROUP  BY dept
HAVING COUNT(*) >= 2
ORDER  BY avg_marks DESC;
--   dept  | n | avg_marks | lowest | highest
--  -------+---+-----------+--------+---------
--  ACtE07 | 2 |     89.25 |  87.50 |   91.00
--  AExE01 | 2 |     61.50 |  55.00 |   68.00

-- WHERE vs HAVING — a guaranteed exam point:
--   WHERE  filters ROWS   BEFORE grouping
--   HAVING filters GROUPS AFTER grouping
-- and therefore HAVING may use aggregates while WHERE
-- cannot.
SELECT dept, COUNT(*) AS passed
FROM   student
WHERE  marks >= 60          -- per-row filter, first
GROUP  BY dept
HAVING COUNT(*) >= 1;       -- per-group filter, after
--   dept  | passed
--  -------+--------
--  ACtE07 |      2
--  AExE01 |      1

-- LOGICAL EVALUATION ORDER (not the written order!):
--   1. FROM      / JOIN
--   2. WHERE
--   3. GROUP BY
--   4. HAVING
--   5. SELECT    <- aliases are created HERE
--   6. DISTINCT
--   7. ORDER BY  <- so it CAN use SELECT aliases
--   8. LIMIT / OFFSET
--
-- This order explains two things that confuse everyone:
--   · you cannot use a SELECT alias in WHERE (step 2 runs
--     before step 5)
--   · you CAN use it in ORDER BY (step 7 runs after step 5)
SELECT roll, marks * 1.1 AS scaled
FROM   student
-- WHERE scaled > 90        ✗ ERROR: column "scaled" does
--                             not exist
WHERE  marks * 1.1 > 90     -- ✔ repeat the expression
ORDER  BY scaled DESC;      -- ✔ alias works here

�ìŒ Go further: the DDL statements you write are themselves stored as data — which is why information_schema and pg_catalog let you query your own schema with SQL. Tools exploit this heavily: schema-diff utilities compare two catalogs and generate the ALTER statements to reconcile them, which is how Liquibase, Flyway and Atlas produce migrations automatically. Search "schema diff migration generation" and "online DDL algorithm=inplace", the second being how MySQL performs some ALTERs without locking the table.

💡 Exam angle: classify commands correctly into DDL, DML, DCL and TCL — that classification alone is a common one-mark question. The TRUNCATE vs DELETE vs DROP comparison is asked almost every year: DML vs DDL, WHERE clause, triggers fired, rollback, identity reset. Know that DDL is auto-committed in MySQL and Oracle but transactional in PostgreSQL. Be ready to write INSERT/UPDATE/DELETE with subqueries, and state the WHERE vs HAVING distinction with the logical evaluation order as the reason.

Syllabus points

DDL (create, alter, drop)
DML (select, insert, update, delete) queries

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.