Views and WITH

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. How is "for all" written in SQL? from Lesson 3.3.9
2. What is an external schema in the three-schema architecture? from Lesson 0.1.3
3. Which clause filters groups? from Lesson 3.3.5

1 · The Hook

The exam cell should see only names and CGPAs, never phone numbers. A long query is used in ten reports. In both cases you want to give a query a name and use it like a table. SQL offers views (stored, reusable) and WITH (temporary, inside one query).

2 · Predict First

A view CS_STUDENTS is defined as "SELECT roll, name FROM STUDENT WHERE dept = 'CS'". A new CS student is inserted into STUDENT. Does the view show her?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

Yes. A (normal) view stores the query, not the rows. Every time the view is used, the query runs on the current data.

Views

CREATE VIEW CS_STUDENTS AS
  SELECT roll, name, cgpa FROM STUDENT WHERE dept = 'CS';

SELECT name FROM CS_STUDENTS WHERE cgpa > 9;    -- used like a table: Asha
DROP VIEW CS_STUDENTS;

• A view is a virtual table: only its definition is stored in the catalog.
• Uses: security (show only some columns or rows), simplicity (hide joins), logical data independence (Lesson 0.1.3: external schemas are views).
• A materialised view stores the result physically and must be refreshed when base tables change. It is faster to read but can be stale.

Updating through a view

An INSERT, UPDATE or DELETE on a view must be translated into a change of base tables. Standard SQL allows it only for simple views, typically:

• defined on one base table,
• no aggregates, GROUP BY, HAVING or DISTINCT,
• the select list includes enough columns (the key and all NOT NULL columns without defaults) to build a base row.

A view with a join or with AVG cannot be updated: AVG(cgpa) = 9 has no single meaning in base rows.

WITH CHECK OPTION: rejects inserts or updates through the view that would produce rows the view cannot see (inserting a non-CS student through CS_STUDENTS).

WITH (common table expressions)

WITH dept_avg AS (
  SELECT dept, AVG(cgpa) AS a FROM STUDENT GROUP BY dept
)
SELECT S.name FROM STUDENT S JOIN dept_avg D ON S.dept = D.dept
WHERE S.cgpa > D.a;                                   -- Asha

A WITH table exists only for that one query. It makes long queries readable and replaces repeated subqueries.

4 · Visual

CREATE VIEW CS_STUDENTS AS SELECT roll, name, cgpa FROM STUDENT WHERE dept='CS'

catalog stores:   the query text           (normal view: no rows stored)
on each use:      query runs on STUDENT -> rows of the moment

materialised view: rows stored, refresh needed
WITH x AS (...):   name lives only inside one statement

5 · Worked Examples

Example 1 Easy

Is a view's data stored on disk?

Solution

For a normal view, no: only its definition. A materialised view does store its rows.

Example 2 Medium

Which view is updatable? V1 = SELECT roll, name FROM STUDENT WHERE cgpa > 8; V2 = SELECT dept, COUNT(*) FROM STUDENT GROUP BY dept.

Solution

V1 (one table, no aggregation, includes the key). V2 uses GROUP BY and COUNT: not updatable.

Example 3 GATE

Through CS_STUDENTS (defined WITH CHECK OPTION) someone runs INSERT INTO CS_STUDENTS VALUES (9, 'Kunal', 8.0). What dept does the base row get, and is the insert accepted?

Solution

dept is not in the view, so the base row gets dept = NULL (or its default). The new row has dept ≠ 'CS', so the view could not see it: WITH CHECK OPTION rejects it. Without the option it would be accepted and silently invisible through the view.

8 · Connect the Dots

Chapter 3.3 is complete: DDL, DML, SELECT, NULLs, aggregates, joins, set operators, subqueries, views. Next: the Chapter 3.3 Lock-in, then one drill lesson writing the same query in RA, TRC and SQL.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Views

A normal (non-materialised) view stores:

1. (A) an index on its base table
2. (B) its defining query
3. (C) a copy of its rows
4. (D) nothing at all

Q2 · MCQ · 1 mark(s) — Updatability

Which view is generally not updatable?

1. (A) SELECT cid, title FROM COURSE
2. (B) SELECT roll, name FROM STUDENT
3. (C) SELECT dept, AVG(cgpa) FROM STUDENT GROUP BY dept
4. (D) SELECT * FROM COURSE WHERE credits = 4

Q3 · MCQ · 1 mark(s) — CHECK OPTION

WITH CHECK OPTION on a view:

1. (A) refreshes a materialised view
2. (B) rejects changes through the view that create rows the view would not show
3. (C) makes the view read-only
4. (D) checks the base table's CHECK constraints only

Q4 · MSQ · 2 mark(s) — WITH

Which are true of WITH (common table expressions)?

1. (A) It can be joined with base tables in the main query.
2. (B) It permanently creates a table.
3. (C) The named result exists only for one statement.
4. (D) It can make a long query more readable.

Q5 · NAT · 1 mark(s) — View query

CS_STUDENTS = SELECT roll, name, cgpa FROM STUDENT WHERE dept = 'CS'. How many rows does SELECT * FROM CS_STUDENTS return on the 5-row STUDENT table?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

view (virtual table)CREATE VIEWDROP VIEWmaterialised viewupdatable viewWITH CHECK OPTIONWITH (common table expression)