DBMS

SQL Fundamentals

Views, Transactions and DML

INSERT INTO students (name, city, marks) VALUES ('Nina', 'Pune', 79);

JrCodex·8 min read

Jr Codex DBMS Notes

Level: Intermediate Prerequisites: Chapter 5: Subqueries and CTEs Time to complete: ~25 minutes

Table of Contents

  1. INSERT
  2. UPDATE
  3. DELETE
  4. UPSERT
  5. Views
  6. Transactions in Practice
  7. Parameterised Queries
  8. Summary & Next Steps

1. INSERT

-- Always name the columns. Positional INSERT breaks when a column is added.
INSERT INTO students (name, city, marks) VALUES ('Nina', 'Pune', 79);
 
-- Multi-row: ONE statement, one round trip, one transaction.
INSERT INTO students (name, city, marks) VALUES
    ('Om',   'Delhi',  68),
    ('Priya','Mumbai', 85),
    ('Qasim','Pune',   72);
 
-- INSERT ... SELECT: bulk-load from a query.
INSERT INTO honour_roll (student_id, marks)
SELECT id, marks FROM students WHERE marks >= 85;
 
-- RETURNING gives you back generated values (PostgreSQL, SQLite 3.35+)
INSERT INTO students (name, city, marks) VALUES ('Riya','Delhi',90)
RETURNING id, name;
Batch, Do Not Loop
─────────────────────────────────────────
  10,000 single-row INSERTs in a Python loop, each
  auto-committed, is 10,000 round trips and 10,000
  fsyncs.

  The same rows in batches inside one transaction
  is routinely 50-100x faster — because durability
  (Module 1, Chapter 4) is paid once, not 10,000
  times.
─────────────────────────────────────────

rows = [(n, c, m) for n, c, m in source]
with conn:                                        # one transaction for the whole batch
    conn.executemany(
        "INSERT INTO students (name, city, marks) VALUES (?, ?, ?)", rows)

2. UPDATE

UPDATE students SET marks = 90 WHERE id = 1;
UPDATE students SET marks = marks + 5 WHERE city = 'Pune';        -- relative
UPDATE students SET city = 'Pune', marks = 88 WHERE id = 4;        -- several columns
 
-- UPDATE from another table (PostgreSQL syntax)
UPDATE students s
SET marks = x.avg_grade_points
FROM (SELECT student_id, AVG(points) AS avg_grade_points
      FROM grade_points GROUP BY student_id) x
WHERE x.student_id = s.id;
THE MISSING WHERE
─────────────────────────────────────────
  UPDATE students SET marks = 90;

  Valid SQL. Updates EVERY row. No warning.

  Habits that prevent it:
    1. Write the WHERE clause FIRST, then go back
       and write SET
    2. Run it as SELECT first — same FROM and
       WHERE — and check the row count
    3. Wrap in BEGIN, verify, then COMMIT
    4. Enable your client's safe-update mode where
       one exists
─────────────────────────────────────────

-- The verify-then-commit habit:
BEGIN;
    SELECT COUNT(*) FROM students WHERE city = 'Pune';    -- expect 3
    UPDATE students SET marks = marks + 5 WHERE city = 'Pune';
    -- check the reported row count matches
COMMIT;                                                    -- or ROLLBACK

3. DELETE

DELETE FROM students WHERE id = 6;
DELETE FROM enrollments WHERE grade IS NULL;
 
-- Delete by reference to another table
DELETE FROM enrollments
WHERE student_id IN (SELECT id FROM students WHERE city = 'Mumbai');
Foreign Keys Change What Happens
─────────────────────────────────────────
  DELETE FROM students WHERE id = 1;

  enrollments references students ON DELETE
  CASCADE, so Asha's three enrollments are deleted
  too — silently, as part of the same statement.

  With ON DELETE RESTRICT, the delete FAILS while
  any enrollment exists.

  Know which one your schema declared before
  running a delete you cannot undo (Module 2,
  Chapter 2).
─────────────────────────────────────────

Soft Deletes
─────────────────────────────────────────
  Many systems never DELETE:

    ALTER TABLE students ADD COLUMN deleted_at TEXT;
    UPDATE students SET deleted_at = datetime('now')
      WHERE id = 6;

  Preserves history and makes deletion reversible.
  Costs you a WHERE deleted_at IS NULL on every
  query forever — usually hidden behind a view
  (Section 5).
─────────────────────────────────────────

4. UPSERT

Insert, or update if it already exists — as one atomic statement.

-- PostgreSQL / SQLite
INSERT INTO students (id, name, city, marks)
VALUES (1, 'Asha', 'Pune', 92)
ON CONFLICT (id) DO UPDATE
SET marks = excluded.marks,               -- `excluded` = the row we tried to insert
    city  = excluded.city;
 
-- Insert only if absent, ignore otherwise
INSERT INTO students (id, name) VALUES (1, 'Asha')
ON CONFLICT (id) DO NOTHING;
 
-- MySQL
INSERT INTO students (id, name, marks) VALUES (1, 'Asha', 92)
ON DUPLICATE KEY UPDATE marks = VALUES(marks);
Why Not SELECT-Then-INSERT
─────────────────────────────────────────
  if not exists: insert  else: update

  Between the check and the write, another
  connection can insert the same row. You then get
  a duplicate-key error, or worse, a lost update
  (Module 7).

  UPSERT is a SINGLE atomic statement, so the race
  window does not exist. Use it rather than
  re-implementing it in application code.
