SQL Views: Creation, Modification, Deletion & Practical Applications

TAKEAWAYS:

• Virtual tables: Views are stored queries that act like tables but don’t store data themselves.
• Security: Views restrict access to sensitive columns/rows without altering base tables.
• Simplification: Complex queries can be saved as views for reuse (e.g., SELECT * FROM employee_salaries).
• Modification: Use CREATE OR REPLACE VIEW to update existing views without dropping them.
• Deletion: DROP VIEW removes a view but leaves base tables intact.
• Performance: Views can improve query efficiency by pre-filtering data (e.g., WHERE salary > 50000).

What Are SQL Views?

A view is a virtual table based on the result set of an SQL query. It does not store data physically but dynamically retrieves data from one or more base tables when queried. Views are created using the CREATE VIEW statement and can be used like regular tables in queries (e.g., SELECT, INSERT, UPDATE, DELETE).

Why Use Views?

1. Data Security: Hide sensitive columns (e.g., passwords) or rows (e.g., confidential records).
2. Simplification: Replace complex queries with a simple view name.
3. Data Independence: Change base table structure without affecting views (if the view query remains valid).
4. Performance: Pre-filter data to reduce query complexity (e.g., views for frequently accessed subsets).

1. Creating a View

Syntax:

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

Example: Employee Salary View

Assume a base table employees with columns: emp_id, name, salary, department. Create a view to show only high earners:

CREATE VIEW high_earners AS
SELECT emp_id, name, salary
FROM employees
WHERE salary > 50000;

Query the view:

SELECT * FROM high_earners;

Output:

EMP_ID | NAME    | SALARY
-------+---------+--------
101    | Alice   | 60000
103    | Bob     | 55000

2. Modifying a View

Views cannot be modified directly with ALTER TABLE-like syntax. Instead:

• Drop and recreate the view:

  DROP VIEW high_earners;
  CREATE VIEW high_earners AS
  SELECT emp_id, name, salary, department
  FROM employees
  WHERE salary > 60000; -- Updated condition
  

• Use CREATE OR REPLACE VIEW (Oracle/PostgreSQL):

  CREATE OR REPLACE VIEW high_earners AS
  SELECT emp_id, name, salary
  FROM employees
  WHERE salary > 60000;
  

3. Deleting a View

Use DROP VIEW to remove a view. This does not delete the underlying base table.

DROP VIEW high_earners;

Note: If the view is referenced in other views or applications, dropping it may cause errors.

4. Updatable Views

Views are not always updatable. A view is updatable if:

• It is based on a single table.
• It includes a primary key column.
• It does not contain:
  • Aggregations (SUM, AVG).
  • GROUP BY, HAVING, DISTINCT.
  • Joins or subqueries.

Example: Updatable View

CREATE VIEW employee_details AS
SELECT emp_id, name, salary
FROM employees;

Update the view (affects the base table):

UPDATE employee_details
SET salary = 70000
WHERE emp_id = 101;

5. Views vs. Base Tables

6. Real-World Applications of Views

