Lab 09 — INSERT, UPDATE, DELETE & CREATE

Not just reading data — writing it. Create tables, insert rows, update records, and safely delete data. The database resets on every page reload so experiment freely!

Loading SQL engine...

STARTING TABLE: students (starts EMPTY — you will populate it!)TABLE: students id INTEGER PRIMARY KEY AUTOINCREMENT name TEXT NOT NULL major TEXT gpa REAL year INTEGER The table is empty. Your INSERT statements will fill it. Page reload resets everything -- experiment without fear!

Concept Recap: DDL & DML

DDL (Data Definition Language) — defines structure:

CREATE TABLE name (col type, ...) — create a new table
CREATE TABLE IF NOT EXISTS — safe version that won't error if it exists
DROP TABLE name — delete a table and all its data
Column constraints: PRIMARY KEY, AUTOINCREMENT, NOT NULL, DEFAULT value, UNIQUE

DML (Data Manipulation Language) — modifies data:

INSERT INTO table (col1, col2) VALUES (v1, v2) — add a row
INSERT INTO table VALUES (v1, v2, ...) — insert with all columns in order
UPDATE table SET col = val WHERE condition — modify existing rows
DELETE FROM table WHERE condition — remove rows
Always use WHERE with UPDATE and DELETE — omitting it affects every row!

Worked Example (read-only)

Create a table, insert rows, update a price, and verify the result.

CREATE TABLE IF NOT EXISTS products (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT NOT NULL,
    price REAL,
    stock INTEGER DEFAULT 0
);
INSERT INTO products (name, price, stock) VALUES ('Laptop', 999.99, 50);
INSERT INTO products (name, price, stock) VALUES ('Mouse', 29.99, 200);
UPDATE products SET price = 899.99 WHERE name = 'Laptop';
SELECT * FROM products;

Guided

Exercise 1 — Your First INSERT

Insert 3 students into the students table. Fill in the VALUES for students 2 and 3, then run a SELECT to verify they were added.

Hint: Column order is (name, major, gpa, year). The id is AUTOINCREMENT so omit it.

-- Insert 3 students
INSERT INTO students (name, major, gpa, year) VALUES ('Alice Chen', 'Computer Science', 3.8, 3);
INSERT INTO students (name, major, gpa, year) VALUES (___, ___, ___, ___);
INSERT INTO students (name, major, gpa, year) VALUES (___, ___, ___, ___);

-- Verify they were added
SELECT * FROM students;

Independent

Exercise 2 — INSERT, UPDATE, and DELETE a Full Workflow

Build up the table step by step. Run each block individually to see changes. Do the following in sequence:

Insert 5 students (mix of majors, GPAs 2.8–3.9, years 1–4).
Update Alice's GPA to 3.95 (use WHERE name = 'Alice Chen').
Update all 'English' major students to year 2.
Delete all students with GPA below 3.0.
Run SELECT * to verify final state.

Remember: Always use WHERE with UPDATE and DELETE!

-- Step 1: Insert 5 students
INSERT INTO students (name, major, gpa, year) VALUES ('Alice Chen', 'Computer Science', 3.8, 3);
INSERT INTO students (name, major, gpa, year) VALUES ('Bob Smith', 'Mathematics', 3.2, 2);
INSERT INTO students (name, major, gpa, year) VALUES ('Carol Wu', 'English', 2.8, 1);
INSERT INTO students (name, major, gpa, year) VALUES ('David Park', 'Computer Science', 3.6, 4);
INSERT INTO students (name, major, gpa, year) VALUES ('Eve Johnson', 'Mathematics', 2.9, 3);

-- Step 2: Update Alice's GPA
UPDATE students SET gpa = 3.95 WHERE name = 'Alice Chen';

-- Step 3: Update all English majors to year 2
UPDATE students SET year = 2 WHERE major = 'English';

-- Step 4: Delete students with GPA below 3.0
DELETE FROM students WHERE gpa < 3.0;

-- Step 5: Verify final state
SELECT * FROM students;

Challenge

Exercise 3 — Build Two Related Tables and JOIN Them

Create a courses table and an enrollments table. Insert data, then write a JOIN query to show which students are enrolled in which courses.

Hint: courses(id, course_name, credits), enrollments(student_id, course_id, grade). Use AUTOINCREMENT for the id columns.

