Simple View
When we create a view on a single table, it is called simple view.
--Now Insert data to table Employee_Test
Insert into Employee_Test values ('Amit','PHP',12000,'SE');
Insert into Employee_Test values ('Mohan','ASP.NET',15000,'TL');
Insert into Employee_Test values ('Avin','C#',14000,'SE');
Insert into Employee_Test values ('Manoj','JAVA',22000,'SSE');
Insert into Employee_Test values ('Riyaz','VB',18000,'TH');
-- Now create view on single table Employee_Test
create VIEW vw_Employee_Test
AS
Select Emp_ID ,Emp_Name ,Emp_Designation
From Employee_Test
-- Query view like as table
Select * from vw_Employee_Test
In simple view we can insert, update, delete data. We can only insert data in simple view if we have primary key and all not null fields in the view.
-- Insert data to view vw_Employee_Test
insert into vw_Employee_Test(Emp_Name, Emp_Designation) values ('Shailu','SSE')
-- Now see the affected view
Select * from vw_Employee_Test
-- Update data to view vw_Employee_Test
Update vw_Employee_Test set Emp_Name = 'Pawan' where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test
-- Delete data from view vw_Employee_Test
delete from vw_Employee_Test where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test
Complex View
When we create a view on more than one table, it is called complex view.
--Create another table
create table Personal_Info
(
Emp_Name varchar(55),
FName varchar(55),
DOB varchar(55),
Address varchar(55),
Mobile int,
State varchar(55)
)
-- Now Insert data
Insert into Personal_Info values ('G.Chaudary','22-10-1985','Ghaziabad',96548922,'UP');
Insert into Personal_Info values ('B.S.Chauhan','02-07-1986','Haridwar',96548200,'UK');
Insert into Personal_Info values ('A.Panwar','30-04-1987','Noida',97437821,'UP');
Insert into Personal_Info values ('H.C.Patak','20-07-1986','Rampur',80109747,'UP');
Insert into Personal_Info values ('M.Shekh','21-10-1985','Delhi',96547954,'Delhi');
-- Now create view on two tables Employee_Test and Personal_Info
Create VIEW vw_Employee_Personal_Info
As
Select e.Emp_ID, e.Emp_Name,e.Emp_Designation,p.DOB,p.Mobile
From Employee_Test e INNER JOIN Personal_Info p
On e.Emp_Name = p. Emp_Name
-- Now Query view like as table
Select * from vw_Employee_Personal_Info
We can only update data in complex view. We can't insert data in complex view.
--Update view
update vw_Employee_Personal_Info set Emp_Designation = 'SSE' where Emp_ID = 3
-- See affected view
Select * from vw_Employee_Personal_Info
