Part III — SQL: Structured Query Language

Chapter 10. SQL Data Manipulation

Chapter 9 built the structure; this chapter fills and reshapes it. DML — INSERT, SELECT, UPDATE, DELETE — is the SQL you will write every working day, and its discipline is Chapter 8's warning made concrete: every changing statement carries a deliberate scope, every WHERE is written first, and every risky experiment runs inside a transaction or a scratch database.

This chapter's mutating examples run against a university_dev copy of the canonical database (created in Laboratory 9.1), so the canonical university database stays pristine and every expected output in this book remains reproducible. Queries (SELECT) are safe anywhere; changes are for dev.

After studying this chapter you will be able to:

• Insert single rows, multiple rows, and query results, with defaults and explicit NULLs.
• Read and write SELECT's full skeleton with expressions and aliases.
• Update and delete precisely, predict row counts before running, and verify with RETURNING.
• Filter with comparison, logical, and range conditions, and with pattern matching.
• Predict and control NULL behavior in comparisons, NOT IN, and ORDER BY.
• Sort on multiple keys, remove duplicates, and paginate deterministically.

10.1 Inserting records with INSERT

The single-row insert names its table and columns, then supplies values in the same order — always write the column list, even when it matches the table order, because schema evolution (Chapter 9) will otherwise silently misalign your scripts:

INSERT INTO department (dept_id, dept_name, building, budget)
VALUES (6, 'Physics', 'Academic Building E', 1600000.00);

Omitted columns take their DEFAULT (or NULL); NULLs can be written explicitly. The multi-row form loads datasets — Appendix H's data loads in exactly this style:

INSERT INTO course (course_id, title, dept_id, credits) VALUES
    ('PHY101', 'Mechanics', 6, 3),
    ('PHY102', 'Electromagnetism', 6, 3);

INSERT ... SELECT copies query results into a table — the ETL workhorse (Chapter 25) and the migration tool of Chapter 7's laboratories. Constraints police every insert: a duplicate dept_id 6, a NULL dept_name, or a dept_id value not present in department (as a child-table insert) each fail with the constraint's name — the Chapter 9 design doing its job. PostgreSQL's RETURNING clause shows what was actually stored: INSERT ... RETURNING dept_id, dept_name; — MySQL's equivalent is LAST_INSERT_ID() for generated keys. Both platforms also support the standard's MERGE-style "insert or update" — PostgreSQL 15+ writes it INSERT ... ON CONFLICT ... DO UPDATE, MySQL as INSERT ... ON DUPLICATE KEY UPDATE — the dialect map of Chapter 17.

10.2 Retrieving data with SELECT

The skeleton grows three more clauses this chapter — ORDER BY (10.9), DISTINCT (10.10), and LIMIT (10.11) — but the core is the projection:

SELECT full_name,
       gpa,
       gpa * 25           AS gpa_scaled,
       total_credits + 12 AS projected_credits
FROM   student
WHERE  major_dept_id = 1;

   full_name   | gpa  | gpa_scaled | projected_credits
---------------+------+------------+-------------------
 Nusrat Jahan  | 3.75 |      93.75 |               114
 Rakib Hasan   | 3.42 |      85.50 |               116
 Tanvir Alam   | 3.55 |      88.75 |                96
 Arif Mahmud   | 3.90 |      97.50 |                72

Three habits visible in one listing: select named columns, never SELECT *, in code that others will read (columns arrive in your order, schema additions don't break it); compute in the query, not in the application (Chapter 5's derived-attribute rule); and alias (AS name) every expression — an unaliased expression column arrives as ?column? (PostgreSQL) or a bare expression string, and no report reader should ever see either. SELECT * remains legitimate for exploration and EXISTS probes (Chapter 12).

10.3 Updating records with UPDATE

UPDATE sets columns for the rows its WHERE selects — every one of them:

UPDATE student
SET    gpa = 3.80,
       total_credits = total_credits + 3
WHERE  student_id = 21300003;

Predict before running: one row — Arif Mahmud, identified by primary key. The two SET shapes both appear here: a literal (gpa = 3.80) and a self-referencing expression (total_credits + 3), where the right side reads the row's current value. UPDATE may also set from other tables (PostgreSQL UPDATE ... SET ... FROM, MySQL a joined update — Chapter 17), but the everyday form is this one.

