Creating Simple and Complex Views in SQL

Creating Simple and Complex Views in SQL

Simple Views

Definition: A simple view is a virtual table based on a single query that retrieves data from one or more tables. It does not include any complex calculations, joins, or aggregations.

Creating a Simple View: 

CREATE VIEW simple_view AS
SELECT employee_id, first_name, last_name
FROM employees;

Usage:

• Provides a straightforward way to query data without modifying the underlying table structure.
• Can be used to restrict access to specific columns or rows.

Querying the Simple View: 

SELECT * FROM simple_view;

Advantages:

• Simplifies complex queries by encapsulating them in a view.
• Can provide a subset of data from one or more tables.

Complex Views

Definition: A complex view involves more sophisticated SQL constructs, such as joins, aggregations, and subqueries. It can combine data from multiple tables and apply filters or calculations.

Creating a Complex View with Joins: 

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

Creating a Complex View with Aggregation: 

CREATE VIEW salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

Creating a Complex View with Subqueries: 

CREATE VIEW high_salary_employees AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees);

Usage:

• Useful for generating complex reports and summaries.
• Allows for combining and transforming data from multiple tables.

Visible and Invisible Columns

Definition: In some SQL database systems, you can create views with columns that are either visible or invisible. Invisible columns are not included in the output of a simple SELECT * query but can be used in queries that explicitly reference them.

Creating a View with Visible and Invisible Columns:

Example with Oracle SQL: 

CREATE VIEW view_with_invisible AS
SELECT employee_id, first_name, last_name,
       salary /* This column will be invisible */
FROM employees
WITH INVISIBLE (salary);

To View Invisible Columns: Invisible columns can be explicitly included in queries.

Querying a View Including Invisible Columns: 

SELECT employee_id, first_name, last_name, salary
FROM view_with_invisible;

Managing Visibility of Columns in Views:

• Visible Columns: Are displayed in a basic SELECT * query.
• Invisible Columns: Require explicit mention in the SELECT statement to be retrieved.

Best Practices for Using Views

• Security: Use views to restrict access to sensitive data. By creating a view that excludes certain columns, you can control what data is exposed to different users.
• Simplicity: Use simple views for straightforward queries and complex views for more elaborate data requirements. Avoid making views overly complex, which can impact performance.
• Performance: Views themselves do not store data; they just provide a window into the data. However, complex views with joins or aggregations might affect performance. Indexes on the underlying tables can help mitigate performance issues.
• Materialized Views: For performance optimization in cases of frequent read operations on complex queries, consider using materialized views, which store the result set.

Creating a Materialized View Example: 

CREATE MATERIALIZED VIEW mat_view_summary
AS SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Updating and Managing Views

Updating a View:

• Views are generally read-only unless they are updatable. Some views can be updated if they meet certain criteria (e.g., they do not involve joins or aggregations).

Example of Updatable View: 

CREATE VIEW updatable_view AS
SELECT employee_id, first_name, last_name
FROM employees;
-- Update via the view
UPDATE updatable_view
SET first_name = 'Jane'
WHERE employee_id = 1;

Dropping a View: 

DROP VIEW view_with_invisible;

Modifying a View: To modify a view, you typically use the CREATE OR REPLACE statement.

Example of Modifying a View: 

CREATE OR REPLACE VIEW complex_view AS
SELECT e.employee_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;