Leveraging Views in SQL for Optimal Data Presentation

Views serve as virtual tables: a saved query that presents data from one or more underlying tables without storing a copy of that data itself (unless it's a materialized view, covered later in this lesson). Used well, a view becomes a stable, reusable presentation layer for the database, a consistent contract that reports, dashboards, applications, and other users can query without reconstructing joins, filters, and business rules from scratch every time.

A view is a saved SELECT statement, sometimes described as a virtual table. It doesn't store data itself; each time it's queried, the database runs the underlying query against the current state of the base tables.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Four things separate a genuinely useful presentation view from an arbitrary saved query:

• It flattens related tables into one coherent row per business entity. An order, its customer, and its product line items become one row, instead of forcing every consumer to reconstruct the same three-table join.
• It exposes business-friendly names and derived fields. full_name, order_month, and is_overdue mean something to a report reader in a way that raw source-column names and inline date arithmetic don't.
• It applies the same filters and definitions everywhere. If "active customer" or "revenue" is defined once inside a view, every report built on that view means the same thing by those terms, instead of five different reports quietly implementing five slightly different definitions.
• It hides columns and rows a given audience shouldn't see. Sensitive columns, other departments' rows, or internal-only fields can be left out of the view entirely.

That combination is what makes views a stable foundation for BI tools, applications, and ad hoc queries alike, rather than just a syntactic convenience.

CREATE VIEW EmployeeOverview AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Active = 1;

EmployeeOverview presents only active employees, and only four columns, deliberately omitting anything like salary or contact information that this view's consumers don't need to see.

The EmployeeOverview view above filters to Active = 1, but by default, nothing stops someone from using that view to update a row's Active flag to 0, at which point the row simply vanishes from the view's visible results, even though the UPDATE itself succeeded without error. That's often surprising behavior to whoever ran the update.

WITH CHECK OPTION closes that gap by rejecting any INSERT or UPDATE through the view that would produce a row the view itself couldn't select:

CREATE VIEW EmployeeOverview AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Active = 1
WITH CHECK OPTION CONSTRAINT employee_overview_active_ck;

With this constraint in place, an UPDATE that tried to set Active = 0 through EmployeeOverview would be rejected outright, rather than silently succeeding and quietly removing the row from view. It's a small addition, but it's the difference between a view that merely filters what's displayed and one that actually enforces what's allowed to be written through it.

Once created, a view is queried exactly like a table:

SELECT FirstName, LastName
FROM EmployeeOverview
WHERE Department = 'IT';

To change what a view shows without dropping and recreating it, CREATE OR REPLACE VIEW redefines it in place:

CREATE OR REPLACE VIEW EmployeeOverview AS
SELECT EmployeeID, FirstName, LastName, Department, HireDate
FROM Employees
WHERE Active = 1;

This adds HireDate to the view without needing to drop it first, which matters since dropping a view can affect grants and dependent objects that reference it.

Views are primarily for retrieval, but INSERT, UPDATE, and DELETE against a view are possible under the right conditions, generally when the view is based on a single table with no aggregation, DISTINCT, or GROUP BY involved:

UPDATE EmployeeOverview
SET Department = 'HR'
WHERE LastName = 'Smith';

This is exactly where WITH CHECK OPTION, covered above, matters most: without it, an update like this one could unintentionally push a row outside the view's own filter condition and make it disappear from subsequent queries against the view, with no error to signal that anything unusual happened.

The "right conditions" caveat is worth being specific about. EmployeeOverview is updatable because it's a simple, single-table view with no aggregation. A view built on a GROUP BY with COUNT and SUM, like the dashboard-style aggregation views covered later in this lesson, is not updatable at all: there's no sensible way to translate an update to a summed total_revenue value back into a change on individual order_details rows, so Oracle simply won't allow INSERT, UPDATE, or DELETE against a view shaped that way. As a rule of thumb, a view built from a single table with no GROUP BY, DISTINCT, or aggregate functions is usually updatable directly; a view built around aggregation is a read-only presentation layer by nature, not a write path back to the underlying data.

DROP VIEW EmployeeOverview;

• Performance. Since a view stores no data of its own, every query against it re-executes the underlying query against the base tables. For views built on heavy joins or aggregations that get queried constantly, this cost adds up, which is exactly the scenario a materialized view, covered below, is built to address.
• Complexity. Views layered on top of other views, or views combining many tables, can produce query plans that are harder to read and tune than the equivalent query written out directly.
• Dependencies. A view depends on its underlying tables. If a referenced table's structure changes in a way that breaks the view's definition, for example a column the view relies on gets dropped, the view is marked invalid and must be recompiled before it can be used again, exactly the dependency behavior covered later in this lesson.

A handful of recurring view patterns cover most real presentation needs.

Reporting views, built for one purpose and one audience. A sales reporting view joins orders, customers, and products into one flat, business-readable row set, rather than trying to be a general-purpose "everything about orders" view that every team reuses for different purposes:

CREATE VIEW vw_sales_report AS
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.region,
    p.category,
    p.product_name,
    od.quantity,
    od.unit_price,
    od.quantity * od.unit_price AS line_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_details od ON od.order_id = o.order_id
JOIN products p ON p.product_id = od.product_id
WHERE o.status <> 'cancelled';

Every consumer of this view gets the same four-table join, the same exclusion of cancelled orders, and the same computed line_amount, without needing to know how any of it was assembled.

Aggregation views for dashboards, which pre-define a KPI's grain and formula once so every dashboard tile that references it agrees on what "monthly revenue by category" actually means, rather than each tile author reimplementing the calculation slightly differently:

CREATE VIEW vw_monthly_revenue_by_category AS
SELECT
    TRUNC(o.order_date, 'MM') AS sales_month,
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(od.quantity * od.unit_price) AS total_revenue
FROM orders o
JOIN order_details od ON od.order_id = o.order_id
JOIN products p ON p.product_id = od.product_id
GROUP BY TRUNC(o.order_date, 'MM'), p.category;

Notice this view is itself built from GROUP BY and COUNT(DISTINCT ...), both covered in earlier lessons; views and the aggregation techniques covered so far in this module aren't separate topics, a view is often just where a well-built aggregate query ends up living permanently.

Security or subset views, where access is granted on the view rather than the base table, hiding salary, personally identifiable information, or other departments' rows from users who shouldn't see them.

Abstraction views, which keep column names and grain stable while the physical schema underneath changes. Applications and reports bind to the view's contract, not the table structure, so a schema refactor doesn't force every downstream consumer to change at the same time.

Splitting core calculations from display formatting. Keep the actual metric definition, say, a total stored in cents, in one core view, and layer a thin presentation view on top of it that formats that value into a display string. Mixing calculation logic and display formatting into a single view definition makes both harder to change independently later.

A few practices keep these views maintainable as they accumulate:

• List columns explicitly rather than using SELECT *, so a new column added to a base table doesn't silently start appearing in every report built on it.
• Name columns like report labels a reader would recognize, not like the raw source column names they came from.
• Give each view one clear job rather than building a single mega-view every team pulls different things from.
• Limit how many views get stacked on top of other views; a few flat views are easier to tune and debug than a deep chain of nested ones.
• Don't rely on an ORDER BY inside the view definition to guarantee presentation order; sort in the query or reporting tool that consumes the view instead, for the same reason GROUP BY and DISTINCT don't guarantee order on their own.

A regular view recomputes its result on every query. A materialized view stores that result physically, on a refresh schedule, trading some data staleness for significantly faster reads:

Need	Prefer
Always-current data, security, simple reuse	Regular view
Heavy joins or aggregations hit repeatedly by dashboards, where slight staleness is acceptable	Materialized view

The practical approach: build the presentation layer with regular views first, and convert the specific views that turn out to be slow and heavily queried into materialized views once that's actually demonstrated to be a problem, rather than materializing everything preemptively.

Creating a materialized view uses almost identical syntax to a regular view, with one added keyword:

CREATE MATERIALIZED VIEW mv_monthly_revenue_by_category
REFRESH COMPLETE ON DEMAND AS
SELECT
    TRUNC(o.order_date, 'MM') AS sales_month,
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(od.quantity * od.unit_price) AS total_revenue
FROM orders o
JOIN order_details od ON od.order_id = o.order_id
JOIN products p ON p.product_id = od.product_id
GROUP BY TRUNC(o.order_date, 'MM'), p.category;

The query itself is identical to the regular view version shown earlier; what changes is that Oracle now physically stores the result and refreshes it according to the schedule specified, ON DEMAND here, rather than recomputing it from scratch on every single query. Dashboards querying mv_monthly_revenue_by_category read from that stored result instead of re-running the underlying joins and aggregation every time, at the cost of the data being only as current as the last refresh.

Before adding a column to a table, it's often necessary to find every view that draws from it, so those views can be updated to expose the new column if needed. Oracle exposes view definitions through its data dictionary rather than the ANSI-standard INFORMATION_SCHEMA used by some other database systems. The relevant views follow Oracle's standard three-tier naming convention: USER_VIEWS for views you own, ALL_VIEWS for views you have access to, and DBA_VIEWS for every view in the database, visible only to administrators.

Each of these views includes a TEXT column containing the actual defining query, which makes a simple text search enough to find every view that references a given table:

SELECT view_name
FROM all_views
WHERE UPPER(text) LIKE UPPER('%EMPLOYEES%');

This returns every view accessible to you whose definition mentions EMPLOYEES anywhere in its query text. It's an imperfect search, a view that references a table called EMPLOYEES_ARCHIVE would also match, so treat the result as a starting list to review rather than a guaranteed-precise answer.

Oracle tracks dependencies between a view and the tables it references automatically. If a referenced table changes in a way that could affect a dependent view or PL/SQL program, the database marks that dependent object invalid, and it's automatically recompiled the next time something tries to use it.

CREATE TABLE test_table (col1 INTEGER, col2 INTEGER);

CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
    FOR x IN (SELECT col1, col2 FROM test_table)
    LOOP
        NULL;
    END LOOP;
END;
/

Adding a new column to test_table leaves test_proc valid, since the procedure never referenced that column:

ALTER TABLE test_table ADD col3 NUMBER;

But changing the data type of a column the procedure actually depends on invalidates it:

ALTER TABLE test_table MODIFY col1 VARCHAR2(20);

Querying test_proc's status at this point would show INVALID. Running or recompiling it makes it valid again, automatically, the next time it's executed. Views follow this same dependency mechanism: because a view's definition is stored as the text of its defining query, the database can always determine which views depend on which tables, and can react automatically the moment something upstream changes.

This dependency tracking is part of a broader advantage: because a view, function, or procedure's source is stored directly in the database and exposed through data dictionary views, you can query your own code base with ordinary SQL, auditing what depends on what, without needing an external tool to reconstruct that information.

Looking Ahead

Views turn out to be one of the more quietly powerful tools covered in this module. A handful of points worth carrying forward:

• A view is a saved query, not stored data; it recomputes on every access unless it's materialized.
• WITH CHECK OPTION closes the gap between what a view displays and what it actually enforces when data is written back through it.
• The presentation patterns covered here, reporting, aggregation, security, and abstraction views, aren't mutually exclusive; a well-run reporting layer typically uses all four at once, for different views.
• Materialization trades data freshness for read speed, and it's a decision to make after a specific view is shown to be slow, not a default to reach for preemptively.
• Because a view's definition is stored as queryable text in the data dictionary, dependency tracking, auditing, and searching for a table's usages are all things ordinary SQL can answer, no external tooling required.

The next lessons in this course build on exactly this foundation, using views alongside the grouping, subquery, and filtering techniques already covered to construct genuinely useful reporting layers rather than one-off queries.