Assignement 8 Views
SQL Views & Set Operations: CREATE VIEW, WITH CHECK OPTION & More
Topic: Understanding SQL Views — how to create, use, and restrict them — along with a quick overview of Set Operations like UNION, INTERSECT, and MINUS.
If you've ever wanted to simplify a complex query, restrict what data certain users can see, or reuse a SELECT statement like a table — SQL Views are exactly what you need. This guide covers everything from the syntax to real assignment queries, so you can revise quickly and write with confidence.
What Is a View in SQL?
A view is a logical table based on one or more base tables. It behaves like a regular table when you query it, but under the hood it's just a stored SELECT statement — no data is physically saved.
Key characteristics of 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
💡 Think of a view as a "saved query with a name" — every time you query it, it runs the underlying SELECT fresh from the base tables.
For deeper reference, see the Oracle documentation on Views.
Advantages of Views
1. Restricts data access A view can expose only selected columns — useful for security when different teams need different access levels.
2. Simplifies complex queries Joins or calculations can be prewritten inside the view, so developers query a simple name instead of writing a 10-line JOIN every time.
3. Data independence Application developers can use the view without touching or even knowing the structure of base tables.
4. Customized view of data Different users can see different slices of the same data depending on their role or need.
Syntax for Creating a View
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];
Important Keywords Explained
Keyword | What It Does |
|---|---|
| Recreates the view if it already exists |
| Creates the view even if the base table doesn't exist yet |
| Creates the view only if the base table exists (default behavior) |
| Prevents updates that would make the row invisible to the view |
| Blocks all DML operations — no INSERT, UPDATE, or DELETE allowed |
Set Operations in SQL — Quick Reference
Set operations let you combine the results of two SELECT statements. Both queries must return the same number of columns with compatible data types.
Operation | Purpose |
|---|---|
| Combines rows from both queries, removes duplicates |
| Combines rows from both queries, keeps duplicates |
| Returns only rows that appear in both queries |
| Returns rows from the first query that don't appear in the second |
For a detailed comparison with examples, GeeksforGeeks has a solid breakdown of SQL Set Operations.
Assignment 8 — All Queries With Expected Output
Table used:
EMPLOYEESColumns:employee_id,first_name,last_name,department_id
Query 1: Create View Emp_vu With Employee Number, Name, and Department
CREATE VIEW Emp_vu AS
SELECT employee_id, first_name, department_id
FROM employees;
This creates a simple view showing three columns. No restrictions — it's a basic read view.
Query 2: Display Contents of Emp_vu
SELECT * FROM Emp_vu;
Querying a view works exactly like querying a table. Oracle runs the stored SELECT internally and returns the result.
Query 3: Check View Definition in USER_VIEWS
SELECT view_name, text
FROM user_views
WHERE view_name = 'EMP_VU';
USER_VIEWS is a data dictionary table that stores metadata about all views owned by the current user. The text column shows the original SELECT query stored inside the view.
Query 4: Use Emp_vu to Display Names and Department Numbers
SELECT first_name, department_id
FROM Emp_vu;
You can select specific columns from a view just like from a regular table — no need to touch the base employees table directly.
Query 5: Create View DEPT50 With Column Aliases and WITH CHECK OPTION
Requirements: rename columns to EMPNO, EMPLOYEE, DEPTNO and prevent department changes.
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;
The WITH CHECK OPTION here ensures no UPDATE can move an employee out of department 50 — because that row would then disappear from the view, which the constraint blocks.
Query 6: View Structure and Contents of DEPT50
Structure:
DESC Dept50;
Data:
SELECT * FROM Dept50;
DESC shows column names and their data types. Useful for quickly verifying that your column aliases (EMPNO, EMPLOYEE, DEPTNO) were applied correctly.
Query 7: Attempt to Move a DEPT50 Employee to Department 80
This update should fail because of WITH CHECK OPTION.
UPDATE Dept50
SET DEPTNO = 80
WHERE EMPNO = 100;
Expected Output:
ERROR: cannot update DEPTNO because of WITH CHECK OPTION
This is the whole point of WITH CHECK OPTION — it enforces that any row modified through the view must still satisfy the view's WHERE condition after the update. Since department_id = 80 does not satisfy department_id = 50, Oracle rejects it.
Common Mistakes to Avoid
Confusing WITH CHECK OPTION and WITH READ ONLY —
WITH READ ONLYblocks all DML;WITH CHECK OPTIONallows DML but enforces the WHERE condition.Forgetting that views don't store data — if the base table is dropped, the view becomes invalid.
Using FORCE without knowing the risk — a FORCE view compiles but will throw errors at runtime if the base table is missing.
For a broader look at how views compare to other SQL concepts, W3Schools has a clear intro to SQL Views worth bookmarking.
Also Explore These Topics
Looking to build on this knowledge? Here are related notes worth checking out: