Oracle SQL Certification 1Z0-071 Course | Section 13: Managing Views

In this section, each lesson builds on the previous one, so make sure to follow them in order and experiment with the examples alongside your videos.

Lesson 1: What Is a View and How to Create One

A view is a saved SQL query that behaves like a virtual table. It does not store data itself but retrieves it from underlying tables whenever you query it.

This makes views very useful for simplifying complex queries that include joins, filters, or calculated columns.

Why Use Views

Views allow you to:

• Simplify complex SQL logic
• Reuse frequently written queries
• Restrict access to specific data
• Improve readability and maintainability

Creating a View

CREATE VIEW employee_view AS
SELECT first_name, last_name, salary
FROM employees;

To overwrite an existing view:

CREATE OR REPLACE VIEW employee_view AS
SELECT first_name, last_name, salary
FROM employees;

Key Rules

• Expressions must use column aliases
• Views can include joins, filters, and calculations
• You query a view the same way as a table

Lesson 2: Updatable Views

Views can sometimes be used to modify data, but only when certain conditions are met.

What You Can Do

If the view is simple enough, you can:

• Update data
• Delete rows
• Insert data in some cases

Changes made through a view directly affect the base table.

When Views Are Not Updatable

A view cannot be updated if it contains:

• Expressions such as concatenated columns
• Missing required columns with NOT NULL constraints
• DISTINCT
• GROUP BY or aggregate functions
• Subqueries
• Joins in most cases

Important Insight

Insert operations often fail because the view does not include all required columns from the base table.

Lesson 3: Inline Views

Inline views are subqueries placed inside the FROM clause and treated like temporary tables.

Why Use Inline Views

They help you:

• Break complex queries into smaller steps
• Improve readability
• Apply transformations in stages

Example

SELECT *
FROM (
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Key Points

• Ordering inside inline views matters when using ROWNUM
• Without ordering, results can be unpredictable
• You can join multiple inline views
• Oracle allows up to 255 inline views in one query

Lesson 4: Modifying Views

Views cannot be directly modified using a simple ALTER statement for their query definition.

How to Modify a View

You must recreate it:

CREATE OR REPLACE VIEW employee_view AS
SELECT first_name, salary
FROM employees;

ALTER VIEW Usage

ALTER VIEW employee_view COMPILE;

Used for:

• Recompiling invalid views
• Managing certain constraints

Important Notes

• Views may become invalid if underlying tables change
• You may need to recompile or recreate them
• In some cases, dropping and recreating is the best approach

Lesson 5: Invisible and Visible Columns in Views

Oracle allows columns to be marked as invisible, which affects how they behave in queries.

Invisible Columns

• Not returned with SELECT *
• Still exist in the table
• Must be explicitly selected

Insert Behavior

Insert statements can fail if:

• Invisible columns are required
• Values are not explicitly provided

Views and Column Visibility

Views allow you to control what is visible:

• You can include invisible columns explicitly
• You can hide columns intentionally
• Using explicit column lists is safer than SELECT *

---

Final Thoughts

Views are essential for both the Oracle SQL exam and real-world database work.

They help you simplify queries, control access to data, and organize your SQL in a clean and reusable way.

Make sure to practice each lesson, especially creating and modifying views, and testing when views are updatable.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?