INSERT, NULL, UPDATE & DELETE

INSERT INTO adds one or more rows. List column names, then VALUES. Order of values must match column order.

INSERT INTO — add new rows

INSERT INTO adds one or more rows. List column names, then VALUES. Order of values must match column order.

Real-life example: INSERT is like writing a new name into the attendance register on the first day of school.

Insert one student and multiple courses

INSERT INTO students (student_id, full_name, grade_level, city)
VALUES (101, 'Ravi Patel', 11, 'Ahmedabad');

INSERT INTO courses (course_id, course_name, credits)
VALUES
  (1, 'Mathematics', 4),
  (2, 'Physics', 4),
  (3, 'English', 3);

NULL Values — missing or unknown data

NULL means 'no value' or 'unknown'. It is not zero and not an empty string. Use IS NULL and IS NOT NULL to test for NULL.

Real-life example: NULL is a blank line on a form — the student did not fill in their city yet, so you cannot assume any city.

Insert and find NULL values

INSERT INTO students (student_id, full_name, grade_level, city)
VALUES (102, 'Meera Singh', 10, NULL);

SELECT full_name
FROM students
WHERE city IS NULL;

UPDATE students
SET city = 'Pune'
WHERE student_id = 102 AND city IS NULL;

UPDATE — change existing rows

UPDATE sets new values. Always use WHERE unless you truly mean to change every row — a missing WHERE updates the whole table.

Real-life example: UPDATE is like correcting a typo in one student's address on the register, not rewriting every page.

Fix a city and bump a grade level

UPDATE students
SET city = 'Bangalore'
WHERE student_id = 101;

UPDATE students
SET grade_level = grade_level + 1
WHERE grade_level = 10;

DELETE — remove rows

DELETE removes rows that match WHERE. DELETE FROM students with no WHERE deletes all students — dangerous in production.

Real-life example: DELETE is like crossing out one enrollment when a student drops a course, not shredding the whole file cabinet.

Before DELETE or UPDATE in real work, run a SELECT with the same WHERE first to preview affected rows.

Delete one enrollment or all rows (careful!)

-- Remove one enrollment
DELETE FROM enrollments
WHERE enrollment_id = 5001;

-- Remove ALL rows from a table (rare — usually use TRUNCATE in admin tasks)
-- DELETE FROM enrollments;