Modifying Data with INSERT, UPDATE, DELETE

So far you've practiced reading from the University database. Now it's time to change the data. We'll add new students and courses, update grades, and remove outdated rows while protecting the integrity of our relationships.

Learning Objectives

• Insert rows into department, student, course, and enrollment
• Update existing rows safely with targeted WHERE clauses
• Delete records while respecting foreign-key constraints
• Apply best practices like transactions and pre-check queries

Inserting Data

INSERT adds brand-new rows. Always list the columns explicitly so your statements survive schema tweaks.

Add a new department and student

INSERT INTO department (dept_id, dept_name)
VALUES (7, 'Data Science');

INSERT INTO student (student_id, first_name, last_name, email, dept_id, enrollment_year)
VALUES (13, 'Leena', 'Roy', 'leena.roy@students.local', 7, 2025);

Multiple rows can be inserted in a single statement:

Insert several new courses

INSERT INTO course (course_id, course_code, course_title, credits, dept_id, instructor_id) VALUES
  (12, 'DS201', 'Intro to Data Science', 3, 7, NULL),
  (13, 'DS310', 'Statistical Modeling', 4, 7, NULL);

If your database auto-generates IDs (e.g., INTEGER PRIMARY KEY in SQLite), omit those columns and let the engine supply the values.

Insert from a SELECT

Populate a table by pulling data from an existing one.

Copy Computer Science students into an advising list

CREATE TABLE cs_advising (
  student_id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name  TEXT
);

INSERT INTO cs_advising (student_id, first_name, last_name)
SELECT student_id, first_name, last_name
FROM student
WHERE dept_id = 1;

Updating Data

UPDATE changes existing rows. The WHERE clause is mandatory unless you truly intend to alter every row.

Assign an instructor to a course

UPDATE course
SET instructor_id = 2
WHERE course_code = 'DS201';

Boost grades for completed projects

UPDATE enrollment
SET grade = 'A'
WHERE course_id = 7             -- Algorithms
  AND semester = 'Spring 2025'
  AND grade IN ('A-', 'B+');

To review affected rows before committing, run the same filter with SELECT:

SELECT *
FROM enrollment
WHERE course_id = 7
  AND semester = 'Spring 2025'
  AND grade IN ('A-', 'B+');

Deleting Data

DELETE removes rows. Use it with precision; foreign-key constraints may block deletes when dependent rows exist.

Remove an enrollment that was entered in error

DELETE FROM enrollment
WHERE enroll_id = 5;

If you need to purge records for an entire semester:

Clear in-progress grades for restarts

DELETE FROM enrollment
WHERE semester = 'Fall 2024'
  AND grade IS NULL;

To remove a course entirely, delete dependent enrollments first or define cascading rules:

BEGIN TRANSACTION;
  DELETE FROM enrollment WHERE course_id = 12;
  DELETE FROM course      WHERE course_id = 12;
COMMIT;

Best Practices

• Always test with SELECT: Preview the rows that match your WHERE clause.
• Use transactions: Wrap related INSERT, UPDATE, or DELETE statements in a transaction so you can roll back if needed.
• Respect constraints: Inserting or updating with invalid foreign keys (e.g., a non-existent dept_id) will fail.
• Default values: Rely on default settings (like credits defaulting to 3) when appropriate instead of hard-coding them everywhere.
• Logging: In production systems, audit critical changes so you can trace who altered what.

Summary

• INSERT adds data; specify columns for clarity and resilience.
• UPDATE changes data; a narrow WHERE clause keeps modifications safe.
• DELETE removes data; understand dependencies before you run it.
• Transactions and preflight SELECT queries protect you from mistakes.

Quiz

Show quiz

1. Which clause ensures an UPDATE affects only intended rows?
   A) SET
   B) FROM
   C) WHERE
   D) VALUES

2. How can you add two new courses in a single statement?
   A) Run two separate INSERT commands
   B) Use INSERT ... VALUES (...), (...);
   C) Use UPDATE with multiple values
   D) Modify the table default

3. True or False: You can delete a course that still has enrollment records without additional steps.
   A) True
   B) False

4. Why should you wrap related modifications in a transaction?
   A) It speeds up queries
   B) It allows rolling back if any statement fails
   C) It automatically creates indexes
   D) It bypasses foreign-key checks

---

Answers

1. C
2. B
3. B
4. B