Intermediate SQL

Views

Why Views?

Not everyone should see the full picture. Suppose someone needs an instructor’s name and department, but not their salary. Instead of trusting every query to remember to hide the salary column, define it once:

select id, name, dept_name from instructor;

A view is exactly this — a saved query that behaves like a “virtual table.” It’s not real stored data; it’s a name attached to a query expression that gets substituted in wherever the view is used.

instructor

id, name, dept_name, salary

→

view

select id, name, dept_name

→

user query

sees only 3 columns

Creating a View

create view v as <query expression>;

-- A view of instructors without salary
create or replace view faculty as
select id, name, dept_name from instructor;

-- Use it like a table
select name from faculty where dept_name = 'Biology';

-- A view with computed columns
create or replace view dept_salary_totals(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name;

select * from dept_salary_totals;

Views Built on Views

Views can refer other views, not just base tables:

create or replace view physics_fall_2006 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
  and course.dept_name = 'Physics'
  and section.semester = 'Fall'
  and section.year = 2006;

create or replace view physics_fall_2006_taylor as
select course_id, room_number
from physics_fall_2006
where building = 'Taylor';

select * from physics_fall_2006_taylor;

• v1 depends directly on v2 if v2 appears in v1’s definition.
• v1 depends on v2 if there’s any chain of dependency leading from v1 to v2.
• A view is recursive if it depends on itself.

View Expansion

To understand what a nested view really means, SQL conceptually expands it: repeatedly replace each view name with its underlying query, until only base tables remain.

repeat:
    find a view reference v_i in the expression
    replace v_i with v_i's defining query
until no view references remain

physics_fall_2006_taylor

the query you write

→

physics_fall_2006

replaced by its definition

→

course, section

only base tables remain

This terminates as long as no view is recursive.

Updating Views

Inserting into a view secretly inserts into the underlying table:

begin;
insert into faculty values ('30765', 'Green', 'Biology');
-- Translates to:
-- insert into instructor values ('30765', 'Green', 'Biology', null);
select * from instructor where id = '30765';
rollback;

But this only works cleanly for simple views — most databases require:

• from clause has exactly one base table
• select lists plain column names only (no expressions, aggregates, or distinct)
• Any column left out of the select can default to null
• No group by or having

If a view joins multiple tables, an insert can be ambiguous (e.g., which department’s row gets the new value if several departments share a building?) — and some inserts that satisfy the view’s output can violate its where clause entirely, making the update meaningless to translate.

Try inserting into the dept_salary_totals view (it has group by), and the database refuses:

insert into dept_salary_totals values ('Music', 100000);

Materialized Views

A materialized view physically stores the view’s result as a real table, instead of recomputing it on every query — faster to read, but it goes stale the moment the underlying tables change. Keeping it correct requires actively maintaining it (refreshing it) whenever the source data updates.

View Type	Storage	Speed	Freshness
Regular View	None (just a saved query)	Recomputed every time	Always current
Materialized View	Physical table	Fast to read	Needs manual/scheduled refresh

begin;
create materialized view dept_count as
select dept_name, count(*) as num_instructors
from instructor group by dept_name;

insert into instructor values ('30765', 'Green', 'Biology', 72000);
select * from dept_count where dept_name = 'Biology';   -- still the old count

refresh materialized view dept_count;
select * from dept_count where dept_name = 'Biology';   -- now updated
rollback;

create

Biology: 2 stored

→

insert into instructor

table has 3, view still says 2

→

refresh

view shows 3

Transactions

A transaction is a unit of work — a group of one or more SQL statements that should succeed or fail as a whole.

• Atomic: either the entire transaction completes, or none of it does (rolled back as if it never happened).
• Isolated: a transaction shouldn’t see the messy, half-finished state of another transaction running at the same time.
• Transactions begin implicitly, and end with either:
  • commit work — make all changes permanent
  • rollback work — undo everything since the transaction began

beginstart the unit of work

statementsqueries and updates

commit workmake changes permanent

rollback workundo everything

begin;
update instructor set salary = salary * 1.10 where dept_name = 'Physics';
select name, salary from instructor where dept_name = 'Physics';   -- raised
rollback work;
select name, salary from instructor where dept_name = 'Physics';   -- back to the original

---

Integrity Constraints

Constraints stop valid-looking but wrong changes from silently corrupting the database — e.g., “an instructor’s salary must be greater than 29000” or “every instructor must have a name.”

On a Single Relation

Constraint	Purpose
not null	Column can never be empty
primary key	Uniquely identifies each row; implies not null + unique
unique (A1, ..., Am)	Columns form a candidate key — but unlike primary keys, candidate keys can be null
check(P)	Row must satisfy predicate P

The university dataset already has these constraints. Each insert below breaks one of them, so the database rejects it:

-- not null: name is required
insert into instructor values ('30767', null, 'Biology', 50000);

-- check: salary > 29000
insert into instructor values ('30768', 'Low', 'Biology', 20000);

-- check: semester in ('Fall', 'Winter', 'Spring', 'Summer')
insert into section values ('105', '9', 'Autumn', 2009, null, null, null);

Creating a new table with a check constraint (in the practice workspace):

drop table if exists fixture;
create table fixture (
    match_num varchar(5),
    status varchar(10),
    primary key (match_num),
    check (status in ('Scheduled', 'Played', 'Postponed', 'Cancelled'))
);
insert into fixture values ('M0001', 'Played');      -- accepted
select * from fixture;

A value outside the list is rejected by the check:

insert into fixture values ('M0002', 'Abandoned');

---

Referential Integrity

Ensures a value referenced in one table actually exists in the table it points to. If 'Biology' appears as a department name in instructor, there must be a matching row for 'Biology' in department.

Formal definition: If A is the primary key of relation S, then A is a foreign key of relation R if every value of A appearing in R also appears in S.

-- 'Music' is not in department, so this is rejected
insert into instructor values ('30769', 'Lee', 'Music', 50000);

Cascading Actions

What should happen when a referenced row is deleted or updated?

drop table if exists project_cascade;
drop table if exists project;
drop table if exists department;

create table department (
    dept_name varchar(20) primary key,
    building varchar(15)
);

create table project (
    project_id char(5) primary key,
    title varchar(20),
    dept_name varchar(20) references department
);

-- with explicit cascading behavior
create table project_cascade (
    project_id char(5) primary key,
    title varchar(20),
    dept_name varchar(20),
    foreign key (dept_name) references department
        on delete cascade
        on update cascade
);

Action	Effect when referenced row is deleted/updated
cascade	Automatically delete/update matching rows too
no action	Block the delete/update if references exist (default)
set null	Set the foreign key to null
set default	Set the foreign key to its default value

The university dataset uses these actions. Deleting a department sets its instructors’ dept_name to null (set null), and deleting a student removes their takes rows (cascade):

begin;
delete from department where dept_name = 'Biology';
select id, name, dept_name from instructor where id in ('80759', '81991');
rollback;

The Chicken-and-Egg Problem

Self-referencing constraints can deadlock a single insert. Consider:

create table employee (
    id char(10),
    name char(40),
    manager_id char(10),
    primary key (id),
    foreign key (manager_id) references employee
);

How do you insert the very first employee if their manager must already exist? Three options:

1. Insert the manager before the employee who reports to them.
2. Set manager_id to null initially, then update it later (impossible if the column is not null).
3. Defer constraint checking until the transaction commits (covered in advanced topics).

---

SQL Data Types and Schemas

Built-in Date/Time Types

Type	Stores	Example
date	Year, month, day	date '2025-7-27'
time	Hours, minutes, seconds	time '09:00:30'
timestamp	Date + time together	timestamp '2025-7-27 09:00:30.75'
interval	A span of time	interval '1' day

NoteKey idea

Subtracting two dates/times gives you an interval; adding an interval to a date/time shifts it forward or backward.

select match_num, match_date,
       match_date + interval '7 days' as rematch_date,
       date '2020-12-31' - match_date as days_to_year_end
from matches;

Indexes

An index is a shortcut structure that lets the database jump straight to matching rows instead of scanning the whole table.

create index if not exists student_name_index on student(name);

select * from student where name = 'Manber';
-- with the index above, this skips a full table scan

User-Defined Types (UDT)

Like a typedef in C — gives a meaningful name to an existing type.

create type dollars as numeric(12,2) final;

create table department (
    dept_name varchar(20),
    building varchar(15),
    budget dollars
);

NoteNote

This create type … final form is standard SQL and does not run in PostgreSQL. A domain (next) does the same job and runs on the dataset.

Domains

Similar to UDTs, but domains can carry their own constraints.

drop table if exists visitor;
drop domain if exists person_name;
drop domain if exists member_kind;

create domain person_name varchar(20) not null;

create domain member_kind varchar(10)
    constraint member_kind_test
    check (value in ('Student', 'Faculty', 'Staff'));

create table visitor (name person_name, kind member_kind);
insert into visitor values ('Asha', 'Student');   -- accepted
select * from visitor;

A value the domain does not allow is rejected:

insert into visitor values ('Ravi', 'Guest');

Large Object Types

For storing big, unstructured content like photos, videos, or files:

Type	Stores
blob	Binary Large OBject — raw binary data
clob	Character Large OBject — large character data

NoteNote

A query returns a pointer to a large object, not the object itself — the actual data is fetched separately.

---

Authorization

Forms of Authorization

On data:

Privilege	Allows
read	Viewing data only
insert	Adding new rows
update	Modifying existing rows
delete	Removing rows

On schema:

Privilege	Allows
index	Creating/dropping indices
resources	Creating new relations
alteration	Adding/removing columns
drop	Deleting relations entirely

Granting Privileges

grant <privilege list> on <relation or view> to <user list>;

<user list> can be a specific user, public (everyone), or a role.

begin;
create role u1;  create role u2;  create role admin_user;
grant select on instructor to u1, u2;
grant insert, update on course to u1;
grant all privileges on department to admin_user;
rollback;

NoteNote

The grantor must already hold the privilege themselves (or be the DBA). Granting a privilege on a view does not automatically grant privileges on the tables underneath it.

Revoking Privileges

revoke <privilege list> on <relation or view> from <user list>;

begin;
create role u1;  create role u2;
grant select on department to u1, u2;
revoke select on department from u1, u2;
rollback;

• revoke all from ... removes every privilege the user holds.
• Revoking from public removes the privilege from everyone except users who were granted it explicitly elsewhere.
• If the same privilege was granted by two different grantors, revoking from one grantor may leave the privilege intact (granted by the other).
• Revoking a privilege also revokes any privileges that depended on it.

Roles

Roles bundle privileges so you don’t have to grant the same set repeatedly.

teaching_assistant

privileges of a TA

→

instructor

gets TA privileges too

→

dean

gets instructor and TA privileges

→

satoshi

inherits the whole chain

begin;
create role instructor_role;
create role amit;
grant select on takes to instructor_role;
grant instructor_role to amit;       -- amit inherits the privileges of 'instructor_role'

-- Roles can inherit from other roles
create role teaching_assistant;
grant teaching_assistant to instructor_role;   -- instructor_role now also gets TA's privileges

-- Chains of roles
create role dean;
create role satoshi;
grant instructor_role to dean;
grant dean to satoshi;          -- satoshi inherits dean → instructor_role → TA
rollback;

Authorization on Views

begin;
create view geo_instructors as
    (select * from instructor where dept_name = 'Geology');

create role geo_staff;
grant select on geo_instructors to geo_staff;
rollback;

If geo_staff queries geo_instructors, it works even if geo_staff has no direct permission on the underlying instructor table — the view’s permissions are what matter, not the creator’s original access (though if the view’s creator lacked permission on instructor, the view itself couldn’t have been created correctly in the first place).

Other Authorization Features

begin;
create role mariano;  create role amit;  create role satoshi;

-- 'references' privilege needed to create a foreign key pointing at someone else's table
grant references (dept_name) on department to mariano;

-- Transferable privileges
grant select on department to amit with grant option;

-- Revoking with dependency handling
revoke select on department from amit, satoshi cascade;   -- also revokes anything amit/satoshi granted onward
rollback;

revoke ... restrict instead blocks the revoke if dependent privileges exist.

NoteKey idea

The references privilege exists because a foreign key constraint can restrict what the referenced table’s owner is allowed to do (e.g., blocking a delete due to on delete no action) — so creating that link requires explicit permission.