Understand given SQL (DDL) statements and be able to write simple SQL (DDL) statements using a sub-set of statements

Published by Patrick Mutisya · 14 days ago

Cambridge A-Level Computer Science 9618 – Data Definition Language (DDL) and Data Manipulation Language (DML)

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

Learning Objective

By the end of this lesson you should be able to:

  • Interpret a range of basic SQL DDL statements.

  • Write simple SQL DDL statements using a defined sub‑set of commands.

  • Distinguish between DDL and DML and know when each is appropriate.

Key Definitions

  • DDL (Data Definition Language) – statements that define or modify the structure of database objects such as tables, indexes, and constraints.

  • DML (Data Manipulation Language) – statements that retrieve or modify the data stored in those objects.

Common DDL Statements (sub‑set)

CommandPurposeTypical Syntax (simplified)
CREATE TABLEDefine a new table and its columns.CREATE TABLE table_name ( column1 datatype, column2 datatype, … );
ALTER TABLEModify an existing table – add, drop or change columns.ALTER TABLE table_name ADD column datatype;

ALTER TABLE table_name DROP COLUMN column;

DROP TABLEDelete a table and all its data permanently.DROP TABLE table_name;
CREATE INDEXSpeed up searching on one or more columns.CREATE INDEX idxname ON tablename(column);
DROP INDEXRemove an index.DROP INDEX idx_name;

Common DML Statements (sub‑set)

CommandPurposeTypical Syntax (simplified)
INSERTAdd new rows to a table.INSERT INTO table_name (col1, col2) VALUES (val1, val2);
UPDATEModify existing rows.UPDATE tablename SET col = newvalue WHERE condition;
DELETERemove rows.DELETE FROM table_name WHERE condition;
SELECTRetrieve data.SELECT col1, col2 FROM table_name WHERE condition;

Understanding Example DDL Statements

Read each statement and answer the questions that follow.

  1. CREATE TABLE Student (

      StudentID INT PRIMARY KEY,

      FirstName \cdot ARCHAR(30) NOT NULL,

      LastName \cdot ARCHAR(30) NOT NULL,

      DOB DATE,

      EnrolDate DATE DEFAULT CURRENT_DATE

    );

    • What objects are being created?

    • Identify the primary key and any constraints.

    • What data type is used for dates?

  2. ALTER TABLE Student ADD Email \cdot ARCHAR(50) UNIQUE;

    • What change does this make to the Student table?

    • Explain the effect of the UNIQUE constraint.

  3. DROP TABLE TempResults;

    • What happens to the data stored in TempResults?

    • When might a teacher decide to use this command?

Writing Simple DDL Statements – Practice

Use the sub‑set of commands listed above to complete the following tasks. Write each statement on a separate line.

  1. Create a table called Course with the columns:

    • CourseID – integer, primary key

    • CourseName – variable character up to 100 characters, not null

    • Credits – integer, default value 3

  2. Add a column RoomNumber (type VARCHAR(10)) to the Course table.

  3. Create an index called idxcoursename on the CourseName column.

  4. Remove the index idxcoursename.

  5. Delete the entire Course table.

Key Differences Between DDL and DML

AspectDDLDML
PurposeDefine or modify database structure.Manipulate the data stored in that structure.
Typical CommandsCREATE, ALTER, DROP, TRUNCATE, RENAME.SELECT, INSERT, UPDATE, DELETE.
Transaction ControlUsually auto‑committed; cannot be rolled back in many DBMS.Can be committed or rolled back as part of a transaction.
Effect on SchemaChanges the schema (metadata).Does not change the schema.

Common Pitfalls

  • Using DROP TABLE accidentally – always double‑check the table name.

  • Forgetting to specify a primary key when creating a table – leads to duplicate rows.

  • Adding a column with NOT NULL without a default value on a populated table – will cause an error.

  • Creating an index on a column that already has a unique constraint – redundant.

Suggested Diagram

Suggested diagram: A simple ER diagram showing the Student and Course tables with a many‑to‑many relationship implemented via a junction table Enrollment.

Summary Checklist

  1. Identify whether a required statement is DDL or DML.

  2. Write the correct syntax using the appropriate sub‑set of commands.

  3. Verify constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT) are correctly placed.

  4. Remember that DDL changes are often auto‑committed.