SQL DML: INSERT, UPDATE, DELETE

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. Can a UNIQUE column hold NULL in several rows? from Lesson 3.3.1
2. What does DROP TABLE remove? from Lesson 3.3.1
3. Which operations can violate referential integrity? from Lesson 2.3.1

1 · The Hook

Bhavin's CGPA is wrong, a new student joins, a course is cancelled. Changing data in SQL takes three statements: INSERT, UPDATE, DELETE. They look simple, but one property catches GATE candidates: SQL changes all qualifying rows at once, using the values before the statement started.

2 · Predict First

Table T(a, b) has one row (1, 2). After UPDATE T SET a = b, b = a; what is the row?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

(2, 1): the values are swapped. Every right-hand side is evaluated on the old row before any assignment happens. (Thinking "a becomes 2, then b becomes the new a = 2" would wrongly give (2, 2).)

INSERT

INSERT INTO STUDENT VALUES (5, 'Esha', 'CS', NULL);
INSERT INTO STUDENT (roll, name) VALUES (6, 'Farhan');      -- other columns NULL or DEFAULT
INSERT INTO TOPPER (roll, name)                             -- many rows from a query
  SELECT roll, name FROM STUDENT WHERE cgpa >= 9;

Every constraint (domain, key, NOT NULL, FK, CHECK) is checked; a violating row is rejected.

UPDATE

UPDATE STUDENT SET cgpa = cgpa + 0.2 WHERE dept = 'EE';
UPDATE COURSE  SET credits = 4, dept = 'CS' WHERE cid = 'C4';

• Without WHERE, every row is updated.
• Set semantics: the WHERE clause picks the rows first, then all SET expressions are computed from the old values, then all rows change together. The order rows are processed in does not affect the result.

DELETE

DELETE FROM ENROLL WHERE grade = 'C';
DELETE FROM ENROLL;            -- all rows deleted, table still exists

• Deleting a referenced row triggers its FK action (reject, cascade, set null …).
• DELETE without WHERE keeps the (empty) table; DROP TABLE removes it.

Summary of "remove" statements

4 · Visual

UPDATE T SET a = b, b = a;

old row: a=1 b=2
  compute new a := old b = 2
  compute new b := old a = 1      (both computed from OLD values)
new row: a=2 b=1

5 · Worked Examples

Example 1 Easy

Write SQL to add 0.5 to the CGPA of every ME student.

Solution

UPDATE STUDENT SET cgpa = cgpa + 0.5 WHERE dept = 'ME'; Only Diya changes (6.9 → 7.4).

Example 2 Medium

ENROLL.roll references STUDENT with ON DELETE CASCADE. DELETE FROM STUDENT WHERE dept = 'CS'; How many ENROLL rows disappear?

Solution

CS students: 1 (3 enrolments) and 3 (2 enrolments): 5 ENROLL rows are cascaded away (and 2 STUDENT rows deleted).

Example 3 GATE

T(x) holds 1, 2, 3. After UPDATE T SET x = x + 1; does the update fail because 1 + 1 = 2 already exists (x is the primary key)?

Solution

Under SQL's set semantics, constraints are checked on the final result {2, 3, 4}, which has no duplicates, so the update succeeds. (Some systems check row by row and may fail; GATE follows the standard.)

8 · Connect the Dots

Lesson 3.3.1 created tables; this lesson changed their rows. Next: the heart of SQL, the SELECT query, and the first big difference from relational algebra: SQL keeps duplicates.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Swap

T(a, b) holds (3, 7). After UPDATE T SET a = b, b = a; the row is:

1. (A) (7, 7)
2. (B) (3, 7)
3. (C) (3, 3)
4. (D) (7, 3)

Q2 · NAT · 1 mark(s) — DELETE

ENROLL has 7 rows. How many remain after DELETE FROM ENROLL WHERE grade <> 'A'?

Numerical answer: ________

Q3 · MCQ · 1 mark(s) — Remove

Which statement removes all rows but keeps the table definition?

1. (A) DROP TABLE T
2. (B) DELETE FROM T
3. (C) ALTER TABLE T DROP COLUMN x
4. (D) DROP SCHEMA

Q4 · NAT · 2 mark(s) — Cascade

ENROLL.roll → STUDENT ON DELETE CASCADE. How many ENROLL rows remain after DELETE FROM STUDENT WHERE cgpa < 8? (Running data: 7 enrolments.)

Numerical answer: ________

Q5 · MSQ · 2 mark(s) — Semantics

Which statements are true?

1. (A) UPDATE without WHERE changes every row.
2. (B) The result of an UPDATE can depend on the order rows are processed.
3. (C) DELETE triggers referential actions on referencing tables.
4. (D) INSERT ... SELECT can add many rows at once.

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

INSERTINSERT … SELECTUPDATE … SETDELETE FROMset-at-a-time semanticsTRUNCATE