Home / Questions / Example of Derived table, CTE, Views, Table Variable
Explanatory Question

Example of Derived table, CTE, Views, Table Variable

👁 460 Views
📘 Detailed Answer
🕒 Easy to Read
Read the answer carefully and go through the related questions on the right side to improve your understanding of this topic.

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,