Views In SQL
View:-
Advantages of using views:-
Syntax:-
Now, we create two tables named are tblEmpRecord and tblSalary and insert some records.
tblEmpRecord table
tblSalary table
Now, we want to get record as shown in below.
Now, let's create a view, using the JOINS query, we have just written query as shown in below.
Run the view:-We can run view as table as shown in below.
Updating OR Alter a View in SQL:-
Syntax:-
Example:-Now, we want to alter view. For this i alredy created a view named is vwGetEmpRecord. In this view will alter some columns or conditons as shown in below. In the view vwGetEmpRecord in which we add Address column from table tblSalary.
Run the view:-
Dropping a View in SQL:-
Syntax:-
How to look at view definition?
syntax:-
Column Level Security:- Salary is confidential information and we want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to this views, rather than the base tables.
Row Level Security:- For example, we want an end user, to have access only to CSE Department employees. If we grant him access to the underlying tblEmpRecord and tblSalary tables, he will be able to see, every department employees. To achieve this, we can create a view, which returns only CSE Department employees, and grant the user access to the view and not to the underlying table.
- The view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
- You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
- A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
Advantages of using views:-
- Views can be used to reduce the complexity of the database schema, for non IT users. The sample view, vWEmployeesByDepartment, hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.
- Views can be used as a mechanism to implement row and column level security.
- Views can be used to present only aggregated data and hide detailed data.
Syntax:-
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Now, we create two tables named are tblEmpRecord and tblSalary and insert some records.
----------tblEmpRecord table---------- CREATE TABLE tblEmpRecord ( EmpId int primary key,Name varchar(100),Department varchar(300),JoiningDate Datetime ) ----------INSERT SOME RECORDS---------- INSERT INTO tblEmpRecord VALUES(1001,'Santosh','IT','2011-05-18 17:45:03.660') INSERT INTO tblEmpRecord VALUES(1002,'Pooja','CSE','2013-09-16 15:45:08.650') INSERT INTO tblEmpRecord VALUES(1003,'Gagan','ECE','2011-10-19 19:25:03.660') INSERT INTO tblEmpRecord VALUES(1004,'Anjanee','MAC','2010-12-25 10:45:03.660') INSERT INTO tblEmpRecord VALUES(1005,'Suneeta','BCA','2011-05-20 15:15:03.665') ----------tblSalary table---------- CREATE TABLE tblSalary ( Id int primary key,Salary int,Gender varchar(30),Email varchar(200),Address varchar(200) ) ----------INSERT SOME RECORDS---------- INSERT INTO tblSalary VALUES(1001,12000,'Male','s@gmail.com','A-3033') INSERT INTO tblSalary VALUES(1002,11000,'Female','p@gmail.com','D-503') INSERT INTO tblSalary VALUES(1003,10000,'Female','g@gmail.com','P-333') INSERT INTO tblSalary VALUES(1005,13000,'Female','st@gmail.com','G-788')
Now, we want to get record as shown in below.
Now, let's create a view, using the JOINS query, we have just written query as shown in below.
CREATE VIEW vwGetEmpRecord AS SELECT e.Name,s.Gender,s.Email,s.Salary,e.JoiningDate FROM tblEmpRecord as e JOIN tblSalary s ON e.EmpId=s.Id
Run the view:-We can run view as table as shown in below.
SELECT * FROM vwGetEmpRecord
Updating OR Alter a View in SQL:-
Syntax:-
ALTER VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Example:-Now, we want to alter view. For this i alredy created a view named is vwGetEmpRecord. In this view will alter some columns or conditons as shown in below. In the view vwGetEmpRecord in which we add Address column from table tblSalary.
ALTER VIEW vwGetEmpRecord AS SELECT e.Name,s.Gender,s.Email,s.Salary,e.JoiningDate,s.Address FROM tblEmpRecord as e JOIN tblSalary s ON e.EmpId=s.Id
Run the view:-
SELECT * FROM vwGetEmpRecord
Dropping a View in SQL:-
Syntax:-
DROP VIEW view_name
How to look at view definition?
syntax:-
sp_helptext vWName
Column Level Security:- Salary is confidential information and we want to prevent access to that column. To achieve this, we can create a view, which excludes the Salary column, and then grant the end user access to this views, rather than the base tables.
CREATE VIEW vwGetEmpRecordHideSalary AS SELECT e.Name,s.Gender,s.Email,e.JoiningDate,s.Address FROM tblEmpRecord as e JOIN tblSalary s ON e.EmpId=s.Id
Row Level Security:- For example, we want an end user, to have access only to CSE Department employees. If we grant him access to the underlying tblEmpRecord and tblSalary tables, he will be able to see, every department employees. To achieve this, we can create a view, which returns only CSE Department employees, and grant the user access to the view and not to the underlying table.
CREATE VIEW vwGetEmpRecord_Department AS SELECT e.Name,s.Gender,s.Email,e.JoiningDate,s.Address FROM tblEmpRecord as e JOIN tblSalary s ON e.EmpId=s.Id WHERE e.Department='CSE'