Learn Sql server in hindi/urdu part-18(Updateable Views IN SQL SERVER)

CREATE TABLE tblEmployee

(

  Id int Primary Key,

  Name nvarchar(30),

  Salary int,

  Gender nvarchar(10),

  DepartmentId int

)





Insert into tblEmployee values (1,'John', 5000, 'Male', 3)

Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)

Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)

Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)

Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)

Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)









Create view vWEmployeesDataExceptSalary

as

Select Id, Name, Gender, DepartmentId

from tblEmployee





Select * from vWEmployeesDataExceptSalary

select * from tblEmployee





Update vWEmployeesDataExceptSalary



Set Name = 'salman' Where Id = 2





Update vWEmployeesDataExceptSalary

set salary=20000 where id=2

Learn Sql server in hindi/urdu part-17(Views/virtual table IN SQL SERVER)

--What is a View?
--A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.


CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)
CREATE TABLE tblDepartment
(
 DeptId int Primary Key,
 DeptName nvarchar(20)
)

Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')


Insert into tblEmployee values (1,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)
Insert into tblEmployee values (7,'salman',51800, 'Male', 4)



select * from tblDepartment
select * from tblEmployee


create view viewforall
as
select * from tblEmployee


select * from viewforall

create view viewforreception
as
select id,Name,Gender,DepartmentId from tblEmployee

select * from viewforreception



create view viewforrecpetionwithdeptname
as

select e.Id,e.Name,e.Gender,d.DeptName from tblEmployee e inner join tblDepartment d on d.DeptId=e.DepartmentId


select * from viewforrecpetionwithdeptname



Create View vWEmployeesCountByDepartment
as
Select DeptName, COUNT(Id) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Group By DeptName


select * from tblDepartment
select * from tblEmployee


select * from vWEmployeesCountByDepartment