The professional safety net is RETURNING: PostgreSQL runs UPDATE ... WHERE ... RETURNING full_name, gpa; and prints exactly the changed rows — count them before breathing again. MySQL users wrap in a transaction (Chapter 18), verify with a SELECT, then COMMIT. And the chapter's standing warning earns its repetition: an UPDATE without WHERE updates every row — write the WHERE clause first, always.

10.4 Deleting records with DELETE

DELETE removes the rows its WHERE selects:

DELETE FROM enrollment
WHERE  section_id = 13 AND grade IS NULL;

Predict: two rows — Arif Mahmud's and Zara Hossain's in-progress enrollments in section 13. Foreign keys police deletes from both directions (Chapter 9): this statement succeeds because nothing references enrollment, but DELETE FROM course_section WHERE section_id = 13; is refused — its two enrollments cite it, and the RESTRICT action chosen in Section 9.4 holds. Deleting by primary key is the everyday case; deleting by a condition over many rows deserves the same verification habits as UPDATE (RETURNING or a transaction), because — there is no partial delete — the statement is all-or-nothing over its WHERE, and a missing WHERE again means every row.

10.5 Filtering rows with WHERE

WHERE accepts the full condition algebra — comparison, ranges, sets, patterns, and NULL tests, combined with AND/OR/NOT:

SELECT full_name, admission_year, gpa
FROM   student
WHERE  admission_year >= 2022
  AND  gpa >= 3.5
  AND  major_dept_id IN (1, 2);

   full_name    | admission_year | gpa
----------------+----------------+------
 Tanvir Alam    |           2022 | 3.55
 Arif Mahmud    |           2023 | 3.90

The set form IN (1, 2) reads better than = 1 OR = 2 and extends to query results (Chapter 12). BETWEEN 3.5 AND 3.9 is inclusive on both ends — a classic exam trap. Dates filter as values: WHERE hire_date >= DATE '2019-01-01' selects the three instructors hired in 2019 or later (Sharmin Ahmed, Farhana Rahman, Tahmina Karim). Operator precedence matters: AND binds tighter than OR, so a OR b AND c means a OR (b AND c) — when you mean the other grouping, write the parentheses even when they are technically redundant; WHERE clauses are read by humans at 2 a.m.

10.6 Comparison, logical, and arithmetic operators

The operator inventory, with precedence from tightest to loosest:

NULL runs through all of them per Chapter 2's three-valued logic: any comparison with NULL is UNKNOWN (not FALSE), UNKNOWN AND FALSE is FALSE, UNKNOWN AND TRUE is UNKNOWN, and NOT UNKNOWN is UNKNOWN. The working rule for reading any WHERE: a row survives only if the condition is exactly TRUE — everything else, including UNKNOWN, is discarded. That rule explains the classic surprise: WHERE NOT (gpa < 3.9) still drops Zara (NULL gpa), because NOT UNKNOWN is UNKNOWN. Section 10.8 collects the escape hatches.

10.7 Pattern matching with LIKE

LIKE matches strings against patterns with two wildcards: % (any run of characters, including none) and _ (exactly one):

SELECT full_name
FROM   student
WHERE  full_name LIKE '%ah%';

   full_name
---------------
 Nusrat Jahan
 Arif Mahmud
 Shahriar Islam

SELECT course_id, title
FROM   course
WHERE  course_id LIKE '_SE%';

 course_id |           title
-----------+----------------------------
 CSE215    | Programming Language II
 CSE221    | Database Systems
 CSE251    | Data Structures
 CSE321    | Web Application Development

Two platform truths to memorize. First, case sensitivity differs by default: PostgreSQL's LIKE is case-sensitive; MySQL's is case-insensitive for the default collations. PostgreSQL offers ILIKE (case-insensitive); the portable spelling is LOWER(full_name) LIKE LOWER('%mahmud%') — the form production code should use. Second, LIKE with a leading wildcard ('%text%') cannot use an ordinary B-tree index — every row must be scanned — which is why "search boxes" on large tables get dedicated text-search machinery (Chapter 19's indexes, and each platform's full-text engines). NOT LIKE negates with the same three-valued caveat as everything else: rows with NULL never match, never not-match.

