Assignement 8 Views

VIEWS & SET OPERATIONS

⭐ What is a View?

A view is a logical table based on one or more base tables.
A view:

• does not store data physically

• stores only the SELECT query in the data dictionary

• retrieves data from base tables whenever accessed

• can be used to restrict or simplify data access

⭐ Advantages of Views

1. Restricts data access

A view can show only selected columns → useful for security.

2. Simplifies complex queries

Joins or calculations can be prewritten inside the view.

3. Data independence

Application developers can use the view without modifying base tables.

4. Customized view of data

Different users can see different data depending on their needs.

⭐ Syntax for Creating a View

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];

Important Keywords

• OR REPLACE → recreate view if it already exists

• FORCE → create view even if base table doesn't exist

• NOFORCE → create only if base table exists (default)

• WITH CHECK OPTION → prevents updates that make rows invisible to view

• WITH READ ONLY → no DML operations allowed (INSERT/UPDATE/DELETE)

📘 SET OPERATIONS (Quick Note)

Set operations combine results of two SELECT statements:

✅ ASSIGNMENT SOLUTIONS

Assuming table name: EMPLOYEES

Columns used:
employee_id, first_name, last_name, department_id

1️⃣ Create view Emp_vu with emp number, name, dept no

CREATE VIEW Emp_vu AS
SELECT employee_id, first_name, department_id
FROM employees;

2️⃣ Display the contents of Emp_vu

SELECT * FROM Emp_vu;

3️⃣ Select the view name and text from USER_VIEWS

SELECT view_name, text
FROM user_views
WHERE view_name = 'EMP_VU';

4️⃣ Using Emp_vu, display employee names and dept numbers

SELECT first_name, department_id
FROM Emp_vu;

5️⃣ Create view DEPT50 for employees in dept 50

Requirements:
✔ Columns renamed → EMPNO, EMPLOYEE, DEPTNO
✔ Prevent changing department → use WITH CHECK OPTION

CREATE OR REPLACE VIEW Dept50 (EMPNO, EMPLOYEE, DEPTNO)
AS
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT dept50_check;

6️⃣ Display structure and contents of DEPT50 view

Structure:

DESC Dept50;

Data:

SELECT * FROM Dept50;

7️⃣ Attempt to reassign any employee in DEPT50 to department 80

This must fail because of WITH CHECK OPTION.

Test with:

UPDATE Dept50
SET DEPTNO = 80
WHERE EMPNO = 100;

Expected Output:

ERROR: cannot update DEPTNO because of WITH CHECK OPTION