SQL Sublanguages: DDL, DML, DQL, DCL, and TCL

When you work on an e-commerce platform, different team members touch the database in very different ways. A backend engineer creates the orders table. A data analyst queries it. An admin controls who can see the payments table. Each of these actions belongs to a different category of SQL. Knowing which category a command falls into helps you understand what it does, whether it can be undone, and when you are allowed to run it.

DDL: Data Definition Language

DDL commands define and modify the structure of your database. They operate on objects like tables, indexes, and schemas, not on the rows inside them.

Common DDL commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.

-- Create a new table to track product reviews
CREATE TABLE product_reviews (
    review_id   INTEGER PRIMARY KEY,
    product_id  INTEGER,
    customer_id INTEGER,
    rating      INTEGER,
    review_text TEXT,
    created_at  TEXT
);

-- Add a new column to the products table
ALTER TABLE products ADD COLUMN weight_kg REAL;

-- Remove the table entirely
DROP TABLE product_reviews;

TRUNCATE removes all rows from a table but keeps the structure intact. SQLite does not support TRUNCATE directly. You use DELETE FROM table_name; without a WHERE clause to achieve the same result.

DML: Data Manipulation Language

DML commands change the data inside tables. They do not touch the table structure itself.

Common DML commands: INSERT, UPDATE, DELETE, MERGE.

-- Insert a new customer
INSERT INTO customers (customer_id, first_name, last_name, email, city, country, created_at)
VALUES (1001, 'Priya', 'Sharma', 'priya@example.com', 'Bangalore', 'India', '2024-01-15');

-- Update a customer's loyalty tier
UPDATE customers
SET loyalty_tier = 'Gold'
WHERE customer_id = 1001;

-- Delete orders that were cancelled and have no payments linked
DELETE FROM orders
WHERE status = 'Cancelled'
  AND order_id NOT IN (SELECT order_id FROM payments);

DML changes can be rolled back if they are wrapped in a transaction. This is one reason DML is safer to run experimentally than DDL, as long as you start a transaction first.

DQL: Data Query Language

DQL is what most analysts spend the majority of their time writing. It reads data without changing anything.

The only command here is SELECT.

-- Find all customers from India with Gold loyalty tier
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
WHERE c.country = 'India'
  AND c.loyalty_tier = 'Gold';

-- Summarise total revenue by order status
SELECT o.status, SUM(o.total_amount) AS total_revenue
FROM orders o
GROUP BY o.status
ORDER BY total_revenue DESC;

SELECT never modifies data. You can run a SELECT on a live production database and nothing changes. That makes it the safest command to experiment with.

DCL: Data Control Language

DCL controls who can do what inside a database. You will not run these commands as an analyst day to day, but you will encounter them when setting up database users or requesting access.

Common DCL commands: GRANT, REVOKE.

-- Grant read access to the orders table for a reporting user
GRANT SELECT ON orders TO reporting_user;

-- Revoke that access
REVOKE SELECT ON orders FROM reporting_user;

SQLite has a minimal permission model and does not fully support GRANT and REVOKE. These commands matter most in MySQL, PostgreSQL, and MS SQL Server. You will still see them in job interviews and system design discussions.

TCL: Transaction Control Language

TCL manages transactions. A transaction is a group of SQL statements that should either all succeed or all fail together.

Common TCL commands: COMMIT, ROLLBACK, SAVEPOINT.

-- Start a transaction, make changes, then commit
BEGIN;

UPDATE orders
SET status = 'Shipped'
WHERE order_id = 5021;

UPDATE products
SET stock_qty = stock_qty - 1
WHERE product_id = 44;

COMMIT;

-- Roll back if something goes wrong
BEGIN;

DELETE FROM payments WHERE order_id = 5021;

-- Realised this was a mistake
ROLLBACK;

If you COMMIT, the changes are permanent. If you ROLLBACK, everything since the last BEGIN is undone. SAVEPOINT lets you mark a point mid-transaction to roll back to without undoing everything.

Common Mistakes

-- wrong
DROP TABLE orders;
ROLLBACK;

-- correct
-- Verify before dropping; DDL cannot be rolled back in most databases
SELECT COUNT(*) FROM orders;
-- Only drop if you are certain
DROP TABLE orders;

In most databases, DROP auto-commits. A ROLLBACK after it does nothing.

---

-- wrong
SELECT * FROM customers
UPDATE customers SET loyalty_tier = 'Silver' WHERE customer_id = 5;

-- correct
SELECT * FROM customers WHERE customer_id = 5;
UPDATE customers SET loyalty_tier = 'Silver' WHERE customer_id = 5;

Running a SELECT and an UPDATE as one block without a semicolon separator causes a syntax error. Each statement needs its own terminator.

---

-- wrong
DELETE FROM order_items;
-- Expecting this to delete the table structure too

-- correct
DELETE FROM order_items;   -- removes all rows, table still exists
DROP TABLE order_items;    -- removes the table entirely

DELETE is DML and only removes rows. DROP is DDL and removes the table itself. They are not interchangeable.

Quick Recap

• SQL has five sublanguages: DDL (structure), DML (data changes), DQL (querying), DCL (access control), and TCL (transactions).
• DDL commands like DROP and TRUNCATE are usually auto-committed and cannot be rolled back, so run them with care.
• As an analyst, you will mostly write DQL (SELECT) and occasionally DML (INSERT, UPDATE, DELETE) wrapped in transactions for safety.

SELECT o.order_id, o.status, o.total_amount FROM orders o WHERE o.status = 'Delivered' ORDER BY o.total_amount DESC;

BEGIN; UPDATE customers SET loyalty_tier = 'Platinum' WHERE country = 'India' AND loyalty_tier = 'Gold'; COMMIT;

SELECT p.product_id, p.product_name, p.price FROM products p WHERE p.product_id NOT IN (SELECT DISTINCT oi.product_id FROM order_items oi) ORDER BY p.product_id;