Change data

Queries that change the database

Until now, you only read data. SQL can also change it. A statement that changes data does not give a result table. Absolute Path shows a short message, such as OK. 1 row changed.

The changes last only for the run. Each time you press Run, the database starts again from the start, and your whole file runs from the top.

CREATE TABLE

CREATE TABLE makes a new table. You give each column a name and a type.

CREATE TABLE pets (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER
);

• INTEGER is a whole number. TEXT is text. REAL is a number with decimals.
• PRIMARY KEY marks the column that identifies each row. It is never repeated.
• NOT NULL means that the column must always have a value.

INSERT

INSERT INTO adds rows. Write the column names, and then the values in the same order.

INSERT INTO pets (id, name, age) VALUES (1, 'Biscuit', 4);
INSERT INTO pets (id, name, age) VALUES (2, 'Pepper', 2), (3, 'Moss', 7);

UPDATE

UPDATE changes the rows that match the WHERE clause.

UPDATE pets SET age = 5 WHERE name = 'Biscuit';

Be careful. UPDATE and DELETE without a WHERE clause change every row in the table. Always write the WHERE clause first, and run a SELECT with the same WHERE clause to see which rows it finds.

DELETE

DELETE removes the rows that match the WHERE clause.

DELETE FROM pets WHERE age > 10;

Your turn

The table books has the same eight books as before. Do the seven tasks in order, and write the statements one after the other in the editor. The checker looks at the state of the database after your whole file has run.