INSERT · UPDATE · DELETE

Lecture 4 · SQL as a Data Manipulation Language — changing the data (rows), not the schema. Plus how the structural commands (ALTER, DROP) differ.

DML changes data state, not structure. It works on tuples in existing tables and is often wrapped in transactions (COMMIT / ROLLBACK).

INSERT — add rows

Start from this Student table:

student_id	name	dept_name	cgpa
101	Rahim	CSE	3.75
102	Karim	EEE	3.4
103	Joya	BBA	3.9

3 tuples · 4 attributes

Task

Add a new student — a NULL is allowed where no value is known.

sql

-- full row (column list optional if you give every value)
INSERT INTO Student (student_id, name, dept_name, cgpa)
VALUES (101, 'Rahim', 'CSE', 3.75);

-- insert with a null value
INSERT INTO Student VALUES (3003, 'Green', 'Finance', null);

-- insert the result of another query
INSERT INTO Alumni (student_id, name)
SELECT student_id, name FROM Student WHERE cgpa >= 3.80;

↓ Result the new row lands at the bottom

student_id	name	dept_name	cgpa
101	Rahim	CSE	3.75
102	Karim	EEE	3.4
103	Joya	BBA	3.9
3003	Green	Finance	null

4 tuples · 4 attributes

UPDATE — modify rows

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;          -- omit WHERE and EVERY row changes

Conditional update — the CASE statement

“Raise salaries over 10000 by 3%, everyone else by 5%.” Two separate statements are order-sensitive and clumsy — CASE does it in one pass:

ID	name	dept_name	salary
1	Abdur	CSE	10000
2	aman	CSE	12500
3	bilal	CSE	17000
4	morshed	EEE	12000
5	rashid	BBA	10000
6	irshad	BBA	7800

6 tuples · 4 attributes

Task

Give everyone a raise: > 10000 → +3%, otherwise +5%.

sql

UPDATE instructor
SET salary = CASE
  WHEN salary <= 10000 THEN salary * 1.05
  ELSE salary * 1.03
END;

↓ Result each salary recomputed in a single pass

ID	name	dept_name	salary
1	Abdur	CSE	10500
2	aman	CSE	12875
3	bilal	CSE	17510
4	morshed	EEE	12360
5	rashid	BBA	10500
6	irshad	BBA	8190

6 tuples · 4 attributes

CASE takes the first matching WHEN; ELSE catches the rest. Put the most specific condition first.

DELETE — remove rows

Task

Delete every student in the BBA department.

sql

DELETE FROM Student WHERE dept_name = 'BBA';

DELETE FROM Student WHERE student_id = 103;  -- a specific row
DELETE FROM Student;                          -- ALL rows, table remains

↓ Result the table stays; only matching rows go

student_id	name	dept_name	cgpa
101	Rahim	CSE	3.75
102	Karim	EEE	3.4

2 tuples · 4 attributes

Deleting against a moving target: to delete instructors earning below the average, SQL first computes the average and marks the rows, then deletes — it does not recompute the average as rows disappear.

DML vs DDL — the four “update” words

DML

INSERT / UPDATE / DELETE — change rows; the table structure is untouched.

DDL

ALTER changes structure; DROP removes the whole object (data + structure).

ALTER TABLE Student ADD phone VARCHAR(15);            -- new column, all NULL
ALTER TABLE Student RENAME COLUMN dept_name TO department;
DROP TABLE IF EXISTS Student;                         -- safe drop

DELETE vs DROP: DELETE (DML) empties rows but keeps the table; DROP (DDL) removes the table entirely.

Next: SELECT–FROM–WHERE →