SQL: DDL and DML
Practical, exam-focused notes on SQL Data Definition Language (DDL) and Data Manipulation Language (DML): what each statement does, exact syntax to remember, worked SQL examples with expected outputs, common exam mistakes and quick practice questions.
Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.
Why it matters
DDL and DML are the two main sets of SQL statements an engineer uses when designing a database schema and when creating, reading, updating or deleting the data that lives in that schema. In real projects you use DDL to create and change tables and constraints; you use DML every day to insert rows, update values and fetch query results for applications.
Key ideas
Core terms are defined before first use; DBMS-specific features are noted where used.
- Relational database: a database that stores data in tables (also called relations). A table is defined by a set of columns (attributes) and holds zero or more rows (tuples).
- Schema: the named collection of table definitions, types, and constraints. A schema names what objects exist in a database.
- Data Definition Language (DDL): the set of SQL statements used to define, alter and remove schema objects (for example: CREATE, ALTER, DROP — e.g., CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX). DDL changes the database structure.
- Data Manipulation Language (DML): the set of SQL statements that operate on the data inside tables (for example: INSERT, UPDATE, DELETE, and often SELECT). DML changes or reads table rows. Note: some texts classify SELECT as Data Query Language (DQL); state the convention you are using in exams.
- Constraint: a rule attached to a table or column that enforces data integrity. Common constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. Note: DEFAULT is a column property (default value), not a constraint type.
Exact behaviour and common exam facts (state clearly):
- CREATE TABLE: defines a new table and its columns and constraints. If you specify a PRIMARY KEY, the column(s) must be NOT NULL and uniquely identify rows.
- What to draw in an exam if asked: draw a rectangle labelled with the table name; list columns each on its own line; mark primary key columns with PK:, foreign keys with FK->ReferencedTable(Column), and other constraints beside each column.
- ALTER TABLE: modifies a table's structure. Common operations: ADD COLUMN, DROP COLUMN, MODIFY/ALTER COLUMN type, ADD CONSTRAINT, DROP CONSTRAINT.
- Note: exact ALTER syntax differs between DBMS (MySQL, Oracle, PostgreSQL). Exams usually accept standard variants; show a correct example and note dialect differences.
- DROP TABLE: removes a table and its data. Once committed, this is irreversible in typical use (unless the DBMS supports flashback/undrop). Many DBMS perform an implicit commit around DDL; in DBMS with transactional DDL (e.g., PostgreSQL) you can ROLLBACK the DROP before committing.
- INSERT: adds new rows. Two common forms: INSERT ... VALUES (single/multiple rows) and INSERT ... SELECT (copying rows from other tables).
- UPDATE: modifies existing rows. Always use a WHERE clause to limit changes; without WHERE it updates every row.
- DELETE: removes rows matching a WHERE. TRUNCATE is often discussed: it removes all rows and may reset storage; it is generally a DDL statement and often causes an implicit commit, but exact transactional behaviour and privileges vary by DBMS.
- Foreign key enforcement: a FOREIGN KEY reference from child to parent prevents deleting or changing parent rows if child rows exist, unless ON DELETE/UPDATE actions are defined. Common actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT/NO ACTION (behaviour and default timing may vary by DBMS).
- Transactions and commits: DML statements are usually transactional and need COMMIT to make changes permanent. DDL often causes implicit commit in several DBMS, but some DBMS (e.g., PostgreSQL) allow transactional DDL; treat this as DBMS-dependent and mention the difference in answers.
Assumptions: single-node RDBMS, standard SQL behaviour unless a DBMS-specific note is added.
Formulas
(For SQL, these are the syntax lines to remember; each line is a separate syntax pattern.)
CREATE TABLE table_name (col1 datatype [constraint], col2 datatype [constraint], ...); — create a table named table_name with columns.
DROP TABLE table_name; — remove the table and its data.
ALTER TABLE table_name ADD COLUMN col_name datatype [constraint]; — add a new column.
ALTER TABLE table_name DROP COLUMN col_name; — remove a column (DBMS may refuse if column used in constraints).
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (...) ; — add a named constraint (e.g., PRIMARY KEY, FOREIGN KEY).
INSERT INTO table_name (col1, col2, ...) VALUES (v1, v2, ...); — insert one row.
INSERT INTO table_name (col1, col2, ...) SELECT colA, colB FROM other_table WHERE ...; — insert rows from a query.
UPDATE table_name SET col1 = expr1, col2 = expr2 WHERE search_condition; — update rows matching the condition.
DELETE FROM table_name WHERE search_condition; — delete rows matching the condition.
TRUNCATE TABLE table_name; — remove all rows quickly (DBMS-dependent transactional behaviour).
SELECT col_list FROM table_name [WHERE ...] [ORDER BY ...]; — read rows.
COMMIT; — make the current transaction’s changes permanent.
ROLLBACK; — undo the current transaction’s uncommitted changes.
SAVEPOINT sp_name; ROLLBACK TO SAVEPOINT sp_name; RELEASE SAVEPOINT sp_name; — transaction savepoint control (DBMS support varies).
Symbols: table_name, col, datatype, constraint are identifiers in SQL; WHERE search_condition is a boolean SQL expression. These apply to ANSI SQL-style relational DBMS; exact dialects may allow extra clauses (RETURNING, IF EXISTS, CASCADE, etc.).
Worked examples
All examples use a single schema. I show SQL statements and the expected resulting table output (text table). Assume a fresh database state before each example.
Example 1 — Create table, insert rows, update and select SQL: CREATE TABLE Students ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, year INT NOT NULL, cgpa DECIMAL(3,2) ); INSERT INTO Students (student_id, name, year, cgpa) VALUES (1, 'Anita', 2, 8.45), (2, 'Raju', 3, 7.80), (3, 'Meena', 2, 9.10); UPDATE Students SET cgpa = 8.60 WHERE student_id = 1; DELETE FROM Students WHERE student_id = 2; SELECT * FROM Students ORDER BY student_id;
Expected output after SELECT (rows in a simple text table):
| student_id | name | year | cgpa |
|---|---|---|---|
| 1 | Anita | 2 | 8.60 |
| 3 | Meena | 2 | 9.10 |
Example 2 — Foreign key constraint and attempted delete SQL: CREATE TABLE Departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(30) NOT NULL ); CREATE TABLE Employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(40) NOT NULL, dept_id INT, CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES Departments(dept_id) ); INSERT INTO Departments (dept_id, dept_name) VALUES (10, 'CSE'); INSERT INTO Employees (emp_id, emp_name, dept_id) VALUES (100, 'Sunil', 10); -- Now try to delete department 10 DELETE FROM Departments WHERE dept_id = 10;
Expected behaviour/output:
- The DELETE fails with a foreign-key constraint violation because Employees(emp_id=100) references Departments(dept_id=10). The exact error text is DBMS-dependent; state in answer: Operation fails with FOREIGN KEY constraint violation; parent row not deleted.
Example 3 — ALTER TABLE add column and backfill SQL: CREATE TABLE Courses (course_id INT PRIMARY KEY, title VARCHAR(50)); INSERT INTO Courses VALUES (1, 'DBMS'), (2, 'OS'); ALTER TABLE Courses ADD COLUMN credits INT DEFAULT 3; SELECT * FROM Courses ORDER BY course_id;
Expected output after SELECT:
| course_id | title | credits |
|---|---|---|
| 1 | DBMS | 3 |
| 2 | OS | 3 |
Note: behaviour is DBMS-dependent. Many systems (e.g., MySQL, PostgreSQL 11+) apply the DEFAULT to existing rows at ALTER time (possibly via metadata); others (e.g., SQL Server, unless you use WITH VALUES or add NOT NULL) leave existing rows as NULL and require UPDATE to backfill. If an exam asks, state the DBMS and assumption.
Common mistakes
- Expecting DDL to be transactional in all DBMS: some systems auto-commit around DDL while others (PostgreSQL) allow transactional DDL.
- Violating NOT NULL or PRIMARY KEY: trying to insert NULL into NOT NULL column or duplicate into PRIMARY KEY fails.
- Misunderstanding FOREIGN KEY: trying to insert a child row with a non-existing parent will fail unless the parent exists.
- Using wrong ALTER TABLE syntax from one DBMS in another (e.g., MODIFY vs ALTER COLUMN). Always state the DBMS if you use dialect-specific syntax in an exam.
For your exam
Question types you will see for this topic:
- Definitions and short notes (define DDL/DML, list statements and purpose).
- Write SQL programs (create schema, insert data, update/delete and show result table). Full marks require correct syntax, mention of constraints, and final state of tables.
- Explain behaviour (what happens to transactions when you run DDL; explain foreign key enforcement). Full marks require mentioning DBMS-dependent behaviour and one or two examples.
- Diagram/annotate a table schema with constraints; draw the rectangle and label PK/FK as described under Key ideas.
- Error explanation (why a given INSERT/DELETE failed). Full marks require pointing to the constraint and the exact check that failed.
How to write full-marks answers:
- Always state assumptions (which DBMS, whether AUTOCOMMIT is ON or OFF), show exact SQL, and, for programs, show the final table content as a result table.
- For schema-change questions, include the ALTER steps and explain the effect on existing rows.
- Name constraints when creating them (good practice and often required in answers).
Quick check
- Which SQL statement creates a new table?
- What happens if you run UPDATE Students SET year = year + 1; without a WHERE?
- True/False: A FOREIGN KEY on child table allows the parent row to be deleted if child rows exist (without cascade).
Answers: 1. CREATE TABLE. 2. Every row in Students has year incremented by 1 (every row updated). 3. False — delete is prevented (unless ON DELETE CASCADE is defined).
Finished this topic? Mark it so your progress, study plan and readiness keep up.
Stuck on something here?