DQL vs DDL vs DML vs DCL vs TCL

Categories of commands within Structured Query Language (SQL) used for managing and interacting with relational databases

Quick memory tip: DQL queries data, DDL defines structure, DML manipulates data, DCL controls access, and TCL manages transactions.

Data Query Language (DQL)

DQL commands are used to retrieve data from the database. It is primarily used for querying and extracting information based on specified criteria.

Examples:

SELECT - Retrieve data from database

-- SELECT: Retrieve specific columns with a filter
SELECT VendorID, fare_amount, trip_distance 
FROM staging_taxi_trips
WHERE fare_amount > 50.00
ORDER BY tpep_pickup_datetime DESC;

---

Data Definition Language (DDL)

DDL commands are used to define, modify, or delete the structure of database objects. They deal with the database schema itself, not the data within it.

Examples:

• CREATE - Create database objects (tables, indexes, views, etc.)

• ALTER - Modify existing database objects

• DROP - Delete database objects

• TRUNCATE - Remove all records from a table (structure remains)

• RENAME - Rename database objects

-- CREATE: Define a new table structure
CREATE TABLE taxi_trips (
    trip_id INT PRIMARY KEY,
    vendor_name VARCHAR(50),
    total_amount DECIMAL(10,2)
);

-- ALTER: Add a new column to an existing table
ALTER TABLE taxi_trips ADD COLUMN payment_type_desc TEXT;

-- RENAME: Change the name of an object
ALTER TABLE taxi_trips RENAME TO trips_archive;

-- TRUNCATE: Wipe data but keep the table structure
TRUNCATE TABLE trips_archive;

-- DROP: Delete the entire table from the database
DROP TABLE trips_archive;

---

Data Manipulation Language (DML)

DML commands are used to manipulate the actual data stored within the database objects (tables). They allow users to insert, update, or delete data.

Examples:

• INSERT - Add new records

• UPDATE - Modify existing records

• DELETE - Remove records

• MERGE - Insert or update records based on conditions

-- INSERT: Add new data
INSERT INTO taxi_trips (trip_id, vendor_name, total_amount)
VALUES (1, 'Creative Mobile', 15.50);

-- UPDATE: Modify existing rows
UPDATE taxi_trips 
SET total_amount = 20.00 
WHERE trip_id = 1;

-- DELETE: Remove specific rows
DELETE FROM taxi_trips WHERE total_amount < 0;

-- MERGE: The "Upsert" (Update if exists, Insert if new)
MERGE INTO final_table f
USING staging_table s
ON (f.unique_row_id = s.unique_row_id)
WHEN MATCHED THEN
    UPDATE SET f.last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (unique_row_id, vendor_id) VALUES (s.unique_row_id, s.vendor_id);

---

Data Control Language (DCL)

These commands control access and permissions to the database and its objects.

Examples:

• GRANT - Give user access privileges

• REVOKE - Remove user access privileges

-- GRANT: Give a user permission to read a table
GRANT SELECT ON taxi_trips TO analyst_user;

-- REVOKE: Take back a permission
REVOKE DELETE ON taxi_trips FROM junior_dev;

---

Transaction Control Language (TCL)

TCL commands are used to manage transactions within a database. They ensure data integrity and consistency by controlling the execution of DML operations as a single, atomic unit.

Examples:

• COMMIT - Save transaction changes permanently

• ROLLBACK - Undo transaction changes

• SAVEPOINT - Set a point within a transaction to rollback to

• SET TRANSACTION - Set transaction properties

BEGIN; -- Start a transaction

-- Step 1: Deduct from one table
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- SAVEPOINT: Create a checkpoint
SAVEPOINT part_one;

-- Step 2: Add to another table
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If something goes wrong:
ROLLBACK TO part_one; 

-- If everything is perfect:
COMMIT; -- Save all changes permanently

---