Views

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What Is a View?

A view is a saved SELECT statement stored in the database under a name. It behaves like a table — you can query it with SELECT, filter it with WHERE, join it to other tables — but no data is stored inside it. Every time you query a view Oracle runs the underlying SELECT and returns fresh results. Think of a view as a window into your data: the window stays fixed but the data it shows reflects whatever is in the base tables at that moment.

Views serve several practical purposes. They simplify complex queries by hiding joins and calculations behind a clean name. They enforce consistent column naming and logic across the application. They restrict access — a user can be granted permission to query a view without being given access to the underlying tables. And they protect sensitive columns by simply not including them in the view definition.

• A view stores a query definition — not data — so it always reflects current table contents
• Views are queried exactly like tables using SELECT
• The underlying tables are called base tables
• A view can reference other views, but deep nesting hurts readability and performance
• Oracle stores view definitions in the data dictionary — query USER_VIEWS to list them
• A view can be replaced without dropping it first using CREATE OR REPLACE VIEW

Creating and Querying a View

Use CREATE VIEW to define the view and give it a name. The SELECT that follows becomes the view definition. Once created, querying the view is identical to querying a table. You can add WHERE, ORDER BY, GROUP BY, and JOIN clauses on top of the view just as you would with any table.

-- Create a view that summarises order totals per customer.
-- The view hides the JOIN and aggregation -- callers just SELECT from it.
CREATE OR REPLACE VIEW dataplexa_store.customer_order_summary AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name  AS customer_name,
    c.country,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.total_amount)                 AS total_spent,
    MAX(o.order_date)                   AS last_order_date
FROM   dataplexa_store.customers  c
LEFT JOIN dataplexa_store.orders  o ON c.customer_id = o.customer_id
GROUP  BY c.customer_id, c.first_name, c.last_name, c.country;
-- Querying the view -- no JOIN or GROUP BY needed by the caller
SELECT *
FROM   dataplexa_store.customer_order_summary
WHERE  country = 'United States'
ORDER  BY total_spent DESC;

• CREATE OR REPLACE VIEW creates the view if it does not exist, or replaces the definition if it does — no need to DROP first
• The caller uses WHERE country = 'United States' directly on the view — Oracle applies it inside the underlying query
• The JOIN and GROUP BY are invisible to anyone querying the view — they just see a clean two-column summary

Views for Security — Hiding Columns and Rows

A view is one of Oracle's primary tools for data access control. By defining a view that excludes sensitive columns — salaries, personal identifiers, payment details — you can grant SELECT on the view without exposing the full table. Row-level filtering in the view definition restricts which rows a user can see, without any application code needed to enforce it.

-- A view for a reporting user who should see employee names and departments
-- but NOT salaries or personal contact details.
CREATE OR REPLACE VIEW dataplexa_hr.employee_public_info AS
SELECT
    employee_id,
    first_name || ' ' || last_name  AS employee_name,
    job_title,
    department_id,
    hire_date
FROM   dataplexa_hr.employees;
-- A view that restricts rows -- only active employees in London
CREATE OR REPLACE VIEW dataplexa_hr.london_active_employees AS
SELECT
    employee_id,
    first_name || ' ' || last_name  AS employee_name,
    job_title,
    department_id
FROM   dataplexa_hr.employees
WHERE  location   = 'London'
AND    status     = 'active';
-- Grant read access to the view only -- not to the base table
GRANT SELECT ON dataplexa_hr.employee_public_info TO reporting_user;

• employee_public_info exposes name, title, department, and hire date — salary and contact columns are simply absent from the definition
• london_active_employees filters rows at the view level — the reporting user can never see employees outside London or with inactive status
• GRANT SELECT ON view TO user gives access to the view only — the underlying table remains inaccessible to that user

Replacing and Dropping Views

View definitions change as business requirements evolve. CREATE OR REPLACE VIEW updates the definition in place without losing any grants that have been given on the view. To remove a view entirely use DROP VIEW — this removes the definition from the data dictionary but leaves the base tables untouched.

-- Replace an existing view definition -- adds email to the public info view.
-- Existing grants on employee_public_info are preserved.
CREATE OR REPLACE VIEW dataplexa_hr.employee_public_info AS
SELECT
    employee_id,
    first_name || ' ' || last_name  AS employee_name,
    job_title,
    department_id,
    hire_date,
    work_email                      -- new column added to the view
FROM   dataplexa_hr.employees;
-- Remove a view entirely
DROP VIEW dataplexa_hr.london_active_employees;
-- List all views owned by the current user
SELECT view_name, text_length
FROM   user_views
ORDER  BY view_name;

• CREATE OR REPLACE VIEW preserves grants — dropping and recreating would lose them
• DROP VIEW removes only the view definition — the base tables and their data are completely unaffected
• USER_VIEWS lists all views owned by the current schema — ALL_VIEWS shows views across all accessible schemas
• text_length is the character length of the stored view definition — useful for spotting unexpectedly complex views

Updatable Views

A view is updatable when Oracle can trace each column in the view back to a single column in a single base table. INSERT, UPDATE, and DELETE on an updatable view modify the underlying base table directly. A view becomes non-updatable when it contains GROUP BY, DISTINCT, aggregate functions, joins across multiple tables, or expressions in the SELECT list — Oracle cannot determine which base table row to modify.

-- A simple single-table view is updatable.
CREATE OR REPLACE VIEW dataplexa_store.active_products AS
SELECT product_id, product_name, price, stock_quantity
FROM   dataplexa_store.products
WHERE  is_active = 1;
-- UPDATE through the view -- modifies the base table directly
UPDATE dataplexa_store.active_products
SET    price = 319.99
WHERE  product_id = 7;
-- WITH CHECK OPTION prevents updates that would make the row invisible in the view.
-- Without it, you could update is_active to 0 through active_products,
-- and the row would silently disappear from the view after the update.
CREATE OR REPLACE VIEW dataplexa_store.active_products AS
SELECT product_id, product_name, price, stock_quantity
FROM   dataplexa_store.products
WHERE  is_active = 1
WITH CHECK OPTION;   -- rejects any change that violates the WHERE condition

• The UPDATE modifies products.price directly — the view is just a conduit
• Without WITH CHECK OPTION, an UPDATE that changes is_active to 0 would silently remove the row from the view after the update — the data would change but the view would no longer show it
• WITH CHECK OPTION prevents this by rejecting any modification that would cause the updated row to fall outside the view's WHERE condition
• Views with GROUP BY, aggregates, DISTINCT, or multi-table joins are never updatable

Lesson Summary

Practice Questions

Quiz

Next up — Indexes — How Oracle uses indexes to speed up queries, the difference between B-tree and bitmap indexes, and when to create or avoid them.