erDiagram
    users ||--o{ transactions : "has"
    users {
        string user_id PK
        string name
        string email
        string password_hash
    }
    transactions {
        string txn_id PK
        string user_id FK
        string amount
        string txn_date
        string description
    }
    user_transactions {
        string user_id PK
        string name
        string email
        string txn_id
        string amount
        string txn_date
        string description
    }
    user_transactions ||--o{ transactions : "views"

ER diagram showing how a view (usertransactions) filters sensitive columns (passwordhash) from the base tables for eSewa's transaction history.

In the Real World

1. eSewa (Nepal):

   • Use Case: Views hide sensitive user data (e.g., transaction passwords) from developers while allowing access to transaction histories.
   • How: A view like user_transactions might exclude password columns from the base users table.

2. Ncell (Telecom):

   • Use Case: Customer service representatives need to see only call logs and balances, not internal billing details.
   • How: A view customer_call_logs filters the billing_records table to show only call duration and dates.

3. Daraz (E-commerce):

   • Use Case: Inventory managers need to see only low-stock products.
   • How: A view low_stock_products is created with:

     CREATE VIEW low_stock_products AS
     SELECT product_id, product_name, stock_quantity
     FROM inventory
     WHERE stock_quantity < 10;
     

Worked Example: NTC Traffic Route Optimization

Scenario: The Nepal Telecommunications Company (NTC) wants to monitor network traffic between districts but hide internal router configurations. Solution: Create a view district_traffic that shows only public data:

CREATE VIEW district_traffic AS
SELECT district, total_data_used, avg_latency
FROM network_usage
WHERE is_internal = 'NO';

Query:

SELECT * FROM district_traffic
WHERE avg_latency > 50;

Output:

DISTRICT | TOTAL_DATA_USED | AVG_LATENCY
---------+-----------------+-------------
Kathmandu| 15000 GB        | 60
Pokhara  | 8000 GB         | 55

7. Advanced View Techniques

sequenceDiagram
    participant User
    participant View
    participant BaseTable

    User->>View: SELECT * FROM valid_salaries
    View->>BaseTable: SELECT emp_id, salary FROM employees WHERE salary BETWEEN 30000 AND 100000
    BaseTable-->>View: Returns filtered rows
    View-->>User: Returns valid_salaries data
    
    User->>View: UPDATE valid_salaries SET salary = 120000 WHERE emp_id = 101
    View->>View: Rejects update (violates view condition)
    View-->>User: Error: 'salary' must be between 30000 and 100000

a. Checked Views (Data Validation)

Ensure data integrity by restricting updates to valid ranges.

CREATE VIEW valid_salaries AS
SELECT emp_id, salary
FROM employees
WHERE salary BETWEEN 30000 AND 100000;

Attempt to update:

UPDATE valid_salaries
SET salary = 120000 -- Fails (violates view condition)
WHERE emp_id = 101;

b. Materialized Views (Performance)

Some databases (e.g., Oracle) support materialized views, which store the result set physically for faster reads.

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT product_id, SUM(quantity) as total_sold
FROM sales
GROUP BY product_id;

Refresh periodically:

REFRESH MATERIALIZED VIEW daily_sales_summary;

8. Common Pitfalls and Best Practices

• Avoid Complex Views: Views with joins or subqueries can become slow or unmaintainable.
• Document Views: Clearly name views (e.g., hr_employee_salaries) and document their purpose.
• Test Updates: Always test INSERT, UPDATE, and DELETE operations on views before deploying.
• Monitor Dependencies: Use tools like USER_DEPENDENCIES (Oracle) to check which objects depend on a view before dropping it.

Exam Tip

1. Syntax is Key:

   • Memorize CREATE VIEW, DROP VIEW, and CREATE OR REPLACE VIEW syntax.
   • Know when a view is updatable (single-table, includes PK, no aggregations).

2. Practical Scenarios:

   • Expect questions on security views (hiding columns) or simplification views (replacing complex queries).
   • Example: "Create a view to show only active customers for a bank’s loan department."

3. Comparison Questions:

   • Be ready to compare views with base tables, stored procedures, or triggers.
   • Example: "Why would a company use a view instead of a stored procedure for reporting?"

4. Error Handling:

   • Understand why an update might fail on a view (e.g., missing PK, aggregation).
   • Example: "Why does this update fail?" (Show a view with GROUP BY).

5. Real-World Tie-Ins:

   • Link views to data security (e.g., eSewa hiding passwords) or performance (e.g., Daraz filtering low-stock items).
   • Example: "How might Ncell use views to restrict customer service agents’ access to billing data?"

Caption: ER diagram showing a view (high_earners) derived from a base table (employees). The view excludes the department column and filters for salary > 50000.

sequenceDiagram
    participant User
    participant View
    participant BaseTable
    User->>View: SELECT * FROM high_earners
    View->>BaseTable: SELECT emp_id, name, salary FROM employees WHERE salary > 50000
    BaseTable-->>View: Returns filtered data
    View-->>User: Displays virtual table

Caption: How a view dynamically retrieves data from a base table when queried. The user interacts only with the view, not the underlying table.