SQL Views

This tutorial introduces you to the SQL views concept and shows you how to manage view in the database.

Introduction to the SQL Views

A relational database consists of multiple related tables e.g., employees, departments, jobs, etc. When you want to see the data of these tables, you use the SELECT statement with JOIN or UNION clauses.

SQL provides you with another way to see the data is by using the views.  A view is like a virtual table produced by executing a query. The relational database management system (RDBMS) stores a view as a named SELECT in the database catalog.

Whenever you issue a SELECT statement that contains a view name, the RDBMS executes the view-defining query to create the virtual table. That virtual table then is used as the source table of the query.

Why do you need to use the views

Views allow you to store complex queries in the database. For example, instead of issuing a complex SQL query each time you want to see the data, you just need to issue a simple query as follows:

SELECT column_list
FROM view_name;

Views help you pack the data for a specific group of users. For example, you can create a view of salary data for the employees for Finance department.

Views help maintain database security. Rather than give the users access to database tables, you create a view to revealing only necessary data and grant the users to access to the view.

Creating SQL views

To create a view, you use the CREATE VIEW statement as follows:

CREATE VIEW view_name 
AS
SELECT-statement

First, specify the name of the view after the CREATE VIEW clause.

Second, construct a SELECT statement to query data from multiple tables.

For example, the following statement creates the employee contacts view based on the data of the employees and departments tables.

CREATE VIEW employee_contacts AS
    SELECT 
        first_name, last_name, email, phone_number, department_name
    FROM
        employees e
            INNER JOIN
        departments d ON d.department_id = e.department_id
    ORDER BY first_name;

By default, the names of columns of the view are the same as column specified in the SELECT statement. If you want to rename the columns in the view, you include the new column names after the CREATE VIEW clause as follows:

CREATE VIEW view_name(new_column_list) 
AS
SELECT-statement;

For example, the following statement creates a view whose column names are not the same as the column names of the base tables.

CREATE VIEW payroll (first_name , last_name , job, compensation) AS
    SELECT 
        first_name, last_name, job_title, salary
    FROM
        employees e
            INNER JOIN
        jobs j ON j.job_id= e.job_id
    ORDER BY first_name;

Querying data from views

Querying data from views is the same as querying data from tables. The following statement selects data from the employee_contacts view.

SELECT 
    *
FROM
    employee_contacts;

Of course, you can apply filtering or grouping as follows:

SELECT 
    job, 
    MIN(compensation), 
    MAX(compensation), 
    AVG(compensation)
FROM
    payroll
WHERE
    job LIKE 'A%'
GROUP BY job;

Modifying SQL views

To modify a view, either adding new columns to the view or removing columns from a view, you use the same CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE view_name AS
SELECT-statement;

The statement creates a view if it does not exist or change the current view if the view already exists.

For example, the following statement changes the payroll view by adding the department column and rename the compensation column to salary column.

CREATE OR REPLACE VIEW payroll (first_name , last_name , job , department , salary) AS
    SELECT 
        first_name, last_name, job_title, department_name, salary
    FROM
        employees e
            INNER JOIN
        jobs j ON j.job_id = e.job_id
            INNER JOIN
        departments d ON d.department_id = e.department_id
    ORDER BY first_name;

SELECT 
    *
FROM
    payroll;SELECT 
    *
FROM
    payroll;

Removing SQL views

To remove a view from the database, you use the DROP VIEW statement:

DROP VIEW view_name;

The DROP VIEW statement deletes the view only, not the base tables.

For example, to remove the payroll view, you use the following statement:

DROP VIEW payroll;