Example of Derived table, CTE, Views, Table Variable

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,