1
/*=================================================================================
USING TEMP TABLES
=================================================================================
*/
--tblMarks
-- ↓
--#Temp1 → Student Average
-- ↓
--#Temp2 → Students Above Average
-- ↓
--#Temp3 → Add Attendance
-- ↓
--#Temp4 → Add Department
-- ↓
--#Temp5 → Categorize Performance
-- ↓
--#Temp6 → Final Eligible Students
-- ↓
--Final Report
--Scenario
--Management wants to identify high-performing students.
--Rules:
--Calculate each student's average marks.
--Find students whose average is greater than 70.
--Add attendance.
--Keep students with attendance ≥ 80%.
--Add department information.
--Categorize students:
--90+ → Excellent
--80–89 → Very Good
--70–79 → Good
--Produce final report.
--Calculate Average marks
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
;
--STEP 2 — Insert into #Temp1
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
INTO
#Temp1
FROM
tblMarks
GROUP BY
StudentID
;
Select
*
from
#Temp1
--STEP 3 — Filter and insert into #Temp2
SELECT
StudentID
,
AverageMarks
INTO
#Temp2
FROM
#Temp1
WHERE
AverageMarks
>=
70
;
Select
*
from
#Temp2
--STEP 4 — Add Attendance and create #Temp3
SELECT
T
.
StudentID
,
T
.
AverageMarks
,
A
.
AttendancePercentage
INTO
#Temp3
FROM
#Temp2 T
INNER JOIN
tblAttendance A
ON
T
.
StudentID
=
A
.
StudentID
;
Select
*
from
#Temp3
--STEP 5 — Filter Attendance and create #Temp4
2
SELECT
StudentID
,
AverageMarks
,
AttendancePercentage
INTO
#Temp4
FROM
#Temp3
WHERE
AttendancePercentage
>=
80
;
Select
*
from
#Temp4
--STEP 6 — Add Department and create #Temp5
SELECT
T
.
StudentID
,
S
.
StudentName
,
D
.
DepartmentName
,
T
.
AverageMarks
,
T
.
AttendancePercentage
INTO
#Temp5
FROM
#Temp4 T
INNER JOIN
tblStudent S
ON
T
.
StudentID
=
S
.
StudentID
INNER JOIN
tblDepartment D
ON
S
.
DepartmentID
=
D
.
DepartmentID
;
Select
*
from
#Temp5
--STEP 7 — Create #Temp6 with Performance Category
SELECT
StudentID
,
StudentName
,
DepartmentName
,
AverageMarks
,
AttendancePercentage
,
CASE
WHEN
AverageMarks
>=
90
THEN
'Excellent'
WHEN
AverageMarks
>=
80
THEN
'Very Good'
WHEN
AverageMarks
>=
70
THEN
'Good'
ELSE
'Needs Improvement'
END AS
PerformanceCategory
INTO
#Temp6
FROM
#Temp5
;
Select
*
from
#Temp6
--STEP 8 — One more Temp Table: #Temp7 >> Let's say management now wants only Very Good and Excellent students.
SELECT
StudentID
,
StudentName
,
DepartmentName
,
AverageMarks
,
AttendancePercentage
,
PerformanceCategory
INTO
#Temp7
FROM
#Temp6
WHERE
PerformanceCategory
IN (
'Excellent'
,
'Very Good'
);
select
*
from
#Temp7
--This is the important teaching diagram:
--
tblMarks
--
│
--
▼
--
Calculate Average
--
│
--
▼
--
#Temp1
--
│
--
▼
--
Average >= 70
--
│
--
▼
--
#Temp2
--
│
--
▼
-- JOIN Attendance
--
│
--
▼
--
#Temp3
--
│
--
▼
3
-- Attendance >= 80
--
│
--
▼
--
#Temp4
--
│
--
▼
--JOIN Student + Department
--
│
--
▼
--
#Temp5
--
│
--
▼
--Calculate Performance
--
│
--
▼
--
#Temp6
--
│
--
▼
--Excellent / Very Good
--
│
--
▼
--
#Temp7
--
│
--
▼
--
FINAL REPORT
-- All steps like this
-- 1
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
INTO
#Temp1
FROM
tblMarks
GROUP BY
StudentID
;
-- 2
SELECT
*
INTO
#Temp2
FROM
#Temp1
WHERE
AverageMarks
>=
70
;
-- 3
SELECT
T
.
StudentID
,
T
.
AverageMarks
,
A
.
AttendancePercentage
INTO
#Temp3
FROM
#Temp2 T
INNER JOIN
tblAttendance A
ON
T
.
StudentID
=
A
.
StudentID
;
-- 4
SELECT
*
INTO
#Temp4
FROM
#Temp3
WHERE
AttendancePercentage
>=
80
;
-- 5
SELECT
T
.
StudentID
,
S
.
StudentName
,
D
.
DepartmentName
,
T
.
AverageMarks
,
T
.
AttendancePercentage
INTO
#Temp5
FROM
#Temp4 T
INNER JOIN
tblStudent S
ON
T
.
StudentID
=
S
.
StudentID
INNER JOIN
tblDepartment D
ON
S
.
DepartmentID
=
D
.
DepartmentID
;
-- 6
SELECT
*,
CASE
WHEN
AverageMarks
>=
90
THEN
'Excellent'
WHEN
AverageMarks
>=
80
THEN
'Very Good'
WHEN
AverageMarks
>=
70
THEN
'Good'
4
ELSE
'Needs Improvement'
END AS
PerformanceCategory
INTO
#Temp6
FROM
#Temp5
;
-- 7
SELECT
*
INTO
#Temp7
FROM
#Temp6
WHERE
PerformanceCategory
IN (
'Excellent'
,
'Very Good'
);
-- FINAL
SELECT
*
FROM
#Temp7
;
--And finally clean them up:
DROP TABLE
#Temp1
;
DROP TABLE
#Temp2
;
DROP TABLE
#Temp3
;
DROP TABLE
#Temp4
;
DROP TABLE
#Temp5
;
DROP TABLE
#Temp6
;
DROP TABLE
#Temp7
;
/*=================================================================================
USING CTE
=================================================================================
*/
--Step 1 — Student Average
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
)
---------------------------------------
--Step 2 — Average >= 70
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
SELECT
*
FROM
StudentAverage
WHERE
AverageMarks
>=
70
)
---------------------------------------------------
--Step 3 — Add Attendance
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
SELECT
*
FROM
StudentAverage
WHERE
AverageMarks
>=
70
),
StudentsWithAttendance
AS
(
SELECT
5
S
.
StudentID
,
S
.
AverageMarks
,
A
.
AttendancePercentage
FROM
StudentsAbove70 S
INNER JOIN
tblAttendance A
ON
S
.
StudentID
=
A
.
StudentID
)
----------------------------------------------------
--Step 4 — Attendance >= 80
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
SELECT
*
FROM
StudentAverage
WHERE
AverageMarks
>=
70
),
StudentsWithAttendance
AS
(
SELECT
S
.
StudentID
,
S
.
AverageMarks
,
A
.
AttendancePercentage
FROM
StudentsAbove70 S
INNER JOIN
tblAttendance A
ON
S
.
StudentID
=
A
.
StudentID
),
EligibleStudents
AS
(
SELECT
*
FROM
StudentsWithAttendance
WHERE
AttendancePercentage
>=
80
)
----------------------------------------------------
--Step 5 — Step 5 — Add Student + Department
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
SELECT
*
FROM
StudentAverage
WHERE
AverageMarks
>=
70
),
StudentsWithAttendance
AS
(
SELECT
S
.
StudentID
,
S
.
AverageMarks
,
A
.
AttendancePercentage
FROM
StudentsAbove70 S
INNER JOIN
tblAttendance A
ON
S
.
StudentID
=
A
.
StudentID
),
EligibleStudents
AS
(
SELECT
*
FROM
StudentsWithAttendance
WHERE
AttendancePercentage
>=
80
),
StudentDetails
AS
(
6
SELECT
E
.
StudentID
,
S
.
StudentName
,
D
.
DepartmentName
,
E
.
AverageMarks
,
E
.
AttendancePercentage
FROM
EligibleStudents E
INNER JOIN
tblStudent S
ON
E
.
StudentID
=
S
.
StudentID
INNER JOIN
tblDepartment D
ON
S
.
DepartmentID
=
D
.
DepartmentID
)
----------------------------------------------------
--Step 6 — Step 6 — Performance Category
WITH
StudentAverage
AS
(
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
SELECT
*
FROM
StudentAverage
WHERE
AverageMarks
>=
70
),
StudentsWithAttendance
AS
(
SELECT
S
.
StudentID
,
S
.
AverageMarks
,
A
.
AttendancePercentage
FROM
StudentsAbove70 S
INNER JOIN
tblAttendance A
ON
S
.
StudentID
=
A
.
StudentID
),
EligibleStudents
AS
(
SELECT
*
FROM
StudentsWithAttendance
WHERE
AttendancePercentage
>=
80
),
StudentDetails
AS
(
SELECT
E
.
StudentID
,
S
.
StudentName
,
D
.
DepartmentName
,
E
.
AverageMarks
,
E
.
AttendancePercentage
FROM
EligibleStudents E
INNER JOIN
tblStudent S
ON
E
.
StudentID
=
S
.
StudentID
INNER JOIN
tblDepartment D
ON
S
.
DepartmentID
=
D
.
DepartmentID
),
StudentPerformance
AS
(
SELECT
*,
CASE
WHEN
AverageMarks
>=
90
THEN
'Excellent'
WHEN
AverageMarks
>=
80
THEN
'Very Good'
WHEN
AverageMarks
>=
70
THEN
'Good'
ELSE
'Needs Improvement'
END AS
PerformanceCategory
FROM
StudentDetails
)
----------------------------------------------------
--Step 7 — Step 7 — Final Filter
SELECT
*
FROM
StudentPerformance
7
WHERE
PerformanceCategory
IN (
'Excellent'
,
'Very Good'
);
--- Complete CTE Query
-- Now give your students the complete version.
WITH
StudentAverage
AS
(
-- STEP 1
SELECT
StudentID
,
AVG
(
Marks
)
AS
AverageMarks
FROM
tblMarks
GROUP BY
StudentID
),
StudentsAbove70
AS
(
-- STEP 2
SELECT
StudentID
,
AverageMarks
FROM
StudentAverage
WHERE
AverageMarks
>=
70
),
StudentsWithAttendance
AS
(
-- STEP 3
SELECT
S
.
StudentID
,
S
.
AverageMarks
,
A
.
AttendancePercentage
FROM
StudentsAbove70 S
INNER JOIN
tblAttendance A
ON
S
.
StudentID
=
A
.
StudentID
),
EligibleStudents
AS
(
-- STEP 4
SELECT
StudentID
,
AverageMarks
,
AttendancePercentage
FROM
StudentsWithAttendance
WHERE
AttendancePercentage
>=
80
),
StudentDetails
AS
(
-- STEP 5
SELECT
E
.
StudentID
,
S
.
StudentName
,
D
.
DepartmentName
,
E
.
AverageMarks
,
E
.
AttendancePercentage
FROM
EligibleStudents E
INNER JOIN
tblStudent S
ON
E
.
StudentID
=
S
.
StudentID
INNER JOIN
tblDepartment D
ON
S
.
DepartmentID
=
D
.
DepartmentID
),
StudentPerformance
AS
(
-- STEP 6
SELECT
StudentID
,
StudentName
,
DepartmentName
,
AverageMarks
,
AttendancePercentage
,
CASE
WHEN
AverageMarks
>=
90
THEN
'Excellent'
WHEN
AverageMarks
>=
80
THEN
'Very Good'
WHEN
AverageMarks
>=
70
THEN
'Good'
ELSE
'Needs Improvement'
END AS
PerformanceCategory
8
FROM
StudentDetails
)
-- STEP 7
SELECT
StudentID
,
StudentName
,
DepartmentName
,
AverageMarks
,
AttendancePercentage
,
PerformanceCategory
FROM
StudentPerformance
WHERE
PerformanceCategory
IN (
'Excellent'
,
'Very Good'
);
--The most important comparison
--Put these two diagrams side-by-side on the board.
--The most important comparison
--Put these two diagrams side-by-side on the board.
--TEMP TABLE
--tblMarks
-- ↓
--Query
-- ↓
--#Temp1
-- ↓
--Query
-- ↓
--#Temp2
-- ↓
--Query
-- ↓
--#Temp3
-- ↓
--Query
-- ↓
--#Temp4
-- ↓
--Query
-- ↓
--#Temp5
-- ↓
--Query
-- ↓
--#Temp6
-- ↓
--Query
-- ↓
--#Temp7
-- ↓
--Final Result
--CTE
--tblMarks
-- ↓
--StudentAverage
-- ↓
--StudentsAbove70
-- ↓
--StudentsWithAttendance
-- ↓
--EligibleStudents
-- ↓
--StudentDetails
-- ↓
--StudentPerformance
-- ↓
--Final Result
--tblStudent
StudentID
StudentName DepartmentID
City
1001
Amit Vantil 101 Pune
1002
Rahul Patil 101 Hyderabad
1003
Priya Deshmukh
102 Pune
1004
Sneha Kulkarni
102 Delhi
1005
Rohit Jadhav
103 Pune
1006
Neha Joshi
103 Mumbai
1007
Akash Shinde
104 Delhi
1008
Pooja Pawar 104 Bangalore
1009
Vikas More
105 Pune
9
1010
Kavita Chavan
105 Delhi
1011
Sagar Patil 101 Bangalore
1012
Meena Joshi 102 Chennai
--tblMarks
StudentID
Subject
Marks
1001
SQL
85
1001
Python
90
1001
Power
BI
80
1002
SQL
70
1002
Python
75
1002
Power
BI
65
1003
SQL
92
1003
Python
88
1003
Power
BI
95
1004
SQL
60
1004
Python
72
1004
Power
BI
68
1005
SQL
78
1005
Python
82
1005
Power
BI
75
1006
SQL
65
1006
Python
70
1006
Power
BI
68
1007
SQL
88
1007
Python
91
1007
Power
BI
86
1008
SQL
74
1008
Python
79
1008
Power
BI
72
1009
SQL
55
1009
Python
62
1009
Power
BI
58
1010
SQL
90
1010
Python
85
1010
Power
BI
88
1011
SQL
82
1011
Python
84
1011
Power
BI
80
1012
SQL
68
1012
Python
73
1012
Power
BI
70
--tblDepartment
DepartmentID
DepartmentName
101 Computer Science
102 Mechanical
103 Civil
104 Electronics
105 Information Technology
--tblAttendance
StudentID
AttendancePercentage
1001
95
1002
82
1003
96
1004
78
1005
88
1006
75
1007
92
1008
85
1009
65
1010
94
1011
89
1012
80