Lesson 1

A view is a stored way of looking at the information in your tables. Once created, a view can be queried again and again, always reflecting the current state of the underlying data, rather than a one-time snapshot that goes stale the moment something changes.

This module covers four things in depth:

1. How to build and save views for reuse.
2. How to use views to tie related tables back together into a single, coherent result.
3. How to use views to narrow or limit the data a given user or application is allowed to see.
4. How views serve security purposes, restricting access to sensitive columns or rows without touching the underlying tables.

View syntax and behavior do vary somewhat between database engines, since a view is ultimately implemented by whatever engine is running underneath it. The core concept, a saved query presented as a table, is universal across every major relational database, but the specific clauses available, how updatability is determined, and how materialization works are all implementation details that differ from one product to the next. Where Oracle's specific behavior differs from the general concept, that difference will be called out explicitly throughout this module.

A view is a virtual table based on the result set of a SELECT statement. It has rows and columns, just like a real table, but those rows and columns are drawn from one or more underlying base tables rather than stored independently. A view isn't a table itself; it's a saved way of looking at part of one or more tables, which is exactly why a view's contents change automatically the moment the underlying data changes. The database re-executes the view's defining query every time the view is queried, so a view always shows current data, never a stale copy.

A simple view built on a single table can, in many cases, be used not just for retrieval but for updating and deleting rows too. A change made through such a view is really a change made to the underlying table; the view has no data of its own to modify.

Three benefits come up repeatedly, regardless of which specific engine you're running:

• Hiding complexity. A view can be built on a join across several tables, or on a query with real calculation logic behind it, and present the result as though it came from one single, simple table. The person querying the view doesn't need to know how to write that join or replicate those calculations themselves.
• Presenting data from a different angle. Columns can be renamed in a view without touching the underlying table at all, letting the view expose business-friendly names while the base table keeps whatever names it was originally given.
• Insulating applications from schema changes. If a view's defining query references three columns out of a four-column table, and a fifth column gets added to that table later, the view's definition is unaffected, and so is every application built on top of it. The view only reacts to changes that actually touch the columns it depends on.

That last point is worth seeing concretely. Suppose a view is defined against a four-column customers table:

CREATE VIEW customer_contacts AS
SELECT customer_id, first_name, last_name
FROM customers;

Later, someone adds a loyalty_points column to customers for an unrelated feature:

ALTER TABLE customers ADD loyalty_points NUMBER DEFAULT 0;

customer_contacts doesn't change at all. It still exposes exactly the three columns it always did, and every report or application built on top of it keeps working without modification. Only a change that touches customer_id, first_name, or last_name specifically, renaming one of them, or dropping one, would actually affect this view.

The general form of a view is:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Here's a concrete example, a view that exposes a subset of customer data, but only for customers with a credit limit of at least 1,000:

CREATE VIEW viwCustomerCreditLimits AS
SELECT cust_last_name, cust_first_name, credit_limit AS buy_limit
FROM Demo_Customers
WHERE credit_limit >= 1000
WITH READ ONLY;

This view exposes three columns, renaming credit_limit to the more descriptive buy_limit along the way, and only surfaces rows where credit_limit is 1,000 or more. It's worth naming a view for what it actually contains, viwCustomerCreditLimits here, rather than something generic or, worse, misleading; a view drawing from a customer table but named after employees is exactly the kind of naming mismatch that costs someone real time later, wondering why "the employee view" is full of customer data. WITH READ ONLY is an Oracle-specific clause that prevents any INSERT, UPDATE, or DELETE through this view entirely, appropriate here since the view's purpose is presentation, not data entry.

WITH READ ONLY is worth distinguishing from WITH CHECK OPTION, covered in more depth later in this course: WITH READ ONLY blocks all write operations against the view outright, while WITH CHECK OPTION still permits writes, but only ones that wouldn't produce a row falling outside the view's own WHERE condition. Choose WITH READ ONLY when a view exists purely to present data; choose WITH CHECK OPTION when the view needs to remain writable but shouldn't be used to sneak data outside its intended scope.

Not every view can support INSERT, UPDATE, or DELETE, and the rules for which ones can are covered in depth later in this course. As a preview: a view generally needs to include a table's primary key, along with any columns that don't allow NULL and don't have a default value, before INSERT through that view can succeed, since the database has to have a way to populate every required column on the underlying table. A view can add its own additional restrictions on top of this, WITH CHECK OPTION being the main example, but a view can never override or bypass a constraint that exists on the base table itself; any constraint the underlying table enforces still applies to every write that happens to arrive through a view.

To make this concrete, suppose Employees has an employee_id primary key and a NOT NULL column called hire_date with no default value. A view that leaves hire_date out entirely can still be queried freely, but INSERT through it will fail, since there's no way for the database to populate a required column the view never mentioned:

CREATE VIEW employee_names AS
SELECT employee_id, last_name, first_name
FROM Employees;

-- Fails: hire_date has no value to insert, and the view doesn't expose it
INSERT INTO employee_names (employee_id, last_name, first_name)
VALUES (500, 'Nguyen', 'Priya');

Include hire_date in the view's column list, and the same INSERT becomes possible, assuming every other NOT NULL column without a default is also present. This is exactly why a view built purely for reporting or presentation often deliberately excludes columns like this; it's not an oversight, it's often a way of making the view read-only in practice even without an explicit WITH READ ONLY.

