Reasons for creating User Views

The data flow diagram, combined with specific questions posed to users of data about their needs, paves the way for the designer to create user views.
User views are specific views of data created with SQL. The designer defines what data (from what database tables) are made available in these stored queries.

Creating stored queries using "SQL user views" involves defining a view in the database. A view is a virtual table that stores a predefined SQL query, allowing users to retrieve data without having to rewrite complex queries. The view does not store the actual data but instead references underlying tables.
Steps to Create a Stored Query Using SQL User Views

1. Define the View using the CREATE VIEW statement.
2. Query Data from the View as if it were a table.
3. Modify or Delete the View if necessary.

Basic Syntax:

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

Example 1: Creating a Simple View
Suppose you have a table called `employees` and want to create a view that retrieves only active employees.

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

Now, you can retrieve data from the view like this:

SELECT * FROM active_employees;

Example 2: Creating a View with Joins**
You can also create a view that joins multiple tables. Suppose you have `employees` and `departments` tables:

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

To fetch data:

SELECT * FROM employee_details;

Modifying an Existing View**
Use the `CREATE OR REPLACE VIEW` statement to update a view:

CREATE OR REPLACE VIEW active_employees AS
SELECT emp_id, first_name, last_name, department, hire_date
FROM employees
WHERE status = 'Active';

Dropping a View**
If you need to delete a view, use:

DROP VIEW active_employees;

Key Benefits of Using Views for Stored Queries**

1. Encapsulation: Users don’t need to remember complex SQL joins and filters.
2. Security: Restricts direct table access, allowing specific data exposure.
3. Simplifies Maintenance: If business logic changes, you can modify the view without altering application queries.

In summary, creating stored queries using SQL user views involves defining a SELECT query, creating a view based on the query, and then using the view in subsequent queries. Views offer a convenient way to store and reuse complex queries, while also providing an additional layer of abstraction and security.

In Oracle 19c, creating stored queries using "user views" follows the standard SQL syntax, with some Oracle-specific enhancements like

1. WITH CHECK OPTION and
2. FORCE/NOFORCE

options.
Example: Creating a View in Oracle 19c Let’s assume we have an `EMPLOYEES` table with the following structure:

DESC EMPLOYEES;

| Column Name    | Data Type    |
|---------------|-------------|
| EMP_ID        | NUMBER(10)  |
| FIRST_NAME    | VARCHAR2(50) |
| LAST_NAME     | VARCHAR2(50) |
| DEPARTMENT_ID | NUMBER(10)  |
| SALARY        | NUMBER(10,2) |
| STATUS        | VARCHAR2(10) |

Step 1: Creating a Simple View
Create a view to store a predefined query that retrieves active employees:

CREATE OR REPLACE VIEW ACTIVE_EMPLOYEES AS
SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE STATUS = 'Active';

Now, you can query the view like a table:

SELECT * FROM ACTIVE_EMPLOYEES;

Step 2: Creating a View with Joins
Suppose you have a `DEPARTMENTS` table and you want to join it with `EMPLOYEES` to create a view:

CREATE OR REPLACE VIEW EMPLOYEE_DETAILS AS
SELECT E.EMP_ID, E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME, E.SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

Query the view:

SELECT * FROM EMPLOYEE_DETAILS;

Step 3: Using `WITH CHECK OPTION` for Security
To ensure that only active employees are inserted/updated in the view:

CREATE OR REPLACE VIEW ACTIVE_EMPLOYEES_V2 AS
SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, STATUS
FROM EMPLOYEES
WHERE STATUS = 'Active'
WITH CHECK OPTION;

- This prevents users from inserting or updating records that do not meet the `STATUS = 'Active'` condition.
Step 4: Using `FORCE` to Create a View Before Table Existence
If the underlying table doesn’t exist yet but you still want to create the view:

CREATE FORCE VIEW FUTURE_EMPLOYEES AS
SELECT EMP_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES;

The keyword "FORCE" allows the creation of the view even if `EMPLOYEES` doesn't yet exist.
To remove the view:

DROP VIEW ACTIVE_EMPLOYEES;

Oracle-Specific Features in Views

• Materialized Views: If you need a physical copy of data for performance:

      CREATE MATERIALIZED VIEW EMPLOYEE_SNAPSHOT AS
      SELECT EMP_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES;
      

• Updatable Views: If all columns in the view come from a single table (not computed or aggregated), the view can support INSERT, UPDATE, and DELETE.

There are three important reasons for creating user views:

1. Data security
2. User needs
3. Calculated fields

• Data Security: User views were defined earlier in terms of data security: they specify which users are permitted access to what data in a database (or across databases). User views ensure that sensitive data remains hidden from unauthorized eyes.
• Specific user needs: Creating views to meet specific user needs is the flipside of the data security issue. Users must have access to certain data to perform their jobs. It is counterproductive for users to have to wade through data that is irrelevant to their work. User views, therefore, not only protect sensitive data within an organization; they also protect users from being inundated with useless data.
• Protecting database using Security: Although it is possible to interact with a DBMS either with basic forms (for a smaller product) or at the SQL command line (for enterprise-level products), doing so requires some measure of specialized training. A business usually has employees who must manipulate data but do not have
  1. the necessary expertise,
  2. cannot or do not want to gain the necessary expertise, or
  3. should not have direct access to the database for security reasons.
  Application developers therefore create programs that simplify access to the database for such users. Most DBMSs designed for business use provide some way to develop such applications. The larger the DBMS, the more likely it is that application development requires traditional programming skills. Smaller products support graphic tools for drawing forms and report layouts. The next lesson discusses the third reason for creating user views: calculated fields.