A3.3.1 · Database programming

The two data language types within SQL

Building a shelf is not the same job as putting books on it, SQL keeps those two jobs in two separate language types, and knowing which is which is the whole of this statement.

Core for SL & HL Command term: outline Builds on A3.2.4

Explainer

SQL isn't one single kind of instruction, it's really two different jobs wearing the same syntax. Some SQL statements shape the database itself, what tables exist, what columns they have. Other SQL statements never touch that shape at all, they only work with the data sitting inside it. Mixing the two up is easy, since both can sound like "changing" something.

Data definition language (DDL) is the half of SQL that never touches a single row of data. Every DDL statement is really doing one of three things to a container, tables, views, indexes, constraints: bringing it into existence, reshaping it, or getting rid of it entirely.

Data manipulation language (DML) is everything else, the half that never redraws the container but does whatever it wants with what's inside it: reading rows back out, adding new ones, changing what's there, or taking rows away. The table it's working in has to already exist, built by DDL first.

DDL statements

Statement	What it does
CREATE	Creates a new database object: a table, a view, an index.
PRIMARY KEY	Sets a field as a table's primary key.
FOREIGN KEY ... REFERENCES ...	Sets a field as a foreign key, naming the table and field it references.
ALTER	Reshapes a table that already has data in it: a column can be added or dropped, a constraint tightened, all without touching the rows already stored.
DROP	Deletes a database object entirely: a table, an index, a view.

DML statements

Statement	What it does
SELECT	Reads rows back out, from a single table or several joined together.
INSERT	Adds a brand-new row where none existed before.
UPDATE	Changes the values sitting inside a row that is already there.
DELETE	Takes a row out of the table entirely.

Worked example

An animal shelter's PET table: shape it, then fill it

Click through and watch DDL build the table's structure first, then DML take over and work entirely inside it.

Step 1 of 6 DDL

Check your understanding

Which statement is DDL: ALTER TABLE PET ADD COLUMN Adopted BOOLEAN; or UPDATE PET SET Adopted = TRUE WHERE PetID = 4;?

Common mistake

Markers reported this pattern for A3.3.1

RecurringBoth DDL and DML statements can sound like "changing" the database in everyday language, ALTER changes a table, UPDATE changes a record, and candidates often classify by that word alone. The real test is WHAT is changing: the structure itself (DDL), or a value already living inside an unchanged structure (DML).

What a weak answer looks like: "UPDATE is DDL because it changes the database." True that it changes something, but it changes a stored VALUE, not the table's structure, that makes it DML.

✓

What it should say: "UPDATE is DML: it modifies a value already stored in an existing row, the table's columns and structure stay exactly as they were before and after." Names the type AND explains why by pointing at what actually changed.

Linking question

How does database programming in SQL differ from programming computationally in a high-level language?

SQL statements like CREATE, SELECT or UPDATE describe WHAT result is wanted, the DBMS works out how to do it. A high-level language like Python or Java describes HOW to get there, step by step, in an explicit sequence the programmer controls directly. DDL and DML are exactly this declarative style in action, no loop or condition needed to add a record or change a table.

Does database programming in SQL require computational thinking?

Yes, just not in the step-by-step form B2's own programming statements teach it in. Writing a correct query still means decomposing a request into the right combination of clauses, recognising the same filtering or grouping pattern across different questions, treating a table as an abstraction over whatever rows actually exist in it, and sequencing SELECT/FROM/WHERE/GROUP BY/HAVING correctly to reach a working result. The thinking is the same four skills, decomposition, pattern recognition, abstraction, algorithm design, expressed declaratively instead of imperatively.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Outline the difference between DDL and DML.

Question 2 · 3 marks

[3]State whether each of the following is DDL or DML, with a reason: DROP TABLE Pet;, INSERT INTO Pet VALUES (5, 'Milo', 'Cat', 2);, CREATE INDEX idx_species ON Pet (Species);

Question 3 · 4 marks

[4]A developer writes ALTER TABLE Pet ADD FOREIGN KEY (ShelterID) REFERENCES Shelter (ShelterID);. Explain why this is DDL rather than DML, and outline what would happen if a matching ShelterID didn't exist.

Flashcards

Active recall for this statement's key terms.