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 understanding that the industry standard for both DDL and DML is Structured Query Language (SQL).

What is DDL?

Data Definition Language (DDL) consists of SQL statements that define, modify, and remove the structure of database objects such as tables, indexes, and constraints. DDL statements are typically executed by database administrators (DBAs) when designing or altering a database schema.

• CREATE – creates a new database object (e.g., CREATE TABLE).
• ALTER – changes the definition of an existing object (e.g., ALTER TABLE).
• DROP – removes an existing object permanently (e.g., DROP INDEX).
• TRUNCATE – quickly removes all rows from a table while preserving its structure.
• RENAME – changes the name of an existing object.

What is DML?

Data Manipulation Language (DML) comprises SQL statements used to retrieve, insert, modify, and delete the data stored within the structures defined by DDL. These statements are the core of everyday database interaction for developers and end‑users.

• SELECT – queries data from one or more tables.
• INSERT – adds new rows to a table.
• UPDATE – modifies existing rows.
• DELETE – removes rows from a table.
• MERGE – performs conditional INSERT, UPDATE, or DELETE in a single statement.

SQL – The Industry Standard

SQL (Structured Query Language) is the internationally recognised standard for both DDL and DML. It is defined by the ISO/IEC 9075 standard and is implemented (with minor vendor‑specific extensions) by all major relational database management systems (RDBMS) such as MySQL, PostgreSQL, Oracle, and Microsoft SQL Server. Because of its ubiquity, learning SQL provides a transferable skill across the industry.

Comparison of DDL and DML Statements

Typical Development Workflow

1. Design the logical schema using DDL (CREATE TABLE, ALTER TABLE).
2. Populate the database with initial data using DML (INSERT).
3. Query and verify data using DML (SELECT).
4. Iteratively refine the schema as requirements change (additional DDL).
5. Maintain data integrity through DML updates and deletions.

Key SQL Syntax (Illustrative)

Below is a simplified BNF‑style representation of the core DDL and DML syntax (presented in LaTeX for clarity):

\$\$\begin{aligned}

\text{ddl\stmt} &::= \text{CREATE}\ \text{TABLE}\ \text{id}\ (\text{col\def}\{,\text{col\_def}\}^*) \\

&\mid \text{ALTER}\ \text{TABLE}\ \text{id}\ \text{ADD}\ \text{col\_def} \\

&\mid \text{DROP}\ \text{TABLE}\ \text{id} \\

\\

\text{dml\stmt} &::= \text{SELECT}\ \text{select\list}\ \text{FROM}\ \text{id}\ \text{WHERE}\ \text{condition} \\

&\mid \text{INSERT}\ \text{INTO}\ \text{id}\ (\text{id}\{,\text{id}\}^*)\ \text{VALUES}\ (\text{value}\{,\text{value}\}^*) \\

&\mid \text{UPDATE}\ \text{id}\ \text{SET}\ \text{id}= \text{value}\{,\text{id}= \text{value}\}^*\ \text{WHERE}\ \text{condition} \\

&\mid \text{DELETE}\ \text{FROM}\ \text{id}\ \text{WHERE}\ \text{condition}

\end{aligned}\$\$

Common Pitfalls

• Confusing DDL with DML – DDL changes the *structure*; DML changes the *data*.
• Neglecting transaction control when using DML – forgetting COMMIT or ROLLBACK can leave the database in an inconsistent state.
• Over‑using TRUNCATE – it bypasses triggers and cannot be rolled back in some systems.
• Relying on vendor‑specific extensions – they reduce portability across different SQL implementations.