SQL Command Categories

SQL statements are grouped into five categories by purpose.

Concept

SQL statements are grouped into five categories by purpose.

Data Definition Language Define and change database structure (schema objects)

CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT Data Manipulation Language Change data inside tables INSERT, UPDATE, DELETE, MERGE Data Query Language Data Control Language Transaction Control Language

COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION Some textbooks put SELECT inside DML, since it "manipulates" data by reading it. Both classifications are accepted in interviews; mention both.

SQL
     ┌──────────┬───────────┼───────────┬──────────┐
    DDL        DML         DQL         DCL        TCL
 (structure) (data change) (query)  (permissions) (transactions)
  CREATE     INSERT       SELECT     GRANT      COMMIT
  ALTER      UPDATE                  REVOKE     ROLLBACK
  DROP       DELETE                             SAVEPOINT
  TRUNCATE   MERGE                              (BEGIN / START TRANSACTION)
  RENAME
  (COMMENT)

Why It Matters

Knowing the category tells you the side effects of a statement: DDL changes structure (and in some databases auto-commits); DML changes data (and can be rolled back inside a transaction).

Permissions are often granted per category (e.g., analysts get DQL only).

Interviewers often ask "Is TRUNCATE DDL or DML?" (DDL) and "Can DDL be rolled back?" (depends on the DBMS).

Syntax

DDL — Data Definition Language DML — Data Manipulation Language

MySQL and SQLite equivalents of MERGE ("upsert"):

DQL — Data Query Language DCL — Data Control Language TCL — Transaction Control Language

-- CREATE: make a new object
CREATE TABLE suppliers (
    supplier_id  INT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL
);

-- ALTER: change an existing object
ALTER TABLE suppliers ADD phone VARCHAR(20);

-- DROP: remove an object entirely (structure + data)
DROP TABLE suppliers;

-- TRUNCATE: remove all rows, keep structure
TRUNCATE TABLE audit_log;

-- RENAME (syntax varies by DBMS)
ALTER TABLE suppliers RENAME TO vendors;      -- PostgreSQL, SQLite, Oracle, MySQL
RENAME TABLE suppliers TO vendors;            -- MySQL
EXEC sp_rename 'suppliers', 'vendors';        -- SQL Server

-- INSERT: add rows
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (107, 'Keyboard', 'Electronics', 1500.00, 80);

-- UPDATE: change existing rows
UPDATE products
SET price = 1400.00
WHERE product_id = 107;

-- DELETE: remove rows
DELETE FROM products
WHERE product_id = 107;

-- MERGE: insert or update depending on whether a match exists (SQL:2003)
-- Supported: SQL Server, Oracle, PostgreSQL 15+, Db2. Not supported in MySQL/SQLite.
MERGE INTO products AS t
USING (VALUES (102, 'Mouse', 'Electronics', 750.00, 250)) AS s(product_id, product_name, category, price, stock)
ON t.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET price = s.price, stock = s.stock
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock)
    VALUES (s.product_id, s.product_name, s.category, s.price, s.stock);

-- MySQL
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (102, 'Mouse', 'Electronics', 750.00, 250)
ON DUPLICATE KEY UPDATE price = VALUES(price), stock = VALUES(stock);
-- (MySQL 8.0.20+ prefers an alias:  ... VALUES (...) AS new
--  ON DUPLICATE KEY UPDATE price = new.price, stock = new.stock;)

-- PostgreSQL and SQLite (3.24+)
INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (102, 'Mouse', 'Electronics', 750.00, 250)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price, stock = EXCLUDED.stock;

SELECT product_name, price
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

GRANT SELECT, INSERT ON orders TO sales_app;
REVOKE INSERT ON orders FROM sales_app;

BEGIN;                          -- PostgreSQL, SQLite (START TRANSACTION in MySQL; BEGIN TRANSACTION in SQL Server)
UPDATE products SET stock = stock - 1 WHERE product_id = 101;
SAVEPOINT after_stock;
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (1007, 5, DATE '2026-03-15', 'PENDING', 75000.00);
ROLLBACK TO SAVEPOINT after_stock;   -- undo only the INSERT
COMMIT;                              -- make the stock change permanent

Example

DELETE vs TRUNCATE vs DROP (in detail) These three are among the most frequently asked interview questions.

Selected rows (WHERE) or all rows Entire table: rows, structure, indexes, constraints, triggers, privileges Table structure afterwards Speed on large tables Slow (row-by-row, logs each row) Very fast (deallocates data pages) Every deleted row is logged Minimal (page deallocation logged) Fires DELETE triggers

No (row triggers do not fire; PostgreSQL has TRUNCATE triggers) Resets identity / auto-increment

Yes in MySQL, SQL Server; PostgreSQL only with RESTART IDENTITY Yes (inside a transaction)

PostgreSQL & SQL Server: yes inside a transaction. MySQL & Oracle: no (implicit commit)

PostgreSQL & SQL Server: yes inside a transaction. MySQL & Oracle: no (Oracle has FLASHBACK / recycle bin) Works if referenced by a foreign key Yes (if no child rows violate)

Usually blocked (PostgreSQL needs CASCADE; SQL Server forbids)

Blocked unless constraints dropped or CASCADE used Table lock (schema lock) Not immediately (space reused later) Remove specific records Empty staging/log tables Remove the object permanently

Mnemonic: DELETE removes some or all rows; TRUNCATE removes all rows; DROP removes the table.

-- DELETE: remove selected rows (or all rows if no WHERE)
DELETE FROM order_items WHERE order_id = 1004;
DELETE FROM audit_log;                -- removes all rows, one by one

-- TRUNCATE: remove all rows quickly, keep the table
TRUNCATE TABLE audit_log;

-- DROP: remove the table itself
DROP TABLE audit_log;

Expected Result

After running the TCL example above, products.stock for product 101 is 14 (reduced by 1) and order 1007 does not exist, because the INSERT was rolled back to the savepoint before COMMIT.

Common Mistakes

Running DELETE FROM table; without WHERE and deleting everything. Always write the WHERE first, or run a SELECT with the same WHERE before deleting.

Assuming TRUNCATE can be rolled back everywhere. In MySQL and Oracle it commits immediately.

Assuming DDL is transactional. In MySQL and Oracle, CREATE/ALTER/DROP cause an implicit commit of the current transaction. PostgreSQL and SQL Server support transactional DDL.

Using MERGE in MySQL (not supported); use INSERT ... ON DUPLICATE KEY UPDATE.

Expecting DELETE to reset an auto-increment counter.

Best Practices

Before UPDATE or DELETE, run the equivalent SELECT to confirm affected rows.

Wrap destructive DML in a transaction so you can roll back.

Restrict DDL and DCL privileges to administrators and migration tools.

Manage schema changes with versioned migration tools (Flyway, Liquibase, Alembic, Prisma Migrate) instead of ad-hoc DDL in production.

Interview Relevance

Guaranteed questions: "Categories of SQL commands?", "DELETE vs TRUNCATE vs DROP?", "Is TRUNCATE DDL or DML?", "Can TRUNCATE be rolled back?", "What is MERGE / upsert?".

Practice Questions

Classify each statement: GRANT, TRUNCATE, MERGE, SAVEPOINT, ALTER, SELECT.

You need to empty a 50-million-row staging table every night. Which command do you use and why?

Write a statement that deletes only cancelled orders.

Why might TRUNCATE TABLE customers; fail even though the table exists?

Write an upsert for the products table in PostgreSQL that sets stock to 50 for product 104, inserting it if missing.