✏️ Explanatory Question

Example of Derived table, CTE, Views, Table Variable

👁 460 Views
📘 Detailed Answer
No previous question
No next question
💡

Answer with Explanation

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,

No previous question
No next question