Views: saved queries

Writing the same complex query again and again is tiresome. A VIEW is a saved query: you use it like a table, but it does not store any data itself.

Creating one

CREATE VIEW student_cards AS
SELECT s.id, s.first_name, s.last_name, g.name AS group_name, f.name AS faculty
FROM students s
JOIN study_groups g ON g.id = s.group_id
JOIN faculties f ON f.id = g.faculty_id;

Now, just like a table:

SELECT * FROM student_cards WHERE faculty = 'Kiberxavfsizlik';

Every time you use it, the query inside runs again — so the result is always current.

Why use views

• simplicity — the complex joins are written once;
• security — a user is given the VIEW rather than the whole table (for example, without the salary column);
• stability — if the tables change, you adjust the VIEW and the applications keep working as before.

Changing and removing

CREATE OR REPLACE VIEW student_cards AS SELECT …;
DROP VIEW student_cards;

Changing data through a view

Through a simple VIEW (one table, no grouping) you can even run UPDATE — the change goes into the underlying table. Views with joins, GROUP BY or DISTINCT are usually read-only.

Materialised views

In PostgreSQL a MATERIALIZED VIEW stores its result — for very heavy reports:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', ordered_at) AS month, count(*) FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;   -- bring it up to date

Reading is fast, but the result stays as it was until you REFRESH.

Exercise

In free play, load the ready-made pharmacy database, create a view “every sale: customer name, medicine name, amount”, then SELECT from it.

Remember

• A VIEW is a named, saved query that stores no data.
• It simplifies complex queries and hides columns people should not see.
• A materialised view (PostgreSQL) stores the result and is updated with REFRESH.