Summary: 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;
Code language: SQL (Structured Query Language) (sql)
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
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
SELECT
*
FROM
payroll;
Code language: SQL (Structured Query Language) (sql)
Removing SQL views
To remove a view from the database, you use the DROP VIEW
statement:
DROP VIEW view_name;
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned about SQL views and their practical usages. Now, you should know what views are and how to create, modify, and delete views in the database.