-- First add some students
INSERT INTO students (name, major, gpa, year) VALUES ('Alice Chen', 'CS', 3.8, 3);
INSERT INTO students (name, major, gpa, year) VALUES ('Bob Smith', 'Math', 3.2, 2);
INSERT INTO students (name, major, gpa, year) VALUES ('Carol Wu', 'CS', 3.9, 4);

-- Create courses table
CREATE TABLE courses (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    course_name TEXT NOT NULL,
    credits     INTEGER DEFAULT 3
);

-- Insert 4 courses
INSERT INTO courses (course_name, credits) VALUES ('Intro to SQL', 3);
INSERT INTO courses (course_name, credits) VALUES ('Data Structures', 4);
INSERT INTO courses (course_name, credits) VALUES ('Statistics', 3);
INSERT INTO courses (course_name, credits) VALUES ('Machine Learning', 4);

-- Create enrollments table
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    grade      TEXT
);

-- Insert 6 enrollments
INSERT INTO enrollments VALUES (1, 1, 'A');
INSERT INTO enrollments VALUES (1, 2, 'B+');
INSERT INTO enrollments VALUES (2, 1, 'A-');
INSERT INTO enrollments VALUES (2, 3, 'B');
INSERT INTO enrollments VALUES (3, 2, 'A');
INSERT INTO enrollments VALUES (3, 4, 'A-');

-- JOIN to show students with their enrolled courses
SELECT s.name, s.major, c.course_name, c.credits, e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.course_name;

Mini Project

Mini Project — Full Student Registration System

Build a complete student registration system from scratch in a single editor. The database starts fresh every page reload, so you can run the whole block at once.

INSERT a full class of 6 students.
UPDATE 2 students — one GPA correction, one major change.
DELETE 1 student who withdrew.
CREATE a grades table: grades(id, student_id, subject, score, grade_letter).
INSERT grades for 4 remaining students across at least 2 subjects each.
Final SELECT: join students with grades to show complete academic status.

-- Step 1: Insert 6 students
INSERT INTO students (name, major, gpa, year) VALUES ('Alice Chen', 'Computer Science', 3.8, 3);
INSERT INTO students (name, major, gpa, year) VALUES ('Bob Smith', 'Mathematics', 3.2, 2);
INSERT INTO students (name, major, gpa, year) VALUES ('Carol Wu', 'Computer Science', 3.9, 4);
INSERT INTO students (name, major, gpa, year) VALUES ('David Park', 'English', 2.9, 1);
INSERT INTO students (name, major, gpa, year) VALUES ('Eve Johnson', 'Mathematics', 3.7, 3);
INSERT INTO students (name, major, gpa, year) VALUES ('Frank Lee', 'Psychology', 3.1, 2);

-- Step 2: Updates
UPDATE students SET gpa = 3.95 WHERE name = 'Carol Wu';
UPDATE students SET major = 'Data Science' WHERE name = 'Bob Smith';

-- Step 3: David Park withdrew
DELETE FROM students WHERE name = 'David Park';

-- Step 4: Create grades table
CREATE TABLE grades (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id   INTEGER NOT NULL,
    subject      TEXT NOT NULL,
    score        REAL,
    grade_letter TEXT
);

-- Step 5: Insert grades
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (1, 'SQL Fundamentals', 96, 'A');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (1, 'Algorithms', 88, 'B+');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (2, 'Statistics', 91, 'A-');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (2, 'SQL Fundamentals', 84, 'B');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (3, 'Algorithms', 99, 'A');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (3, 'Machine Learning', 95, 'A');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (5, 'Statistics', 87, 'B+');
INSERT INTO grades (student_id, subject, score, grade_letter) VALUES (5, 'SQL Fundamentals', 90, 'A-');

-- Step 6: Final status report
SELECT s.name, s.major, s.gpa, s.year,
       g.subject, g.score, g.grade_letter
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
ORDER BY s.name, g.subject;

Lab 09 Complete!

You can now create tables with constraints, insert rows, update specific records, and safely delete data. You also know why WHERE is non-negotiable with UPDATE and DELETE.

One lab to go — the capstone. No fill-in-the-blanks. Real analyst questions.

Continue to Lab 10 — Capstone →