─────────────────────────────────────────

5. Views

A view is a stored query that behaves like a table.

CREATE VIEW student_summary AS
SELECT s.id, s.name, s.city,
       COUNT(e.course_id)                      AS courses,
       SUM(CASE WHEN e.grade = 'A' THEN 1 ELSE 0 END) AS a_grades
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name, s.city;
 
SELECT * FROM student_summary WHERE courses >= 3;    -- query it like a table
What Views Are For
─────────────────────────────────────────
  SIMPLIFY     hide a complex join behind a name

  SECURITY     grant access to the VIEW, not the
               table — the salary column simply is
               not in it

  STABILITY    Module 1, Chapter 3's logical data
               independence: restructure the tables,
               redefine the view, and applications
               do not change

  CONSISTENCY  the soft-delete filter lives in one
               place instead of every query
─────────────────────────────────────────

-- MATERIALIZED VIEW: the results are STORED, not recomputed.
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept, COUNT(*) AS courses, SUM(credits) AS total_credits
FROM courses GROUP BY dept;
 
REFRESH MATERIALIZED VIEW dept_stats;      -- must be refreshed explicitly
Ordinary vs Materialized
─────────────────────────────────────────
  VIEW               re-runs the query every time.
                     Always current. Costs the full
                     query on each access.

  MATERIALIZED VIEW  stores the result. Fast to
                     read. STALE until refreshed.

  Use materialized views for expensive aggregates
  that tolerate being minutes old — dashboards,
  reports. Never for anything that must be current.
─────────────────────────────────────────

6. Transactions in Practice

import sqlite3
 
conn = sqlite3.connect("school.db", isolation_level=None)
conn.execute("PRAGMA foreign_keys = ON")
 
def enrol(conn, student_id, course_id):
    try:
        conn.execute("BEGIN")
        conn.execute("INSERT INTO enrollments (student_id, course_id) VALUES (?,?)",
                     (student_id, course_id))
        conn.execute("UPDATE courses SET seats_taken = seats_taken + 1 WHERE id = ?",
                     (course_id,))
        conn.execute("COMMIT")
    except sqlite3.Error:
        conn.execute("ROLLBACK")                 # BOTH statements undone
        raise
Three Rules
─────────────────────────────────────────
  KEEP THEM SHORT. An open transaction holds locks
  (Module 7). Never wait on a network call, a user,
  or a file read inside one.

  NEVER LEAVE ONE OPEN. Use a context manager or a
  try/finally. An abandoned transaction blocks
  other writers indefinitely.

  GROUP BY UNIT OF WORK, not by statement. The two
  statements above are one logical action; either
  both happen or neither does.
─────────────────────────────────────────

# Python's context manager handles commit/rollback for you.
with conn:                                       # COMMIT on success, ROLLBACK on exception
    conn.execute("INSERT INTO enrollments (student_id, course_id) VALUES (?,?)", (6, 10))

7. Parameterised Queries

# NEVER build SQL with string formatting.
name = "'; DROP TABLE students; --"
conn.execute(f"SELECT * FROM students WHERE name = '{name}'")     # SQL INJECTION
 
# ALWAYS pass parameters separately.
conn.execute("SELECT * FROM students WHERE name = ?", (name,))    # safe
Why Parameters Are Safe
─────────────────────────────────────────
  The SQL text is parsed ONCE, with placeholders.
  Values are supplied afterwards and can never be
  parsed as SQL — they are data by construction,
  not by escaping.

  Escaping is a filter that can be got around.
  Parameterisation removes the possibility, which
  is a different kind of guarantee.

  BONUS: the parsed plan is CACHEABLE and reused
  across executions, so parameterised queries are
  also faster.
─────────────────────────────────────────

# Parameters are for VALUES, never for identifiers.
conn.execute("SELECT * FROM ? WHERE id = ?", (table, 1))       # does NOT work
 
ALLOWED = {"students", "courses", "enrollments"}               # validate against a
if table not in ALLOWED:                                       # whitelist instead
    raise ValueError(f"unknown table {table!r}")
conn.execute(f"SELECT * FROM {table} WHERE id = ?", (1,))

8. Summary & Next Steps

Key Takeaways

  • Batch inserts inside one transaction rather than looping single statements — durability is then paid once instead of per row.
  • UPDATE and DELETE without a WHERE are valid SQL that silently affect every row; write the WHERE first and verify with a SELECT.
  • UPSERT is one atomic statement, so it has no race window; select-then-insert in application code does.
  • Parameterised queries make injection structurally impossible rather than filtered against, and let the database cache the plan.

Module 3 Complete — What's Next

You can now define schema, query it fluently, join, aggregate, nest and modify safely. Module 4 steps back to the question those skills assume an answer to: what should the tables be in the first place?

Concept Check

  1. Why is 10,000 looped single-row inserts so much slower than the same rows batched in one transaction?
  2. What race condition does UPSERT eliminate that select-then-insert does not?
  3. Why can a parameter not be used for a table name, and what should you do instead?

Next Module

→ Module 4: Database Design

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index