Example of Derived table, CTE, Views, Table Variable

SQL >   SQL Server Trigger >   Instead of delete trigger  

423

---

Answer:

Let's create the required Employee and Department tables, that we will be using for this demo.

SQL Script to create tblEmployee table:

CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Gender nvarchar(10),
  DepartmentId int
)

SQL Script to create tblDepartment table

 
CREATE TABLE tblDepartment
(
 DeptId int Primary Key,
 DeptName nvarchar(20)
)

Insert data into tblDepartment table

 
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 data into tblEmployee table

Insert into tblEmployee values (1,'Rambo', 'Male', 3)
Insert into tblEmployee values (2,'Raja', 'Male', 2)
Insert into tblEmployee values (3,'Roma', 'Female', 1)
Insert into tblEmployee values (4,'Ron', 'Male', 4)
Insert into tblEmployee values (5,'Sara', 'Female', 1)
Insert into tblEmployee values (6,'Azam', 'Male', 3)

Now, we want to write a query which would return the following output. The query should return, the Department Name and Total Number of employees,

---

---

Join Our telegram group to ask Questions

Click below button to join our groups.