Database basic tutorial - Part 13: View

Views in SQL are virtual tables that represent the result of a query. They are not stored as actual tables with data but are generated dynamically when accessed.

Benefits when using View:

1. Simplification of Complex Queries:

Views allow you to encapsulate complex queries into a single virtual table. This means that instead of writing a complicated SQL query multiple times, you can define it once as a view and then simply query the view as if it were a table.

2. Data Abstraction:

Views can be used to hide the complexity of the underlying database schema. You can expose only specific columns or rows of a table through a view, providing a simplified interface to the data.

3. Security:

Views can act as a security mechanism. By granting access to views rather than the underlying tables, you can control what data users can see or modify.

4. Reusability:

Once a view is created, it can be reused in multiple queries. This promotes code reuse and reduces redundancy.

5. Logical Data Independence:

Views provide a level of abstraction from the underlying physical schema. Changes to the structure of the underlying tables do not necessarily affect the view, as long as the view's definition remains valid.

6. Performance Optimization:

In some cases, views can be used to optimize query performance, especially if the database supports materialized views, which store the view's result set as a physical table for faster access.

Example:

SET SQL_SAFE_UPDATES = 0;

USE JadeDb;


DROP TABLE IF EXISTS Employees;


CREATE TABLE IF NOT EXISTS Employees (id INT PRIMARY KEY AUTO_INCREMENT,
                                                         `name` VARCHAR(30),
                                                                department_id INT, salary FLOAT);


DROP TABLE IF EXISTS Departments;


CREATE TABLE IF NOT EXISTS Departments (department_id INT PRIMARY KEY AUTO_INCREMENT,
                                                                      department_name VARCHAR(20),
                                                                                      LOCATION VARCHAR(20));


INSERT INTO Departments (department_name, LOCATION)
VALUES ('HR', 'New York'),
       ('Engineering', 'San Francisco'),
       ('Sales', 'Chicago');


INSERT INTO Employees (`name`, department_id, salary)
VALUES ('Alice', 1, 70000),
       ('Bob', 2, 80000),
       ('Charlie', 3, 60000),
       ('David', 3, 75000),
       ('Eve', 2, 90000);


DROP VIEW IF EXISTS EmployeesView;


CREATE VIEW EmployeesView AS
SELECT `name`,
       department_name
FROM Employees
JOIN Departments ON Employees.department_id = Departments.department_id;


SELECT *
FROM EmployeesView

Tutorial video: