SQSQL · Lesson 2 of 8

CREATE, INSERT, UPDATE, DELETE

Reading data is half the story. The other half: creating tables and changing what's in them. These four statements are called CRUD — Create, Read, Update, Delete.

CREATE TABLE defines a new table: each column gets a name and a type. Common types are INTEGER, REAL (floating point), TEXT, and BOOLEAN. Constraints like NOT NULL and UNIQUE enforce rules on the data so garbage can't get in.

SQL

CREATE TABLE students (
  id    INTEGER PRIMARY KEY,   -- unique identifier for each row
  name  TEXT NOT NULL,         -- must have a value
  email TEXT UNIQUE,           -- no two rows can share this
  age   INTEGER,
  grade REAL DEFAULT 0         -- value used if none given
);

SQL

-- Add rows:
INSERT INTO students (name, email, age, grade)
VALUES ('Ada', 'ada@example.com', 17, 95);

-- Insert several at once:
INSERT INTO students (name, age) VALUES
  ('Linus', 16),
  ('Grace', 17);

-- Change existing rows:
UPDATE students SET grade = 89 WHERE name = 'Linus';

-- Remove rows:
DELETE FROM students WHERE age < 16;

⚠ Warning
UPDATE and DELETE without a WHERE clause affect EVERY row in the table. 'DELETE FROM students;' empties the whole table, no confirmation asked. Always write the WHERE first, and consider running it inside a SELECT to preview which rows will be hit.