DBMS & SQL›Level 11
PART 3 • SQL MASTERY

Change Rows Deliberately, Not Accidentally

Write data-changing SQL that states the intended rows, respects constraints and proves its effect before you commit to the change.

Level 11 of 18Core SQL140–180 minutesLevel 10 recommended
BY THE END, YOU CAN
  • Insert one or many valid rows.
  • Target updates precisely.
  • Delete with a verified predicate.
  • Explain upsert and MERGE choices.
  • Use affected-row counts and RETURNING.
  • Plan reversible data changes.
01 • CHANGE TABLE CONTENTS

DML Moves Rows Through a Controlled Lifecycle

Every change should answer three questions: what values, which rows and how will the result be verified?

INSERT

Create rows

Supply values that satisfy types, keys and constraints.

→
UPDATE

Modify rows

Change selected columns in precisely matched rows.

→
DELETE

Remove rows

Delete only rows that meet an intentional predicate.

→
VERIFY

Prove the effect

Inspect returned rows, counts and post-change invariants.

02 • ADD VALID ROWS

Name Target Columns in Every INSERT

INSERT INTO students (student_id, name, branch, cgpa)
VALUES (106, 'Farah', 'AIML', 8.7);
Target table

students receives the new row.

Explicit column list

Protects the statement from physical column-order changes.

Value mapping

Each value corresponds positionally to its named column.

SINGLE ROWVALUES (106, 'Farah', 'AIML', 8.7)

Best for one clearly known record.

MULTI-ROWVALUES (...), (...), (...)

Reduces round trips and expresses one logical batch.

FROM A QUERYINSERT INTO alumni (...) SELECT ...

Copies a relational result without manually listing values.

DEFAULT VALUESINSERT ... DEFAULT VALUES

Lets defined defaults supply omitted values where supported.

BEFORE THE ROW IS ACCEPTEDTypes→NOT NULL→CHECK→UNIQUE / PK→FOREIGN KEY
03 • CHANGE MATCHED ROWS

UPDATE Has No Built-In “One Row” Assumption

UPDATE students
SET cgpa = 8.9,
    updated_at = CURRENT_TIMESTAMP
WHERE student_id = 106;
Predicate controls the blast radius

WHERE student_id = 106 should match one key value. Without WHERE, every row is a candidate.

  1. Run the predicate as a SELECT.
  2. Check row identities and count.
  3. Perform the UPDATE in a transaction.
  4. Verify changed values and constraints.
OLD VALUES

Expressions normally read the row before assignment, but multiple-assignment details can be dialect-sensitive.

DERIVED CHANGEsalary = salary * 1.05

Transforms each matched row from its current value.

NULLmentor_id = NULL

Explicitly clears a nullable attribute; it is not the same as omitting it.

JOINED UPDATE

Syntax differs across PostgreSQL, SQL Server and MySQL. Verify the exact target dialect.

04 • REMOVE INTENDED ROWS

DELETE Removes Rows, Not the Table

1

Preview

SELECT * FROM students WHERE status = 'WITHDRAWN';
2

Validate

Confirm keys, row count, retention rules and dependants.

3

Delete

DELETE FROM students WHERE status = 'WITHDRAWN';
4

Verify

Inspect affected rows and test remaining invariants.

Stop conditionsMissing WHEREUnexpected row countUnreviewed cascadeNo recovery pathWrong environment
05 • HANDLE NEW OR EXISTING ROWS

Upsert Is a Goal, Not One Universal Syntax

POSTGRESQL / SQLITE STYLE
INSERT INTO students (...)
VALUES (...)
ON CONFLICT (student_id)
DO UPDATE SET cgpa = EXCLUDED.cgpa;
MYSQL STYLE
INSERT INTO students (...)
VALUES (...)
ON DUPLICATE KEY UPDATE
cgpa = VALUES(cgpa);
MERGE FAMILY
MERGE INTO target t
USING source s ON (...)
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...;
Choose the conflict key explicitly.Decide which source wins.Protect immutable columns.Test concurrent requests.Do not assume MERGE behavior is identical across products.
06 • OBSERVE THE RESULT

A Successful Statement Still Needs Verification

AFFECTED ROW COUNT1 row updated

Compare with the expected blast radius. Zero or many may expose a stale key or broad predicate.

RETURNED ROWSUPDATE ... RETURNING student_id, cgpa;

PostgreSQL and some other systems can return changed data directly; syntax and support differ.

POST-CONDITION QUERYSELECT ... WHERE student_id = 106;

Confirm final values and related records using an independent read.

AUDIT EVIDENCEwho · when · what · why

Important systems record accountable change evidence without exposing sensitive values unnecessarily.

07 • PREVIEW, APPLY, UNDO

Interactive Data-Change Laboratory

Build a controlled statement, preview its exact row impact and apply it to a temporary classroom dataset. Nothing is sent to a database.

Choose an operation to generate SQL.
CLASSROOM DATASET

students

5 rows
Will change New row Will delete
08 • MAKE THE RELEASE DECISION

DML Safety Decision Laboratory

Choose a situation and decide whether to proceed, revise or stop.

09 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. Why list columns explicitly in INSERT?

2. An UPDATE without WHERE normally targets:

3. Best preview before a DELETE?

4. Multi-row INSERT primarily lets one statement:

5. If an UPDATE reports 8,000 rows but 1 was expected:

6. Upsert syntax is:

7. Setting a nullable column to NULL means:

8. A foreign key may make a parent DELETE:

9. RETURNING is useful for:

10. Which is the safest general DML sequence?

Answered correctly: 0 of 10
10 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS
  1. Define DML.
  2. INSERT versus UPDATE?
  3. What is an affected-row count?
  4. Why is WHERE important?
  5. What is an upsert?
5-MARK / PRACTICAL
  1. Write single- and multi-row INSERT statements.
  2. Plan a safe salary UPDATE.
  3. Explain referential effects of DELETE.
  4. Compare upsert approaches.
INTERVIEW QUESTIONS
  1. How do you prevent mass UPDATE?
  2. How do retries create duplicates?
  3. What would you verify after DML?
  4. When is MERGE risky?
  5. How do constraints affect a batch?
Show the safe DML answer framework
  1. State the intended business change.
  2. Name the target table and columns.
  3. Define the exact row predicate or conflict key.
  4. Preview row identities and count.
  5. Consider constraints, cascades and concurrency.
  6. Use a transaction where appropriate.
  7. Verify affected rows and post-conditions.
  8. Explain recovery if the result differs.
LEVEL 11 SUMMARY

You Can Now Change Data with Evidence

  • INSERT maps explicit columns to valid values.
  • UPDATE changes every row matched by its predicate.
  • DELETE requires preview, dependency review and recovery planning.
  • Upsert and MERGE syntax and behavior vary by DBMS.
  • Affected-row counts and returned rows prove impact.
  • Safe DML follows preview, validate, change and verify.
COURSE CHECKPOINT

Mark this level when you can write INSERT, UPDATE and DELETE statements and explain their exact row impact before execution.

Saved in this browser only.