SQL Command Categories

SQL statements fall into five groups by what they act on. The grouping is not trivia. It tells you what a statement can break, whether it can be undone, and which privileges it needs.

SQL
 +-- DDL  CREATE  ALTER  DROP  TRUNCATE        shape of the data
 +-- DML  INSERT  UPDATE  DELETE               contents of the data
 +-- DQL  SELECT                               reading it
 +-- DCL  GRANT  REVOKE                        who is allowed what
 +-- TCL  BEGIN  COMMIT  ROLLBACK  SAVEPOINT   grouping changes together

Some textbooks fold SELECT into DML and call it a day. Treat it separately here, because a SELECT never changes data and everything else can.

DDL: defining structure#

Data Definition Language creates and changes the objects that hold data. You will use a scratch table so nothing in the course schema is harmed.

CREATE TABLE scratch_notes (
    note_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    body       TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Changing a table later is ALTER TABLE:

ALTER TABLE scratch_notes ADD COLUMN pinned BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE scratch_notes RENAME COLUMN body TO content;
ALTER TABLE scratch_notes ALTER COLUMN content TYPE VARCHAR(500);
ALTER TABLE scratch_notes DROP COLUMN pinned;

Each of these looks harmless and none of them is free. ALTER TABLE takes a heavy lock, and some forms rewrite the entire table. Adding a column with a constant default is a metadata-only change in PostgreSQL 11 and later. Changing a column's type usually rewrites every row while blocking other sessions. On a ten-row table nobody notices. On a hundred-million-row table in production, it is an outage.

A PostgreSQL detail worth knowing: DDL is transactional. You can BEGIN, drop a table, look around, and ROLLBACK. MySQL commits DDL immediately and cannot undo it.

DML: changing contents#

INSERT adds rows. RETURNING hands back what was written, which saves a second query to learn the generated ID.

INSERT INTO scratch_notes (content)
VALUES ('first'), ('second')
RETURNING note_id, content;

 note_id | content
---------+---------
       1 | first
       2 | second
(2 rows)

INSERT 0 2

You can also insert the result of a query:

INSERT INTO scratch_notes (content)
SELECT name FROM products WHERE category = 'Office';

UPDATE changes existing rows, and DELETE removes them:

UPDATE scratch_notes SET content = 'first (edited)' WHERE note_id = 1;
DELETE FROM scratch_notes WHERE note_id = 2;

Both report how many rows they touched (UPDATE 1, DELETE 1). Read that number. If you expected one row and see UPDATE 4812, you have a problem and the number is your only warning.

DQL: reading#

SELECT is the one statement you will write more than all the others combined.

SELECT note_id, content FROM scratch_notes ORDER BY note_id;

It gets its own two lessons because there is so much in it. For now, notice it does not belong with the others: it needs only read access and cannot damage anything.

DELETE, TRUNCATE and DROP are not the same#

Beginners treat these as three ways to "clear a table". They differ in scope, speed and side effects.

Under the hood, DELETE marks each row version as dead and leaves cleanup to autovacuum later. TRUNCATE throws away the table's storage files wholesale, which is why it is fast and why it needs an ACCESS EXCLUSIVE lock that blocks every other session.

TRUNCATE scratch_notes RESTART IDENTITY;

A table referenced by a foreign key refuses TRUNCATE unless you add CASCADE, which also empties every table that references it. Read that sentence twice before typing it on a real system.

DCL: permissions#

Data Control Language decides who can do what.

GRANT SELECT ON customers TO reporting_user;
REVOKE INSERT ON customers FROM reporting_user;

Module 6 covers roles, least privilege and why your application should never connect as a superuser. For now, remember that permissions are statements too, and can be rolled back in PostgreSQL like any other.

TCL: grouping changes#

Transaction Control Language marks the edges of a unit of work.

BEGIN;
UPDATE scratch_notes SET content = 'draft' WHERE note_id = 1;
SAVEPOINT before_delete;
DELETE FROM scratch_notes;
ROLLBACK TO SAVEPOINT before_delete;   -- undo only the delete
COMMIT;                                 -- keep the update

Without BEGIN, each statement runs in its own tiny transaction and commits on success. That is autocommit mode, and it is the default in psql. Module 5 covers transactions properly.

The safe-edit habit#

Almost every "I deleted the whole table" story starts with a statement missing its WHERE. The cure is a routine, not willpower.

1. Write the SELECT first and confirm it returns exactly the rows you intend to change.
2. Open a transaction with BEGIN.
3. Replace SELECT ... with UPDATE ... SET or DELETE, keeping the identical WHERE.
4. Check the reported row count.
5. COMMIT if it matches, ROLLBACK if it does not.

BEGIN;
UPDATE products SET unit_price = unit_price * 1.10 WHERE category = 'Office';
-- UPDATE 2   (Desk Lamp and Cable Organizer, as expected)
ROLLBACK;

Common mistakes#

UPDATE or DELETE with no WHERE. The statement is valid. It simply applies to every row.

Using DELETE FROM big_table to empty it. It works and is slow. It also leaves bloat. TRUNCATE is the tool, if you are sure.

Believing ALTER TABLE is instant. Test it on production-sized data before you run it in production.

Assuming DDL cannot be rolled back. That is true in MySQL and Oracle. It is false in PostgreSQL, and the difference changes how you can safely run migrations.

Forgetting that TRUNCATE does not fire DELETE triggers. Audit tables never see the rows that vanished.

Practice#

1. Create scratch_notes, insert three rows with RETURNING, add a pinned column, then drop it. Write down which of these statements are DDL and which are DML.
2. Inside a transaction, DELETE FROM scratch_notes, run SELECT COUNT(*) to confirm it is empty, then ROLLBACK and count again.
3. Run TRUNCATE scratch_notes RESTART IDENTITY;, insert one row, and check its note_id. Repeat without RESTART IDENTITY and compare.
4. Try TRUNCATE orders; on the course schema. Read the error and explain which constraint is protecting you.