How to Create SQL View

Introduce to SQL View

SQL View is a virtual table which is used to encapsulate a complex queries. After creating SQL view, you can treat a view as a table and manipulate data on it with only some restrictions. When the data in table changes, the data in view which is dependent on table changes also. View does not occupied the physical space as table does. The syntax of creating view as follows:

CREATE VIEW {view_name}[(column1,column2...)] 
AS  
SELECT column1,column2  
FROM table

SQL View Example

We have departments and employees table with sample data

department_id  name                
-------------  --------------------
            1  Software Engineering
            2  Sale                
            3  Marketing           
            4  HR              

employee_id  name      department_id  job_id  salary 
-----------  --------  -------------  ------  -------
          1  jack                  1       1  3000.00
          2  mary                  2       2  2500.00
          3  newcomer         (NULL)       0  2000.00
          4  anna                  1       1  2800.00
          5  Tom                   2       2  2700.00
          6  foo                   3       3  4700.00

Now we create view to specify the salary of each employee and their department.

CREATE VIEW SALARY_RECORDS 
AS  
SELECT employees.name,
	   salary,
	   departments.name AS department_nnme  
FROM employees   
INNER JOIN departments ON 
employees.department_id = departments.department_id

We can take an example to query our created view by using SQL SELECT statement:

	SELECT * FROM SALARY_RECORDS

And you get this result.

name     salary  department_nnme     
------  -------  --------------------
jack    3000.00  Software Engineering
anna    2800.00  Software Engineering
mary    2500.00  Sale                
Tom     2700.00  Sale                
foo     4700.00  Marketing       

You can also take some SQL data manipulation on it to understand what view is and how to create SQL view.

Applications of View

View can be useful in some cases:

1. Helps you to encapsulate complex query and make it reusable
2. Provides user security on each view - it depends on your data policy security.
3. Using view to convert units - if you have a financial data in US currency, you can create view to convert them into Euro for viewing in Euro currency, for example.

Click here to see how to alter view and drop view