DDL and DML

DDL
DDL in full is Data Definition Language where in SQL is a set of commands used to define, modify, and manage the structure of a database.DDL defines the logical schema so database engines can optimise storage, enforce constraints, and execute declarative queries efficiently.

USES
One of the core uses of is that it defines a schema where DDL is used to declare structures like tables, views, indexes and constraints.

It also declares relations for example in relational databases a table and a select are both relations as DDL sets those relation definitions and constraints.

Key Commands in DDL
DDL includes commands to create, alter, drop, truncate, and rename database objects.

1. CREATE: Used to create new database objects such as tables and views. Example: "CREATE TABLE mobile_money.transcation)"transaction_id varchar(20) NOT NULL, transaction_timestamp timestamp NULL, customer_id varchar(20) NULL, customer_name varchar(100) NULL,

1. ALTER: Used to modify the structure of an existing database object. For Example adding a new column to an existing table Example: "ALTER TABLE ADD COLUMN "

1. DROP: Used to delete existing database objects. Example: "DROP TABLE mobile_money.transactions"

1. TRUNCATE: Used to remove all records from a table, including all spaces allocated for the records, but without deleting the table structure itself. Example: "TRUNCATE TABLE mobile_money.transactions". This clears out all the rows from my transactions table while keeping the structure.

1. RENAME: Used to rename an existing database object.If you want to change the table name from transactions to something like mobile_money_logs. For example: "RENAME TABLE TO mobile_money_logs"

If you want to rename a column inside the table (for example, changing amount_kes to transaction_amount):

DML
DML stands for Data Manipulation Language and the DML commands are used to manage data within database objects. These commands affect the records in the tables only. The commands include Select, Insert, Update, Delete

1.SELECT is used to retrieve data from one or more tables.
For example: "SELECT * FROM mobile_money.transactions WHERE status = 'Success';". Running this query retrieves all columns (*) from the transactions table, but only for rows where the status column equals 'Success'.

1. INSERT: Used to add new records to a table. For example:" INSERT INTO mobile_money.transactions (transaction_id, amount_kes) VALUES ('TXN001', 500);". Running this query adds a new record (row) to the transactions table, but only fills two columns which is transaction_id and amount_kes.

1. UPDATE: Used to modify existing records in a table. For Example: "UPDATE mobile_money.transactions SET status = 'Failed' WHERE transaction_id = 'TXN001';"This query modifies existing data in the transactions table by changing the status column to 'Failed' for the specific transaction with ID 'TXN001'.

1. DELETE: Used to remove existing records from a table. For example: " DELETE FROM mobile_money.transactions WHERE transaction_id = 'TXN001';" . This query removes an entire row from the transactions table where the transaction_id equals 'TXN001'. This action is permanent.

Also one can delete data with date conditions, Example: "DELETE FROM mobile_money.transactions
WHERE transaction_timestamp < '2025-01-01';" . This deletes all transactions older than 2025.

Difference
The main difference between DDL and DML is that DDL defines and manages database structures altering the schema, while DML manipulates the data within those structures changing or having access to the data inside it.