Lecture 5 — Intermediate SQL

Join expressions and outer joins, views (including materialized views), integrity constraints with cascading actions, SQL data types & domains, index creation, and authorization with GRANT/REVOKE and roles.

Sections

🔗

Join Expressions

Inner/theta join vs natural join, and left, right & full outer joins with every result table worked out.

👁️

Views

create view, how the DBMS rewrites a view query, views on views, the limits of updating a view, and materialized views.

🛡️

Integrity Constraints

not null, unique, check(P), referential integrity and the five cascading actions.

🗓️

Data Types, Domains & Indexes

date/time/timestamp/interval, create type & create domain, blob/clob, and CREATE INDEX.

🔐

Authorization

Data & schema privileges, GRANT/REVOKE with every variant, roles, and authorization on views.

Quick summary

JOINS: join = Cartesian product + match condition.
   inner join … on  → keeps BOTH copies of the common column, any predicate (theta).
   natural join     → matches same-named columns automatically, keeps ONE copy.
   OUTER JOINS avoid loss of information, pad the missing side with NULL:
      ⟕ left (all left rows) · ⟖ right (all right rows) · ⟗ full (both).
   "Which rows have no partner?" = outer join + IS NULL.

VIEWS: create view v as <query>. NOT a stored table — a STORED QUERY (virtual table).
   Used to HIDE data (security), simplify joins, and give logical data independence.
   Updatable only on simple views (one relation, no aggregate/distinct/group by).
   MATERIALIZED view = a real physical table → fast but can go stale, must be maintained.

INTEGRITY: single relation → not null · primary key · unique (may be NULL!) · check(P).
   Referential integrity → foreign key … references; subquery in CHECK is unsupported (use a trigger).
   CASCADING: on delete/update cascade · set null · set default · restrict/no action.

TYPES: date · time · timestamp · interval (date − date = interval).
   create type / create domain (domains can carry constraints) · blob & clob (pointer returned).

INDEX: create index i on T(col) — index stores the key + a row pointer.
AUTHORIZATION: grant <priv> on <rel> to <user|public|role>; revoke … from ….
   Column-level grants · WITH GRANT OPTION · REFERENCES · roles = named privilege sets.
   Granting on a VIEW grants nothing on the base tables.