Creating SQL Views and the Benefits of User Views

SQL views provide a flexible way to present data from one or more base tables without exposing the underlying physical design. When you design views from the requirements you gathered earlier in the database life cycle, you create a “custom lens” for each role in the organization.

The key benefits of creating user views include:

1. Role-focused data presentation
   You can present each user with exactly the data they need to perform their job, and only that data. For example, a billing clerk might see invoices, balances, and payment dates, while a support analyst sees only customer contact details and issue history. By mapping each job role to one or more views, you simplify screens and queries, reduce training time, and lower the risk of user error.
2. Reusable logic for complex queries
   You can encapsulate complex joins, filters, and calculations inside a view and reuse that logic in many queries and reports. Instead of copying a long SELECT statement into every report, you define it once as a view and reference it just like a table:

   CREATE VIEW v_customer_orders AS
   SELECT  c.customer_id,
           c.customer_name,
           o.order_id,
           o.order_date,
           o.order_total
   FROM    customers AS c
   JOIN    orders    AS o
      ON   c.customer_id = o.customer_id;

   Application developers and report writers can then query v_customer_orders directly, which improves consistency, reduces bugs, and makes later schema changes easier to manage.
3. Support for calculated and derived values
   Views can expose derived values that you intentionally avoid storing in base tables. Because the data is calculated at query time, it always reflects the latest state of the underlying tables:

   CREATE VIEW v_order_summary AS
   SELECT  o.order_id,
           o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS order_total
   FROM    orders      AS o
   JOIN    order_items AS oi
      ON   o.order_id = oi.order_id
   GROUP BY o.order_id, o.customer_id;

   This design keeps the base tables normalized while still delivering the metrics business users expect (totals, percentages, rankings, and so on) through views.
4. Security and compliance
   By exposing only views to end users—and assigning permissions on those views instead of on base tables—you can implement row- and column-level security. Sensitive columns (such as salary or personally identifiable information) can be omitted or masked in a view, while administrators retain full access via the base tables. This is a common pattern in modern relational systems such as Oracle Database, SQL Server, PostgreSQL, and MySQL.
5. Schema evolution and insulation
   Because applications query views instead of base tables directly, you can refactor or partition underlying tables (for example, migrating legacy LONG/LONG RAW columns to modern LOB types) while keeping the view interface stable. As long as each view continues to return the same set of columns with compatible data types, dependent applications and reports can remain unchanged.

As you will see in the next lesson, you can further control which operations users can perform on views by assigning database permissions.

Views and permissions work together to implement the security model of a database. A view defines what a user can see; permissions define what actions they can take. In most relational database systems, the administrator grants permissions using statements such as GRANT and REVOKE.

Common permission types include:

1. SELECT – The user can retrieve data from a table or view.
2. INSERT – The user can create new rows in a table or, when the view is updatable, through a view. Permissions can be restricted to specific columns.
3. UPDATE – The user can modify existing data in a table or updatable view, again for an entire table or specific columns.
4. DELETE – The user can remove existing rows from a table or view.
5. REFERENCES – The user can reference a column (for example, as a foreign key) when creating new tables, sometimes limited to particular columns.
6. ALL – The user has all of the permissions listed above.

A common best practice is to deny direct access to base tables for most users, grant them the minimum required permissions on views, and reserve table-level permissions for administrators and service accounts.

Database architects and application developers typically understand the full database schema and may work directly with base tables, indexes, and constraints. End users, however, should not need to know how many tables exist or how they are joined. The relational model therefore includes views as a way to present an external schema, or user view, that hides internal complexity and protects the underlying design.

A well-designed set of views:

• Abstracts away table names and join details
• Provides stable interfaces even as the physical design evolves
• Supports different perspectives for different departments (operations, finance, support, analytics)
• Reduces the likelihood that ad-hoc queries will bypass business rules or damage data

In modern systems, views are also used as integration points for reporting tools, APIs, and data warehouses, because they formalize the shape of the data being exposed.

A regular (non-materialized) SQL view does not store its own copy of data. Instead, the database stores:

• The name of the view
• The query definition that describes how to produce its rows

When a user references the view in a query, the database optimizer effectively expands the view definition and executes the underlying query against the base tables.

Because of this behavior, a view can:

1. Combine data from one or more tables
2. Return selected rows based on filters
3. Expose selected columns (or calculated expressions) while hiding others

The result set returned through the view is sometimes called a virtual table^[1]—it looks and behaves like a table to the user, but it is rebuilt on demand.

Many relational systems also support updatable views as long as they meet certain rules (for example, selecting from a single base table without disruptive aggregates). Updates issued against such a view are automatically translated into updates on the underlying table, allowing you to maintain data through a simplified interface.

Caution: Some desktop or end-user tools allow saving the results of a view as a physical table. Unless you also implement refresh logic (for example, with a scheduled job or a true materialized view feature), those copied tables quickly become stale and inconsistent with the source data. In most transactional systems, it is better to keep frequently used queries as logical views or use vendor-supported materialized views that include refresh policies.

[1] Virtual table: A logical table returned by a view definition. The data is generated dynamically from one or more base tables each time the view is referenced, rather than being stored separately.