DDL, DML, DQL Statements: Create, Drop, Truncate, Rename, Alter, Insert, Update, Delete, Select

📖 Theory

Three jobs, three families

Meera is about to actually run SQL. But SQL has dozens of commands, and throwing them at you as a flat list is how students drown.

There is a clean way to see them: every SQL command does one of three jobs, define the structure, change the data, or ask a question. Three families: DDL, DML, DQL.

And hidden inside is the most dangerous exam trap in the subject: three commands that all 'delete' but remove wildly different amounts. Sort the families first; the trap becomes obvious.

📖 Theory

Building, furnishing, inspecting a house

Three different jobs on a house. Building or remodelling the structure, walls, rooms: that is DDL (CREATE, ALTER, DROP). Moving furniture in and out, changing what is inside: that is DML (INSERT, UPDATE, DELETE). Walking through to see what is there: that is DQL (SELECT). Structure, contents, inspection, every SQL command is one of these three.

📊 At a glance

The three SQL families

⌨️ Practical

Build it, fill it, query it

-- DDL: define the structure
CREATE TABLE sales (
    id       INT,
    item     VARCHAR(30),
    category VARCHAR(20),
    price    INT,
    qty      INT
);

-- DML: put data in and change it
INSERT INTO sales VALUES (1, 'Sugar', 'Grocery', 45, 20);
UPDATE sales SET price = 50 WHERE item = 'Sugar';
DELETE FROM sales WHERE item = 'Sugar';

-- DQL: ask a question
SELECT * FROM sales;

This example runs in Gri-Learn on the web, where you can edit it and see the output.

📖 Theory

The three 'deletes', compared

This is the exam's favourite trap. Three commands remove things, at three different scales:

• DELETE (DML): removes chosen rows (DELETE FROM sales WHERE item='Sugar'), or all rows if no WHERE. The table stays; can be rolled back.
• TRUNCATE (DDL): removes ALL rows at once but keeps the empty table. Faster, and usually cannot be filtered or easily undone.
• DROP (DDL): removes the entire table, structure and data, gone completely.

Scale: DELETE (some rows) < TRUNCATE (all rows, keep table) < DROP (table itself).

🧩 Quiz

Meera wants to empty her sales table of all rows but KEEP the table so she can refill it tomorrow. Which command, and why not DROP?

1. TRUNCATE, it removes all rows but keeps the empty table structure
2. DROP, it keeps the table
3. DELETE one row at a time only
4. ALTER, to remove the data

💭 Think first

Which family is each?

Sort these into DDL, DML, or DQL: SELECT, CREATE, UPDATE, DROP, INSERT. Do it in your head before revealing.

⚠️ Watch out

Where marks leak

The big trap: DELETE (rows, filterable, DML) vs TRUNCATE (all rows, keep table, DDL) vs DROP (whole table gone, DDL). Misfiling commands: INSERT/UPDATE/DELETE are DML, not DDL; CREATE/ALTER/DROP/TRUNCATE are DDL; SELECT is DQL. And note ALTER changes structure (add a column), it does not change data. Getting the family of each command right is easy marks if you learned the three jobs.

📖 Theory

SELECT is where you will live

Of all these, SELECT is the one you will write ten thousand times, asking questions of data is the daily work of every analyst and backend developer. The rest of Unit 4 is almost entirely about making SELECT powerful: filtering with WHERE, sorting, grouping, combining conditions, and summarising. Next lesson: the WHERE clause and its operators, how to ask for exactly the rows you want.

✅ Summary

Key takeaways

• SQL commands fall into three families by job.
• DDL defines/changes STRUCTURE: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• DML changes DATA: INSERT (add), UPDATE (change), DELETE (remove rows).
• DQL asks questions: SELECT.
• DELETE removes chosen rows (keep table); TRUNCATE removes all rows (keep empty table); DROP removes the whole table.
• Memory hook: build the house (DDL), move furniture (DML), inspect it (DQL).