Introduction to DDL

Dialect note: MODIFY and the implicit-commit examples here use MySQL syntax and behaviour. TRUNCATE and identity reset behaviour differ across databases.

1. Introduction to DDL

DDL (Data Definition Language) deals with defining and modifying the structure of database objects.

Includes:

• CREATE → create a database or table
• ALTER → change an existing table structure
• DROP → delete a table or database

DDL commands are auto-committed (cannot be rolled back).

2. CREATE – Creating Tables

Example:

CREATE TABLE orders (
  order_id INT,
  order_item_id INT,
  order_date DATE,
  customer_id INT,
  product_id INT,
  quantity INT,
  product_price FLOAT,
  total_price FLOAT
);

Theory:

Defines a table with specific columns and their data types.

3. INSERT – Adding Data to the Table

INSERT INTO orders VALUES
(1, 1, '2013-07-25', 11599, 957, 1, 299.98, 299.98),
...
(5, 13, '2013-07-25', 11318, 403, 1, 129.99, 129.99);

Theory:

Adds rows to the table.

Values must match the column order.

4. ALTER – Modifying an Existing Table

ALTER is used for:

• Adding a new column
• Dropping a column
• Changing a column datatype
• Renaming a column
• Renaming a table

4.1 Add a New Column

Requirement: Add order_status column.

ALTER TABLE orders ADD COLUMN order_status VARCHAR(20);

Theory:

Adds a new column to the end of an existing table.

4.2 Insert Rows After Adding the Column

INSERT INTO orders
(order_id, order_item_id, order_date, customer_id, order_status,
 product_id, quantity, product_price, total_price)
VALUES
(7, 14, '2013-07-25', 4530, 'COMPLETE', 1073, 1, 199.99, 199.99);

If order_status is missing:

INSERT INTO orders
(order_id, order_item_id, order_date, customer_id, product_id,
 quantity, product_price, total_price)
VALUES (8, 17, '2013-07-25', 2911, 365, 3, 59.99, 179.97);

Theory:

When column names are used explicitly, order does not matter.

4.3 Drop an Existing Column

ALTER TABLE orders DROP COLUMN total_price;

Theory:

Permanently deletes a column and its data.

4.4 Change Column Datatype

Example 1: DATE → DATETIME

ALTER TABLE orders MODIFY order_date DATETIME;

Example 2: DATETIME → DATE

ALTER TABLE orders MODIFY order_date DATE;

Example 3: float → int

ALTER TABLE orders MODIFY product_price INT;

Example 4: int → varchar

ALTER TABLE orders MODIFY product_id VARCHAR(5);
ALTER TABLE orders MODIFY customer_id VARCHAR(3);

Example 5: datetime → varchar

ALTER TABLE orders MODIFY order_time VARCHAR(50);

Example 6: varchar → date

ALTER TABLE orders MODIFY order_time DATE;

Theory:

ALTER modifies column data type as long as the data is compatible.

4.5 Rename a Column

ALTER TABLE orders RENAME COLUMN order_date TO order_time;

Theory:

Renames the column without affecting existing data.

4.6 Rename a Table

ALTER TABLE orders RENAME TO orders_with_status;

Theory:

Renames the table but preserves data and structure.

5. DROP – Deleting Structure

5.1 Drop Database

DROP DATABASE retail_db;

5.2 Create Database Again

CREATE DATABASE retail_db;
USE retail_db;

Theory:

Dropping a DB removes everything inside it. Cannot be undone.

Common Mistakes to Avoid

• Using DELETE when you mean TRUNCATE: DELETE is DML and removes rows one by one (slow). TRUNCATE is DDL and wipes the entire table instantly by resetting the structure (fast).
• Accidental DROP: DROP is a DDL command. If you run it, you lose the data AND the table. Many beginners use DROP when they just wanted to DELETE some rows.
• Data Type Conflicts: If you use DDL to ALTER a column from a Number to a Date, your existing DML data might cause an error if the values do not fit the new rule.