Lecture 4 — Introduction to SQL

SQL end-to-end: DDL & domain types, CREATE TABLE + constraints, INSERT/UPDATE/DELETE with CASE, the SELECT–FROM–WHERE query, string & ordering ops, aggregates with GROUP BY/HAVING, nulls, set operations, and nested subqueries.

Sections

🏗️

DDL, Domains & CREATE TABLE

SQL history, domain/data types, the CREATE TABLE construct, and integrity constraints.

✏️

INSERT · UPDATE · DELETE

Row-level DML, the CASE update, and how ALTER/DROP differ (DDL vs DML).

🔎

SELECT–FROM–WHERE

Basic query structure, DISTINCT/AS, WHERE predicates, LIKE, BETWEEN, ORDER BY & joins via FROM.

📊

Aggregates, GROUP BY & Nulls

avg/min/max/sum/count, GROUP BY vs HAVING, and null values with three-valued logic.

🧩

Set Operations & Subqueries

union/intersect/except plus nested subqueries: IN, SOME/ALL, EXISTS, scalar & WITH.

Quick summary

DDL: CREATE TABLE r (A1 D1, …, constraints). Domains: INT · DECIMAL(p,s)
   · CHAR(n)/VARCHAR(n) · DATE/TIME/TIMESTAMP · BOOLEAN · BLOB.
   Constraints: NOT NULL · PRIMARY KEY (auto not-null) · FOREIGN KEY … REFERENCES.

DML: INSERT · UPDATE (… SET … WHERE, order matters → use CASE) · DELETE.
   ALTER=structure · DROP=whole object (both DDL).

QUERY:  select A1,… from r1,… where P   (=  π then × then σ).
   distinct removes dups · AS renames · LIKE %/_ · BETWEEN · ORDER BY … asc/desc.
   from = Cartesian product; add where join condition to make it useful.

AGGREGATES: avg/min/max/sum/count. GROUP BY (non-agg cols must be grouped).
   HAVING filters GROUPS (after); WHERE filters ROWS (before). count(*) counts nulls.

NULL = unknown; any op with null → null; 3-valued logic (true/false/unknown).
SET OPS: union / intersect / except (auto dedupe; …all keeps dups).
SUBQUERIES: in · not in · some · all · exists · not exists · scalar · WITH.