10.8 Handling NULL values

The NULL survival kit, in order of frequency of use:

• IS NULL / IS NOT NULL — the only two-valued NULL tests, from Chapter 2: WHERE grade IS NULL (8 rows: the in-progress enrollments).
• COALESCE — first non-NULL of its arguments: COALESCE(gpa, 0.00) renders Zara as 0.00 in reports (with the honesty cost that a report reader cannot tell "no GPA" from "0.0 GPA" — Chapter 11 examines that trade).
• IS DISTINCT FROM — a NULL-safe inequality: a IS DISTINCT FROM b is TRUE when one side is NULL and the other is not, and behaves like <> otherwise — the comparison operator NULL should have had.
• The NOT IN trap — the most famous NULL bug in SQL:

SELECT student_id
FROM   enrollment
WHERE  section_id = 12
  AND  grade NOT IN (SELECT grade FROM enrollment
                     WHERE section_id = 13);

The inner query returns the grades of section 13 — which are all NULL, so the list is {NULL}. x NOT IN (NULL) evaluates to UNKNOWN for every x (including NULL itself), so the query returns zero rows — not "section 12's graded rows," which a careless reader expects. The law: never let a NOT IN list contain NULL — filter it (WHERE grade IS NOT NULL) inside the subquery, or rewrite with NOT EXISTS (Chapter 12), which is immune.

10.9 Sorting with ORDER BY

ORDER BY sorts the result — it is display, not structure, and Chapter 2's "row order means nothing" rule is why every paginated or reported query must sort explicitly:

SELECT full_name, gpa
FROM   student
WHERE  gpa IS NOT NULL
ORDER  BY gpa DESC, full_name ASC;

   full_name    | gpa
----------------+------
 Arif Mahmud    | 3.90
 Sadia Afrin    | 3.88
 Nusrat Jahan   | 3.75
 Mehjabin Chowdhury | 3.70
 Sumaiya Tabassum | 3.60
 Tanvir Alam    | 3.55
 Rakib Hasan    | 3.42
 Shahriar Islam | 3.25
 Imran Hossain  | 3.15
 Nabil Khan     | 3.05
 Farhan Akter   | 2.98

Multiple keys sort lexicographically: gpa first, full name breaking ties. Expressions and aliases may both be sort keys (ORDER BY gpa * 25 DESC).

NULL ordering is a platform difference to memorize. PostgreSQL treats NULL as larger than every value: ascending sorts put NULLs last, descending puts them first. MySQL treats NULL as smallest: ascending puts NULLs first, descending last. So ORDER BY gpa DESC alone puts Zara first in PostgreSQL and last in MySQL. The portable spells: PostgreSQL's explicit NULLS LAST / NULLS FIRST, or the everywhere-works pattern of sorting on a NULL-discriminator expression (ORDER BY gpa IS NULL, gpa DESC — false sorts before true, so NULLs go last on both platforms; MySQL 8.0 lacks the NULLS LAST syntax, making the expression the portable choice).

10.10 Eliminating duplicates with DISTINCT

Chapter 2 established that SQL is bag-valued; DISTINCT restores set semantics for one query, applied to the whole selected row:

SELECT DISTINCT major_dept_id FROM student;          -- 5 rows
SELECT DISTINCT major_dept_id, admission_year
FROM   student;                                     -- 11 pairs

The second listing returns 11 pairs from 12 students because exactly one pair repeats — (1, 2021): Nusrat Jahan and Rakib Hasan, both CSE majors admitted in 2021. DISTINCT costs a sort or hash — deduplicate 12 rows freely, but on a million-row join, know whether the duplicates were meaningful before discarding them. COUNT(DISTINCT major_dept_id) (Chapter 11) counts without listing.

10.11 Limiting and paginating query results

Result truncation is platform-spelled: PostgreSQL LIMIT n OFFSET m, MySQL LIMIT m, n, standard SQL OFFSET m ROWS FETCH NEXT n ROWS ONLY (PostgreSQL supports the standard form too). With ORDER BY it becomes pagination:

