Part 4: The 5 Core SQL Sublanguages: DDL, DML, DQL, DCL & TCL

SQL commands are categorized into five distinct sublanguages based on their functional purpose.

1. DDL: Data Definition Language

Deals with the structure and schema of database objects (tables, schemas, indexes): - CREATE: Creates new tables, views, indexes, or databases. - ALTER: Modifies an existing database structure. - DROP: Deletes an entire structure and its data. - TRUNCATE: Empties all rows from a table instantly without dropping the structure.

2. DML: Data Manipulation Language

Modifies the data inside tables: - INSERT: Adds new rows. - UPDATE: Modifies existing rows. - DELETE: Removes specific rows. - MERGE (Upsert): Inserts or updates conditionally.

3. DQL: Data Query Language

Retrieves data without modifying the underlying state: - SELECT: The primary command used to fetch and project rows and columns.

4. DCL: Data Control Language

Manages security, permissions, and access rights: - GRANT: Gives a user or role permission to perform actions. - REVOKE: Withdraws previously granted permissions.

5. TCL: Transaction Control Language

Manages transactional boundaries and atomicity: - COMMIT: Permanently saves transaction changes to the database. - ROLLBACK: Aborts the transaction and reverts all changes. - SAVEPOINT: Sets an intermediate checkpoint for partial rollback.

Key Takeaways

• DDL manages table schemas (CREATE, ALTER, DROP).
• DML modifies data (INSERT, UPDATE, DELETE).
• DQL queries data (SELECT).
• DCL secures access (GRANT, REVOKE).
• TCL controls transactions (COMMIT, ROLLBACK).

In Part 5, we will set up a local database environment with PostgreSQL, MySQL & SQLite!