CTE(Common_Table_Expression) in SQL server

## CREATE A DATABASE CALLED CTE
CREATE DATABASE CTE;

## Use CTE
USE CTE;

## NON_RECURSIVE CTE ##

## Create a table called Dept
CREATE TABLE dept
(
dept_id INT PRIMARY KEY NOT NULL,
dept_name nVARCHAR(20)
);
INSERT INTO dept VALUES(1,’IT’),(2,’Payroll’),(3,’HR’),(4,’Admin’);
SELECT * FROM dept;

## Create a table called emp
CREATE TABLE emp
(
id INT PRIMARY KEY NOT NULL,
name NVARCHAR(20),
gender NVARCHAR(10),
dept_id INT,
CONSTRAINT fk_deptid FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
INSERT INTO emp VALUES(1,’John’,’male’,3),(2,’Mike’,’male’,2),(3,’Rob’,’female’,1),(4,’Todd’,’male’,4),(5,’Sara’,’female’,1),(6,’Ben’,’male’,3);
SELECT * FROM emp;

SELECT * FROM DEPT;
SELECT * FROM EMP;

## WRITE A CTE TO GET THE TOTAL COUNT OF EMPLOYEES WHERE THE COUNT OF EMPLOYEES IN PARTICULAR DEPT > =2 AND COUNT AS PER THE DEPT NAME

WITH emp_count(department_name, department_id , total_employees)
AS
(
SELECT…