SELECT full_name, gpa
FROM   student
WHERE  gpa IS NOT NULL
ORDER  BY gpa DESC NULLS LAST
LIMIT  3 OFFSET 0;                    -- page 1 of a 3-per-page report

  full_name  | gpa
--------------+------
 Arif Mahmud  | 3.90
 Sadia Afrin  | 3.88
 Nusrat Jahan | 3.75

The pagination formula is mechanical — OFFSET (page - 1) * size — but OFFSET pagination skips m rows by materializing them, so page 10,000 of a catalog pays for 30,000 discarded rows. High-volume systems graduate to keyset (seek) pagination: WHERE (gpa, full_name) < (previous_last_gpa, previous_last_name) ORDER BY ... LIMIT 3 — fetch after the last-seen key instead of counting from the top; it is the technique behind every "load more" button you have ever pressed. Either way, the non-negotiable rule stands: no ORDER BY, no pagination — without a total order, "page 2" is an arbitrary shuffle.

Chapter Summary

• INSERT names table and columns (always the column list), takes multi-row VALUES for datasets, SELECT for copies, obeys every constraint, and reports via RETURNING.
• SELECT's projection carries expressions and mandatory aliases; select named columns in code.
• UPDATE sets literals or self-referencing expressions over the WHERE's rows; DELETE removes them; both without WHERE hit every row — WHERE first, always.
• WHERE composes comparisons, IN, BETWEEN (inclusive), LIKE, and NULL tests with AND/OR/NOT; AND binds tighter than OR; a row survives only on exactly TRUE.
• LIKE's % and _ match case-sensitively in PostgreSQL, insensitively in MySQL by default; leading wildcards defeat B-tree indexes.
• NULL kit: IS NULL, COALESCE, IS DISTINCT FROM; NOT IN over a NULL-bearing list returns nothing — filter or switch to NOT EXISTS.
• ORDER BY sorts results, supports multiple keys, and orders NULLs differently per platform (PostgreSQL larger, MySQL smaller) — NULLS LAST or a NULL-discriminator expression for portability.
• DISTINCT deduplicates whole rows and costs a sort/hash; COUNT(DISTINCT) counts instead of listing.
• LIMIT/OFFSET paginate deterministically only with ORDER BY; keyset pagination scales where OFFSET does not.

Key Terms

Laboratory Exercises

1. In university_dev, run Section 10.1's inserts (department 6 and its two courses), then verify with a three-column query joining course to department, and finish by attempting a duplicate dept_id 6 insert to capture the constraint error. Expected results: 2 course rows showing Physics; duplicate-key violation naming the primary key.
2. Load a dataset: use a multi-row INSERT to add two Physics sections in Spring 2027 (choose room and capacity values; instructor NULL), then an INSERT ... SELECT that enrolls every student currently enrolled in a Fall 2026 CSE section (sections 12 and 13) into the first new section. Verify enrollment counts before and after. Expected result: 3 rows copied — Arif Mahmud, Shahriar Islam, and Zara Hossain are the distinct students in sections 12 and 13.
3. Predict-then-run practice: write each of the four DML statements of Sections 10.3–10.4, predict the affected row count on paper first, then run with RETURNING (PostgreSQL) or in a transaction with a verifying SELECT (MySQL), and compare. Expected result: every prediction matches the returned/verified count.
4. Run the %ah% and _SE% LIKE queries, then portability practice: rewrite %ah% case-insensitively three ways — ILIKE (PostgreSQL), LOWER/LIKE, and plain LIKE in MySQL — and confirm identical row sets. Expected results: 3 students (Nusrat Jahan, Arif Mahmud, Shahriar Islam); 4 CSE courses; all three rewrites return the same 3 names.
5. Demonstrate the NULL rules: run the NOT IN trap query of Section 10.8 (zero rows), fix it with an inner WHERE grade IS NOT NULL, and note the result; then run both platforms' ORDER BY gpa DESC over all 12 students and explain the differing position of Zara Hossain. Expected results: the trap query returns 0 rows; adding WHERE grade IS NOT NULL inside the subquery empties the list, making NOT IN true for every row — 2 rows (section 12's enrollments: Arif Mahmud and Shahriar Islam); Zara Hossain sorts first in PostgreSQL, last in MySQL.
6. Build pagination three ways on the GPA report: page 2 (rows 4–6) with LIMIT/OFFSET on both platforms, the standard FETCH form in PostgreSQL, and the keyset form starting after (3.75, 'Nusrat Jahan'). Expected result: Mehjabin Chowdhury (3.70), Sumaiya Tabassum (3.60), Tanvir Alam (3.55) — the same three rows from all three forms.

