Lesson 2 of 12 · 0 of 1 exercises solved

CREATE, INSERT, UPDATE, DELETE

This lesson covers creating tables and changing the data in them. Together with SELECT, these statements make up 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 . Constraints like NOT and UNIQUE enforce rules on the data so garbage can't get in.

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
);

-- 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;

Practice

Three changes in order: insert a new student named Hypatia, age 17, grade 97; Ken's grade to 70; then delete every student younger than 16.

Exercise

Insert, update, delete

4 checks

-- 1. INSERT Hypatia

-- 2. UPDATE Ken

-- 3. DELETE students under 16

SELECT * FROM students;

Output

Press Run to try your code, then Check answer to grade it.

Check yourself

Quick check

What does DELETE FROM students; do?

Finished reading?1 exercise still open. You can come back to it later.

Tip: use the ← and → keys to move between lessons.