What is SQL

SQL is a declarative query language, not a procedural one

SQL (Structured Query Language) is the standard language for relational databases. Its defining trait is that it is declarative: you state what result you want, and the database's query optimizer decides how to get it (which index, which join algorithm, what order). You write SELECT … WHERE …; you do not write the loops. Contrast a procedural language where you'd code the scan and the join yourself. (Modern SQL with recursive CTEs is surprisingly powerful, but day-to-day it is a domain-specific query language, not a general-purpose programming language.)

The five sub-languages — with the categories correct

Category	Purpose	Statements
DDL — Data Definition	define/alter structure	CREATE, ALTER, DROP, TRUNCATE
DML — Data Manipulation	change rows	INSERT, UPDATE, DELETE
DQL — Data Query	read rows	SELECT
DCL — Data Control	permissions	GRANT, REVOKE
TCL — Transaction Control	transaction boundaries	COMMIT, ROLLBACK, SAVEPOINT

Correction: granting and revoking privileges is DCL (GRANT/REVOKE). TCL is about transactions — COMMIT, ROLLBACK, SAVEPOINT. The two are often swapped; they are not the same.

Why the split matters

DDL and TRUNCATE behave differently depending on the database engine. In MySQL and Oracle, DDL statements trigger an implicit commit (auto-commit), meaning they cannot be rolled back if they are executed inside a transaction. In PostgreSQL, however, DDL and TRUNCATE are fully transactional; they can be run inside a transaction block and safely rolled back using ROLLBACK. Knowing your database engine's DDL transaction model is critical to avoiding accidental schema corruption. This connects directly to the Transactions & ACID lesson.

Takeaways

• SQL is declarative: you specify the result; the optimizer plans the execution.
• DCL = GRANT/REVOKE (permissions); TCL = COMMIT/ROLLBACK/SAVEPOINT (transactions). Don't conflate them.
• DDL typically auto-commits; DML is transactional.

---

Re-authored for correctness for this guide (the prior version defined TCL as grant/revoke, which is DCL). Per the ISO SQL standard & MySQL/PostgreSQL docs. See also: Transactions & ACID, TRUNCATE, CREATE.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — What is SQL

Why this concept exists (judgment chain)

SQL is declarative: you state the result shape; the optimizer chooses access paths. The five sublanguages (DDL/DML/DQL/DCL/TCL) matter because they have different transactional semantics — especially DDL auto-commit vs transactional DDL — and mixing DCL with TCL is a common interview fail.

Worked example with numbers or traced steps

Categories:
  DDL: CREATE/ALTER/DROP/TRUNCATE
  DML: INSERT/UPDATE/DELETE
  DQL: SELECT
  DCL: GRANT/REVOKE  (permissions — not transactions)
  TCL: COMMIT/ROLLBACK/SAVEPOINT
Postgres transactional DDL (concrete):
  BEGIN;
  CREATE TABLE t(id int);
  -- SELECT * FROM t;  -- works inside this txn
  ROLLBACK;
  -- after ROLLBACK: relation "t" does not exist
MySQL/Oracle: CREATE/ALTER typically implicit COMMIT — mid-migration ROLLBACK fails.
Takeaway: “DDL is always transactional” is false; check the engine.

When NOT to use / named alternative

Do not write procedural loops in the app for set-oriented filters the optimizer can plan. Do not rely on transactional DDL in MySQL. Do not grant via ad-hoc superuser sessions — use DCL in migrations with least privilege. Prefer SQL for set queries; prefer application code for multi-service orchestration.

Failure / ops fingerprint

Fingerprint: migration scripts that cannot ROLLBACK after ALTER on MySQL; GRANT mistaken as “transaction control”; TRUNCATE used thinking it is DELETE-with-rollback everywhere. Ops: document engine DDL semantics in runbooks; wrap PG migrations in transactions; avoid TRUNCATE when soft-delete audit is required.

Hostile-panel drills (defend the decision)

Q1. DCL vs TCL — one sentence each.
Model answer: DCL controls privileges (GRANT/REVOKE). TCL controls transaction boundaries (COMMIT/ROLLBACK/SAVEPOINT).

Q2. Is TRUNCATE transactional?
Model answer: In PostgreSQL, yes (can roll back). In MySQL, typically DDL-like and auto-commits — treat as non-rollbackable.

Q3. Why is SQL called declarative?
Model answer: You specify what rows/columns; the planner picks indexes, join order, and algorithms. Procedural code would hard-code the access path.

I just read a lesson on **What is SQL** (Databases) and want to truly understand it. Explain What is SQL from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **What is SQL** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **What is SQL** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **What is SQL** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.