Why to create view?

Let us assume if other users have access to run query on table, they can get all data from the table that includes sensitive information too. To avoid this situation, view came into picture as a solution. By creating view, we can give access to the selected columns on table. End users can work on view without having the idea about the table what it contains. By using view, we can combine data from various tables and make it as one table to use.

Views are created for the purpose of hiding some secured information from end users. Views are created with all or some specific columns from table as per requirement and hides the sensitive information from end users.

The tables that are used to create the view are called as base tables.

View is nothing but select statement output which is stored in database. It just looks like a normal table, end users can do all operations like select, create, update statements on views. View can create from existing one or more tables in database.

How to create view?

CREATE VIEW statement is used to create the view from one or more tables with all or selected columns from the base tables.

FOR CREATE view -

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

CREATE VIEW by using multiple tables -

CREATE VIEW view_name AS SELECT table_name1.column1,
     table_name1.column2, table_name2.column1, table_name2.column2, ….,
     table_nameN.columnN 
  FROM table_name1, table_name2, …, table_nameN
  WHERE condition; 

CREATE VIEW WITH CHECK OPTION -

CREATE VIEW view_name AS
  SELECT column1,column2 
  FROM table_name WHERE condition WITH CHECK OPTION;

CREATE VIEW with aggregate function -

 CREATE VIEW view_name AS
  SELECT aggregate function (column1),column2
    FROM table_name WHERE condition; 

• view_name - Represents the name of the view which is going to crate.
• column1,column2 - Represents the included columns on view.
• WITH CHECK Option - Specifies to the SQL server that all modification on the view should satify the definition of the view.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Creating view for one table.

Requirement – Creating view employee_basic_info on employee_details table with three columns like emp_id, emp_name, designation. The query was as follows -

CREATE VIEW employee_basic_info AS
SELECT emp_id, emp_name, designation
FROM employee_details; 

By executing above query, we can create view. The output was shown as below –

Scenario – Creating view from multiple tables.

Requirement – Creating view employee_full_info on employee_details and department_details table. The query was as follows –

CREATE VIEW department AS
 SELECT employee_details.emp_id,
     employee_details.emp_name,
     department_details.dept_name,
     department_details.dept_location 
 FROM employee_details, department_details
 WHERE employee_details.dept_id = department.dept_id; 

By executing above query, we can create view from multiple tables. The output was shown as below –

Once view was created, we can run the query like normal table.

Scenario – Getting data from view.

Requirement – Fetch the data from employee_full_info view. The query is as shown below -

 SELECT * FROM employee_full_info; 

By executing above query, we can get all details from view. The output was shown as below -

Scenario – Creating view by using aggregate functions.

Requirement – Create view employee_aggr by using aggregate functions. The query was as follows -

CREATE VIEW employee (total employees, designation) AS
SELECT COUNT(emp_id), designation
FROM employee_details
GROUP BY designation; 

By executing above query, we can create view with aggregate function. The output was shown as follows –