Views in SQL

Lecture 5 · sometimes it is not desirable for every user to see the entire logical model. A view is a mechanism to hide certain data from certain users — a stored query that behaves like a table.

Why views exist

Consider a person who needs an instructor’s name and department but not the salary. That person should see the relation described by select ID, name, dept_name from instructor — and nothing more.

Base table: instructor

ID	name	dept_name	salary
1	Abdur	CSE	10000
2	aman	CSE	12500
3	bilal	EEE	17000
4	morshed	EEE	12000
5	rashid	BBA	10000
6	irshad	BBA	7800

The red column is what the view must hide.

View definition

create view v as < query expression >

where <query expression> is any legal SQL expression and v is the view name. Once defined, the view name refers to the virtual relation the view generates.

NOTE! Creating a view does not create a new physical table/relation that stores data. A view is mainly a stored query (a virtual table).

create view

create view faculty as
    select ID, name, dept_name
    from instructor;

faculty (virtual)

ID	name	dept_name
1	Abdur	CSE
2	aman	CSE
3	bilal	EEE
4	morshed	EEE
5	rashid	BBA
6	irshad	BBA

How it works when you query a view

query on the view

-- Find all instructors in the CSE department
select name
from faculty
where dept_name = 'CSE';

Query names the view `faculty`

DBMS looks up the STORED view query

select ID, name, dept_name from instructor

Step 1 — execute it against the base tables to get the view relation

Step 2 — execute the user’s query on that relation

where dept_name = 'CSE'

Result: Abdur, aman

A view is expanded (rewritten) into the underlying SELECT — the data always comes from the base tables.

A view with aggregation

aggregate view

create view departments_total_salary (dept_name, total_salary) as
    select dept_name, sum(salary)
    from instructor
    group by dept_name;

select * from departments_total_salary;

dept_name	total_salary
CSE	22500
EEE	29000
BBA	17800

Naming the view’s columns in brackets — (dept_name, total_salary) — is how you give a readable name to an expression like sum(salary).

Views defined using other views

view on a view

create view faculty_by_dept as
    select dept_name, count(name) as total_faculty
    from faculty          -- ← the view we created earlier
    group by dept_name;

Dependency terminology. v₁ depends directly on v₂ if v₂ is used in the expression defining v₁. v₁ depends on v₂ if it depends directly, or through a path of dependencies. A view is recursive if it depends on itself.

Updating through a view

insert into a view

insert into faculty values ('30765', 'Green', 'Music');

This must be represented as an insert of ('30765','Green','Music', null) into instructor — the salary the view hides becomes null.

Some updates cannot be translated uniquely

ambiguous update

create view instructor_info as
    select ID, name, building
    from instructor, department
    where instructor.dept_name = department.dept_name;

insert into instructor_info values ('69987', 'White', 'Taylor');

Which department, if multiple departments are in Taylor?
What if no department is in Taylor?

Most SQL implementations allow updates only on “simple” views:

the from clause has only one database relation;
the select clause contains only attribute names — no expressions, aggregates or distinct;
any attribute not listed in select can be set to null;
the query has no group by or having.

…and some not at all

the disappearing row

create view history_instructors as
    select * from instructor where dept_name = 'History';

-- What happens if we insert ('25566','Brown','Biology',100000)?

The row is legally inserted into instructor, but it immediately vanishes from the view because it fails the view’s predicate. WITH CHECK OPTION exists exactly to reject such inserts.

Materialized views

Materializing a view = create a physical table containing all the tuples in the result of the query defining the view. If the underlying relations are updated, the materialized result becomes out of date, so the system must maintain it — updating the view whenever the base relations change.

	Ordinary view	Materialized view
Storage	None — just a stored query	A real physical table
Read speed	Recomputed each time	Fast — already computed
Freshness	Always current	Can go stale; needs maintenance
Best for	Security & simplification	Expensive aggregations read often

Three reasons to use a view: security (hide salary), simplification (bury a 4-table join behind one name), and logical data independence (the base schema can change while the view keeps its old shape for existing applications).

Next: Authorization on views →