A view is formally described as a single table derived from other tables, which can themselves be base tables or other previously defined views. It has no independent physical existence; it's a virtual table, in contrast to a base table, whose rows are always physically stored. That has real consequences for what operations are possible against a view, covered later in this course, but it places no real limitation on querying one. The underlying reason updating a view is ever a "problem" at all traces back to this same virtual nature: a write against a view has to be translated into a write against whichever base table actually owns the data, and that translation is straightforward for a simple, single-table view but can become ambiguous or impossible once a view involves a join, an aggregate, or a computed column with no single underlying column to write back to. This lesson only introduces the concept; the specific rules that separate updatable views from read-only ones are covered in depth later in this course.

Think of a view as a way of naming a query you expect to run often, even though the thing being named doesn't physically exist as its own table. Consider a database built around these tables:

EMPLOYEE:        Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno
DEPARTMENT:      Dname, Dnumber, Mgr_ssn, Mgr_start_date
DEPT_LOCATIONS:  Dnumber, Dlocation
PROJECT:         Pname, Pnumber, Plocation, Dnum
WORKS_ON:        Essn, Pno, Hours
DEPENDENT:       Essn, Dependent_name, Sex, Bdate, Relationship

A frequent request against a database like this might be "show me each employee's name alongside the names of the projects they work on." Answered directly, that requires joining EMPLOYEE, WORKS_ON, and PROJECT together every single time the question comes up:

SELECT e.Fname, e.Lname, p.Pname
FROM EMPLOYEE e
JOIN WORKS_ON w ON e.Ssn = w.Essn
JOIN PROJECT p ON w.Pno = p.Pnumber;

Rather than writing that three-table join out every time, the same logic can be saved once as a view:

CREATE VIEW employee_projects AS
SELECT e.Fname, e.Lname, p.Pname
FROM EMPLOYEE e
JOIN WORKS_ON w ON e.Ssn = w.Essn
JOIN PROJECT p ON w.Pno = p.Pnumber;

From that point forward, the same question becomes a single-table query:

SELECT * FROM employee_projects;

EMPLOYEE, WORKS_ON, and PROJECT are, in this context, the defining tables of the view, the underlying tables the view's query actually reads from. Everything about that three-table join still happens, but it happens once, inside the view's own definition, rather than being retyped by hand every time someone needs the answer.

It's worth knowing, even at this introductory stage, that querying a view isn't a two-step process where the view's result gets computed first and then filtered separately. When possible, Oracle merges the query issued against the view with the view's own defining query, and optimizes the combined result as a single statement, as if the view had never been involved at all. That means Oracle can generally use indexes on the underlying base table columns even when a query only ever references the view, not the table directly.

Here's what that merging actually looks like. Suppose a view joins two tables and filters to one department:

CREATE VIEW employees_view AS
SELECT employee_id, last_name, salary, location_id
FROM employees JOIN departments USING (department_id)
WHERE department_id = 10;

A user then queries that view for one specific employee:

SELECT last_name
FROM employees_view
WHERE employee_id = 200;

Oracle doesn't run the view's query first, materialize its result, and then filter that result by employee_id. Instead, it merges the two statements into a single query, roughly equivalent to writing this directly against the base tables:

SELECT last_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
  AND departments.department_id = 10
  AND employees.employee_id = 200;

This matters practically: a view isn't a hidden performance cost layered on top of ordinary queries, it's a name for a query the optimizer treats essentially the same as if you'd written the whole thing out by hand. Sometimes Oracle can't merge a view definition with the user's query, in which case it may not be able to use every index on the referenced columns, but that's the exception rather than the default assumption to build around.

Everything in this lesson has assumed a regular view, one with no data of its own, recomputed from the base tables on every query. That's the default, and it's worth being explicit about it now, since Oracle also supports materialized views, which do physically store their result and refresh it on a schedule rather than recomputing it live every time.

A regular view guarantees current data at the cost of recomputing its query on every access. A materialized view trades some of that currency for speed, reading a stored result instead of rerunning joins and aggregations each time. Materialized views, and the specific tradeoffs involved in choosing one over a regular view, are covered in full elsewhere in this course; the point worth carrying forward from this introductory lesson is simply that "view," on its own, means the regular, always-current kind unless materialization is stated explicitly.

Looking Ahead

This lesson introduced views as stored, always-current, single-table representations of one or more underlying tables. A few points worth carrying forward:

• A view has no data of its own; it's a saved query, recomputed against the base tables on every access.
• The three recurring reasons to use one: hiding complexity, presenting data differently without touching the base table, and insulating applications from schema changes elsewhere in that table.
• WITH READ ONLY blocks all writes through a view outright; WITH CHECK OPTION allows writes but only ones that stay within the view's own filter condition.
• Updatability depends on the view exposing every column the underlying table requires for a write to succeed, not on any special permission granted to the view itself.
• Oracle typically merges a query against a view with the view's own defining query, so a view is rarely a meaningful performance tax on its own.

The rest of this module builds directly on this foundation: how to control exactly which rows and columns a view exposes, how views support security by restricting access at the row and column level, and the specific rules that determine whether a given view supports being written back to, not just read from.