Review Questions and Exercises

1. Why must production INSERTs always carry a column list? Column-order drift and schema additions otherwise silently misalign values; the list pins meaning regardless of table order.
2. Write the statement that adds student 21100007, 'Ayesha Rahman', major 3, admitted 2024, no credits yet, no GPA.

   INSERT INTO student (student_id, full_name, major_dept_id,
                        admission_year, total_credits, gpa)
   VALUES (21100007, 'Ayesha Rahman', 3, 2024, 0, NULL);

3. What does UPDATE student SET gpa = gpa + 0.10 WHERE admission_year = 2023; change, and how many rows? Raises each 2023 admit's GPA by 0.10 — Sumaiya Tabassum (3.60), Arif Mahmud (3.90), Nabil Khan (3.05) — three rows, since all three have non-NULL GPAs.
4. Predict the rows: DELETE FROM enrollment WHERE grade IS NULL AND section_id IN (11, 12); Six rows — the in-progress enrollments of sections 11 (four) and 12 (two).
5. Why does WHERE gpa <> NULL return no rows, and what are the two correct spellings? *Comparison with NULL is UNKNOWN, and WHERE keeps only TRUE; use gpa IS NOT NULL or gpa IS DISTINCT FROM NULL.*
6. Evaluate on paper for a row with gpa NULL: NOT (gpa > 3.0 OR admission_year = 2026). gpa > 3.0 is UNKNOWN; UNKNOWN OR TRUE is TRUE; NOT TRUE is FALSE — the row is discarded. For a NULL-GPA row from another year, UNKNOWN OR FALSE is UNKNOWN, and NOT UNKNOWN stays UNKNOWN — also discarded.
7. Explain the difference between LIKE '%data%' and LIKE 'data%' as filter and as index user. Anywhere vs. prefix match; the leading wildcard cannot use a B-tree index, so it forces a full scan on large tables.
8. State each platform's default NULL placement for ascending and descending sorts. PostgreSQL — NULLs last in ASC, first in DESC (NULL is largest); MySQL — first in ASC, last in DESC (NULL is smallest).
9. Write a portable NULLs-last descending GPA sort without NULLS LAST syntax.

   SELECT full_name, gpa
   FROM   student
   ORDER  BY gpa IS NULL, gpa DESC;

10. How many rows does SELECT DISTINCT major_dept_id, admission_year FROM student; return, and which pair repeats? 11 — the 12 students contain one duplicate pair, (1, 2021): Nusrat Jahan and Rakib Hasan.
11. Give the pagination formula for page p of size s and the condition under which OFFSET pagination becomes expensive. OFFSET (p-1)s, LIMIT s, over a deterministic ORDER BY; cost grows with offset because skipped rows are still produced and discarded — page 10,000 pays for everything before it.*
12. Why is SELECT * acceptable in an EXISTS probe but discouraged in application queries? EXISTS only tests row presence — the columns are never fetched; application code reading explicit columns survives schema additions and stays self-documenting.

Mini-Project

Run a full registration cycle in university_dev, as the registrar's office would. Script registration_cycle.sql with: (1) a comment-predicted, then executed sequence — admit two new students (choose IDs consistent with the admission-year convention), open one new section of an existing course for Spring 2027 with a chosen room, capacity, and instructor, enroll the new students plus one existing student, and assign a grade to one completed enrollment; (2) after every mutating statement, a verification query with its expected output in a comment (row counts, the new rows themselves); (3) a mistake section — deliberately run one unscoped UPDATE and one NOT IN trap inside a transaction, capture the effects (13 rows updated; zero rows), then ROLLBACK both; (4) a final report: the top-5 GPA leaderboard with portable NULL handling and keyset pagination starting after position 3, plus per-department student counts via DISTINCT. Reload university_dev from Appendix H afterwards and confirm the six canonical counts — your script must leave the dev database exactly as it found it, which is itself the exercise.