Creating and changing data (DDL and DML)

Testers change data all the time. Test setup inserts rows, a test moves an order into a special state, and cleanup deletes what the test made. This topic covers the statements that create and change tables and the ones that change rows, plus the habits that keep you from damaging a shared database. Interviewers ask about it because "DELETE, TRUNCATE or DROP?" is one of the most common database questions, and because a careless UPDATE in a test environment is a real risk. Every example starts from a fresh copy of the company data and ends with a SELECT, so you can see the effect.

DDL and DML

DDL (data definition language) changes structure: CREATE, ALTER and DROP. DML (data manipulation language) changes rows: INSERT, UPDATE and DELETE. SELECT is often counted as DML too, or called DQL (data query language). Two smaller groups complete the picture: DCL for permissions (GRANT, REVOKE) and TCL for transactions (COMMIT, ROLLBACK).

CREATE TABLE and INSERT

A table definition lists each column with a type and optional constraints (rules such as NOT NULL). Name the columns in every INSERT, so the statement still works if columns are added or reordered later.

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)
);
INSERT INTO projects (id, name, dept_id) VALUES
  (1, 'Checkout revamp', 1),
  (2, 'Test automation', 2);
SELECT p.name, d.name AS department FROM projects p JOIN departments d ON d.id = p.dept_id;

name | department
Checkout revamp | Engineering
Test automation | QA

PRIMARY KEY marks the column whose value identifies each row. REFERENCES departments(id) says the value must be the id of a real department (this is a foreign key). The keys topic explains both.

Type names vary between databases (for example VARCHAR(100) in MySQL and PostgreSQL, TEXT in SQLite), but the shape of the statement is the same. SQLite is also relaxed about types: it stores text in an INTEGER column unless the table is declared STRICT. MySQL in strict mode and PostgreSQL reject such values, so do not treat SQLite's tolerance as normal.

ALTER TABLE

ALTER changes an existing table. Adding a column is the common case. Existing rows get NULL in the new column, or its default value if it has one:

ALTER TABLE employees ADD COLUMN phone TEXT;
UPDATE employees SET phone = '98450 11111' WHERE id = 1;
SELECT id, name, phone FROM employees WHERE id <= 3;

id | name | phone
1 | Asha Rao | 98450 11111
2 | Vikram Shah | NULL
3 | Neha Gupta | NULL

UPDATE and DELETE: WHERE decides everything

An UPDATE or DELETE without WHERE changes every row in the table. A safe habit: write a SELECT with the same WHERE first, check the rows it returns, then turn it into the UPDATE or DELETE.

UPDATE employees SET salary = salary * 11 / 10 WHERE dept_id = 2;
SELECT id, name, salary FROM employees WHERE dept_id IN (2, 3);

id | name | salary
3 | Neha Gupta | 90200
4 | Rohan Mehta | 70400
5 | Priya Nair | 70400
6 | Arjun Das | 70000
7 | Kavya Iyer | 52000

Only department 2 changed; department 3 is shown to prove it. With missing values, the NULL rules apply: WHERE bonus = NULL updates nothing, while WHERE bonus IS NULL updates the rows without a bonus.

DELETE FROM orders WHERE status = 'CANCELLED';
SELECT status, COUNT(*) FROM orders GROUP BY status;

status | COUNT(*)
DELIVERED | 5
PENDING | 2
SHIPPED | 2

A note on division: SQLite and PostgreSQL divide two whole numbers as whole numbers (7 / 2 is 3), while MySQL's / returns a decimal. salary * 11 / 10 is exact here because every salary is a multiple of 10.

DELETE, TRUNCATE and DROP

DELETE removes the rows that match WHERE, or all rows without it, one row at a time. It fires DELETE triggers (code the database runs on each deleted row) and can be rolled back (undone) inside a transaction. A transaction is a group of statements that are saved together or undone together; see the keys and transactions topic.

TRUNCATE removes all rows at once and keeps the empty table. It takes no WHERE, is much faster on big tables and does not fire DELETE triggers. In MySQL it commits at once (the change is saved for good), so it cannot be rolled back, and it resets the AUTO_INCREMENT counter (the counter that gives each new row the next id). In PostgreSQL it can be rolled back inside a transaction.

DROP TABLE removes the table itself: rows, structure, indexes and constraints.

SQLite, which runs this page, has no TRUNCATE statement. A DELETE without WHERE on a table with no triggers uses a fast internal "truncate optimisation" instead.

How to answer in an interview

For "DELETE versus TRUNCATE versus DROP", compare them on four points: what is removed (chosen rows, all rows, or the whole table), whether WHERE is allowed, speed, and rollback. Then add one practical line: "In test cleanup I use DELETE with a WHERE that matches only the data my test created."

Common mistakes

• Running UPDATE or DELETE without first checking the WHERE with a SELECT.
• Cleaning up with "delete everything older than today" on a shared database. Delete only what your test created, and give that data values that are easy to spot (a test prefix in names or emails) so cleanup can find it.
• Ignoring the affected row count (the number of rows the statement changed). An UPDATE that changed 0 rows is not an error, but it usually means the test is wrong.
• Saying TRUNCATE can never be rolled back; that is true in MySQL but not in PostgreSQL.

Recap

DDL changes structure and DML changes rows. Name columns in INSERT, check WHERE before UPDATE or DELETE, and remember the three ways to remove data: DELETE removes chosen rows, TRUNCATE removes all rows, and DROP removes the table. Where your framework allows it, run each test inside a transaction and roll it back.