Creating Views (Virtual Tables)

tags: #sql

What is a view?

A view in SQL is a virtual table based on the result of a SELECT statement that is not necessarily populated i.e., it is NOT a physical table and does not store any data on its own and does not really exist or physically saved.

• i.e., basically creating "pseudo-relations" with customized data from existing relations.
  
  
• The stored SELECT statement is executed each time the view is queried.

Views are created on a schema-level.

Why do we use views?

Views can also be used to restrict access to sensitive data in a database

• We can create views that only show specific data and grant access of said view to specific users instead of the entire table. This allows users to access relevant data while keeping the original table secure.

Simplify Data

• Views can be used to hide complexity by combining several tables into a single view that presents a simplified representation of the data.

Customized View of Data

• Can create various representation of the data from different tables to provide a customized view of the database that is tailored to specific user needs.

Enforce Some Real-world Rules

• i.e., Views can be used to enforce business rules by providing a virtual table that only includes rows that meet certain criteria
• e.g., a view can be created that only shows customers who have placed an order in the last 30 days.

General Syntax

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

-- to view output of a view
SELECT * FROM view_name;

Example 1: Views of customer orders

CREATE VIEW customer_orders AS
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers AS c
JOIN orders o ON c.customer_id = o.customer_id;

Example 2: Querying salary bonus of employees

CREATE VIEW employee_bonus AS
SELECT employee_name, salary, salary * 0.1 AS bonus
FROM employees;

Dropping Views

To remove VIEWS from the schema:

DROP VIEW [IF EXISTS] view_name;

• IF EXISTS is an optional keyword that prevents an error from occurring if the view does not exist.