CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Sort four statements (15 minutes)

Classify each as DDL, DML, DCL or TCL: DROP TABLE reviews;, SELECT * FROM books;, REVOKE SELECT ON orders FROM intern_role;, ROLLBACK;.

Done when: all four are correctly classified.

Hint

DROP TABLE is DDL, SELECT is DML, REVOKE is DCL, ROLLBACK is TCL.

Easy 2: DDL versus DML in one sentence (15 minutes)

Explain, in one sentence, the difference between DDL and DML.

Done when: the sentence correctly contrasts structure versus row data.

Hint

DDL changes what a table looks like; DML changes or reads what is inside it.

Easy 3: Where COMMIT fits (15 minutes)

State which category COMMIT belongs to, and why it is not DML even though it is essential to making data changes permanent.

Done when: TCL is correctly named, with the reasoning that COMMIT marks a boundary, not a data change itself.

Hint

COMMIT finalizes a transaction boundary; it does not itself read or write a row's value.

Medium

Medium 1: Write one of each (25 minutes)

Write one example statement for each of the four categories, using the bookstore schema.

Done when: four correct statements are given, one per category, each using a real table or column from the bookstore schema.

Hint

For example, CREATE TABLE (DDL), INSERT INTO books ... (DML), GRANT ... (DCL), BEGIN; (TCL).

Medium 2: A statement that could look like two categories (25 minutes)

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); adds a rule about data. Explain why it is still DDL, not DML.

Done when: the explanation correctly states that a constraint is part of the table's structure, not a row value.

Hint

A foreign key constraint is a rule attached to the table's structure, enforced on every future row, not a value stored in any one row.

Medium 3: Permissions without data access (25 minutes)

Explain what GRANT SELECT ON orders TO reporting_role; actually changes. Confirm that running it does not read or modify any row in orders.

Done when: the explanation correctly states it changes a permission, not any row, confirming it as DCL.

Hint

It changes the system's record of who may run SELECT against orders; it touches no row of orders itself.

Hard

Hard 1: A migration script with all four categories (30 minutes)

A realistic deployment script adds a new table, inserts some seed data into it, grants a service account read access, and wraps the whole thing in a transaction. Identify which category each step belongs to.

Done when: all four categories are represented and correctly matched to a specific step in the described script.

Hint

CREATE TABLE is DDL, the seed INSERTs are DML, the GRANT is DCL, and BEGIN/COMMIT around the whole script is TCL.

Hard 2: Why TCL needs to exist as its own category (35 minutes)

BEGIN and COMMIT exist entirely to make DML statements take effect correctly. Explain why they are still not grouped under DML.

Done when: the explanation correctly separates "controls when changes take effect" (TCL) from "performs the changes" (DML).

Hint

TCL governs when a group of DML statements becomes permanent or is undone; DML governs what those statements actually do to the data.

Hard 3: Classify a statement with mixed effects (40 minutes)

Some databases support TRUNCATE TABLE, which removes every row from a table extremely fast. Many systems classify it as DDL rather than DML, because of how it is implemented internally. Explain why this classification can make sense, even though it looks row-focused on the surface.

Done when: the explanation correctly notes that TRUNCATE is often implemented by deallocating the table's storage and rebuilding it empty, which is a structural operation, not a row-by-row delete.

Hint

Rebuilding an empty table from scratch is a structural change to how the table is stored. It is closer in nature to DROP and re-CREATE than to a row-by-row DELETE.