Show understanding that the industry standard for both DDL and DML is Structured Query Language (SQL)

8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

Learning objective

Show that the industry‑standard language for both DDL and DML is Structured Query Language (SQL) and be able to use the core SQL statements required by the Cambridge IGCSE/A‑Level Computer Science (9618) syllabus.

1. SQL – the industry standard

  • SQL stands for Structured Query Language.

  • It is defined by the international standard ISO/IEC 9075 and is implemented (with only minor vendor‑specific extensions) by all major relational DBMSs – MySQL, PostgreSQL, Oracle, Microsoft SQL Server, SQLite, etc.

  • Because SQL is the common language for both defining database structures (DDL) and manipulating the data stored in those structures (DML), mastering it gives a transferable skill across the whole software industry.

2. Data Definition Language (DDL)

DDL statements describe, modify, or remove the structure of database objects such as tables, indexes, views and constraints. They are normally used by a database designer or DBA when the logical schema is being created or altered.

2.1 Core DDL statements

StatementPurposeTypical example
CREATEDefine a new object (table, view, index, …)CREATE TABLE Student (id INT PRIMARY KEY, name VARCHAR(50));
ALTERChange the definition of an existing objectALTER TABLE Student ADD COLUMN birthdate DATE;
DROPPermanently remove an objectDROP TABLE Student;
TRUNCATEDelete all rows quickly while keeping the table definition (usually non‑transactional)TRUNCATE TABLE Student;
RENAMEGive an existing object a new nameRENAME TABLE Student TO Learner;

2.2 Data‑integrity constraints (optional but essential for exams)

  • PRIMARY KEY – uniquely identifies each row.

  • FOREIGN KEY – enforces a link to a primary key in another table.

  • UNIQUE – guarantees no duplicate values in a column (or column set).

  • NOT NULL – forbids NULL values.

  • CHECK – restricts the range or format of values, e.g. CHECK (age >= 0).

2.3 Common SQL data types (DDL)

CategoryTypical types
NumericINTEGER, SMALLINT, BIGINT, DECIMAL(p,s), NUMERIC, FLOAT
CharacterCHAR(n), VARCHAR(n), TEXT
Date & timeDATE, TIME, TIMESTAMP
BooleanBOOLEAN (TRUE/FALSE)

3. Data Manipulation Language (DML)

DML statements operate on the data stored in the structures created by DDL. They are used daily by developers, analysts and end‑users.

3.1 Core DML statements

StatementPurposeTypical example
SELECTRetrieve rows from one or more tablesSELECT name FROM Student WHERE id = 10;
INSERTAdd new rowsINSERT INTO Student (id, name) VALUES (10, 'Alice');
UPDATEModify existing rowsUPDATE Student SET name = 'Bob' WHERE id = 10;
DELETERemove rowsDELETE FROM Student WHERE id = 10;
MERGEConditionally INSERT, UPDATE or DELETE in a single statement (supported by many commercial DBMSs)MERGE INTO Target t USING Source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.val = s.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

3.2 Transaction control (good DML practice)

  • COMMIT – permanently saves all changes made in the current transaction.

  • ROLLBACK – discards all changes made in the current transaction.

  • In exam questions you may be asked to decide whether a series of DML statements should be committed or rolled back.

4. Typical development workflow (DDL ↔ DML)

  1. Design the logical schema (ER diagram, normalization). Implement it with DDL – CREATE TABLE, ALTER TABLE, constraints.

  2. Populate the database using DML – INSERT statements.

  3. Verify the design by running SELECT queries and checking that constraints work.

  4. Iterate – as requirements change, use ALTER or DROP to refine the schema.

  5. Maintain the data with regular DML operations, always ending a logical unit of work with COMMIT (or ROLLBACK on error).

5. Mini‑project: a complete DDL/DML script (run on any standard SQL platform)

-- 1️⃣  Create the table (DDL)
CREATE TABLE Students (
student_id   INTEGER PRIMARY KEY,
first_name   VARCHAR(30) NOT NULL,
last_name    VARCHAR(30) NOT NULL,
enrolyear   INTEGER CHECK (enrolyear >= 2000)
);
-- 2️⃣  Add a column (DDL)
ALTER TABLE Students
ADD COLUMN birthdate DATE;
-- 3️⃣  Insert sample rows (DML)
INSERT INTO Students (studentid, firstname, lastname, enrolyear, birthdate)
VALUES
(1, 'Alice',   'Brown', 2022, '2004-05-12'),
(2, 'Bob',     'Smith', 2021, '2003-11-23'),
(3, 'Charlie', 'Lee',   2023, '2005-02-07');
-- 4️⃣  Retrieve data (DML)
SELECT studentid, firstname, lastname, enrolyear
FROM Students
WHERE enrol_year >= 2022;
-- 5️⃣  Update a row (DML)
UPDATE Students
SET last_name = 'Green'
WHERE student_id = 2;
-- 6️⃣  Delete a row (DML)
DELETE FROM Students
WHERE student_id = 3;
-- 7️⃣  Remove all rows but keep the definition (DDL)
TRUNCATE TABLE Students;
-- 8️⃣  Drop the table when finished (DDL)
DROP TABLE Students;

6. Compact BNF‑style syntax (illustrative only)

The following notation shows the essential structure of the core DDL and DML statements. It is deliberately concise – the full SQL grammar is far larger.

ddlstmt ::= CREATE TABLE id '(' coldef {',' col_def}* ')'
| ALTER TABLE id ADD COLUMN col_def
| DROP TABLE id
| TRUNCATE TABLE id
| RENAME TABLE id TO id
dmlstmt ::= SELECT selectlist FROM id [WHERE condition]
| INSERT INTO id '(' id {',' id}* ')' VALUES '(' value {',' value}* ')'
| UPDATE id SET id = value {',' id = value}* [WHERE condition]
| DELETE FROM id [WHERE condition]
| COMMIT | ROLLBACK

7. Common pitfalls (exam‑style reminders)

  • Confusing DDL with DML – DDL changes the *structure*; DML changes the *data*.

  • Omitting transaction control – forgetting COMMIT can leave changes invisible; forgetting ROLLBACK after an error may corrupt the database.

  • Using TRUNCATE indiscriminately – it cannot be rolled back in many DBMSs and bypasses triggers.

  • Relying on vendor‑specific extensions – reduces portability; stick to the standard forms required by the syllabus.

  • Neglecting constraints – without PRIMARY KEY, FOREIGN KEY, NOT NULL, etc., the DBMS cannot enforce data integrity.

8. Link to broader database concepts (Unit 8.1 & 8.2)

  • Entity‑relationship modelling and normalization (1NF‑3NF) produce the logical schema that DDL implements.

  • Physical design (indexes, storage parameters) is handled by the DBMS after the DDL statements are executed.

  • Interpreting given SQL statements – a common Cambridge exam task – draws directly on the DDL/DML knowledge presented here.

Suggested diagram: a flowchart contrasting the lifecycle of DDL (schema design → ALTER → DROP) with the lifecycle of DML (INSERT → SELECT → UPDATE → DELETE → COMMIT/ROLLBACK).