Reasons for Creating User Views

User views are stored queries (virtual tables) that present exactly the data a role or team needs—nothing more, nothing less. In Requirements Analysis, DFDs and user interviews reveal who needs which data. Views turn those findings into concrete, reusable SQL objects that improve security, usability, and maintainability.

A view is a named SELECT statement stored in the database. It does not store rows; it references base tables and returns results as if it were a table.

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

Query it like a table:

SELECT * FROM view_name;

1. Data Security
   Expose only required columns/rows, hide sensitive attributes, and limit direct table access.

   CREATE VIEW sales_public AS
   SELECT order_id, order_date, customer_id, total_amount
   FROM   orders
   WHERE  status = 'COMPLETE';

2. Specific User Needs
   Give each group a task-focused slice of data so they don’t wade through irrelevant tables or logic.

   CREATE VIEW hr_active_employees AS
   SELECT emp_id, first_name, last_name, dept_id
   FROM   employees
   WHERE  status = 'Active';

3. Calculated Fields & Consistent Logic
   Centralize formulas and joins once; every consumer gets the same, correct computation.

   CREATE VIEW order_summary AS
   SELECT o.order_id,
          SUM(oi.qty * oi.unit_price) AS computed_total
   FROM   orders o
   JOIN   order_items oi ON oi.order_id = o.order_id
   GROUP  BY o.order_id;

1. Define the SELECT you want to reuse.
2. Create the view with a meaningful, role-oriented name.
3. Use the view in reports, apps, and downstream queries.
4. Evolve with CREATE OR REPLACE VIEW to keep business logic centralized.

Joined view example

CREATE OR REPLACE VIEW employee_details AS
SELECT e.emp_id, e.first_name, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON d.department_id = e.department_id;

• WITH CHECK OPTION enforces the view’s predicate on DML through the view:

  CREATE OR REPLACE VIEW hr_active_employees AS
  SELECT emp_id, first_name, last_name, dept_id, status
  FROM   employees
  WHERE  status = 'Active'
  WITH CHECK OPTION;

• FORCE creates a view even if base objects don’t yet exist (useful in deployment pipelines):

  CREATE FORCE VIEW future_employees AS
  SELECT emp_id, first_name FROM employees;

• Updatability: Simple views over a single base table (no set ops/aggregates) are often updatable.
• Materialized Views: For performance/caching and refresh schedules—distinct from logical views.

• Name views by audience/purpose (e.g., finance_invoice_v, ops_orders_v).
• Expose only needed columns; avoid SELECT *.
• Document filters/joins in comments; centralize repeated formulas.
• Tie view grants to roles, not individual users.

Views turn user requirements into secure, consistent, and maintainable data access. They protect sensitive information, streamline daily work, and keep evolving business logic in one authoritative place.