Learn Sql server in hindi/urdu part-20(Triggers IN SQL SERVER)

select * from sys.tables


-----------------------------------------------------------------------------
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)
----------------------------------------------------------------------------------------------

CREATE TABLE tblEmployeeAudit
(
  Id int identity(1,1) primary key,
  AuditData nvarchar(1000)
)
--------------------------------------------------------------------------------------------
CREATE TRIGGER tr_tblEMployee_ForInsert
ON tblEmployee
FOR INSERT
AS
BEGIN
 Declare @Id int
 Select @Id = Id from inserted

 insert into tblEmployeeAudit
 values('New employee with Id  = ' + Cast(@Id as nvarchar(5)) + ' is added at ' + cast(Getdate() as nvarchar(20)))
END
-------------------------------------------------------------------------------------------------------------
CREATE TRIGGER tr_tblEMployee_ForDelete
ON tblEmployee
FOR DELETE
AS
BEGIN
 Declare @Id int
 Select @Id = Id from deleted

 insert into tblEmployeeAudit
 values('An existing employee with Id  = ' + Cast(@Id as nvarchar(5)) + ' is deleted at ' + Cast(Getdate() as nvarchar(20)))
END



-------------------------------------------------------------------------------------------------------------


Create trigger tr_tblEmployee_ForUpdate2
on tblEmployee
for Update
as
Begin


 Declare @Id int
 Select @Id = Id from inserted


 insert into tblEmployeeAudit
 values('An existing employee with Id  = ' + Cast(@Id as nvarchar(5)) + ' is updated at ' + Cast(Getdate() as nvarchar(20)))

 Select * from deleted
 Select * from inserted


End


-------------------------------------------------------------------------------------------------------------

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

Learn Sql server in hindi/urdu part-16(String fucntions-2 IN SQL SERVER)

Select LTRIM('      Hello')





Select RTRIM (LTRIM('  Hello   '))



Select LOWER('CONVERT This String Into Lower Case')

Select UPPER('CONVERT This String Into Lower Case')

Select REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Select LEN('ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Learn Sql server in hindi/urdu part-15(String fucntions IN SQL SERVER)

use stringfunction





print ASCII('A')

print CHAR(51)









declare @number int

set @number=65

while(@number<=90)

begin



print CHAR(@number)



set @number=@number+1

end

Learn Sql server in hindi/urdu part-15(String fucntions IN SQL SERVER)

use stringfunction





print ASCII('A')

print CHAR(51)









declare @number int

set @number=65

while(@number<=90)

begin



print CHAR(@number)



set @number=@number+1

end

Learn Sql server in hindi/urdu part-14(Union and Union All)

--The UNION operator is used to combine the result-set of two or more SELECT statements.



--Each SELECT statement within UNION must have the same number of columns

--The columns must also have similar data types

--The columns in each SELECT statement must also be in the same order





create table tblIndiaCustomers

(

id int identity,

Name nvarchar(20),

email nvarchar(20)

)

create table tblUKCustomers

(

id int identity,

Name nvarchar(20),

email nvarchar(20)

)





insert into tblIndiaCustomers

values('Aditi','Aditi@yahoo.com')



insert into tblIndiaCustomers

values('Rahul','Rahul@yahoo.com')



insert into tblIndiaCustomers

values('Sundar','Sundar@yahoo.com')



insert into tblIndiaCustomers

values('Ganesh','Ganesh@yahoo.com')





----------------------------------------



insert into tblUKCustomers

values('Mark','Mark@hotmail.com')



insert into tblUKCustomers

values('Ben','Ben@hotmail.com')



insert into tblUKCustomers

values('Linda','Linda@hotmail.com')



insert into tblUKCustomers

values('Suzan','Suzan@hotmail.com')



----------------------------------





select * from tblIndiaCustomers

union

select * from tblUKCustomers





select * from tblIndiaCustomers

union all

select * from tblUKCustomers