Question-4 Marks [10]

TOPIC: SQL Stored procedures:

S_ID	Name	Gender	City	Attendance %	Quiz 1 (10)	Quiz 2 (10)	Quiz 3 (10)	Assign 1 (10)	Assign 2 (15)	Assign 3 (10)	Final (45)
101	Ella	F	California	69	5	8	6	5	12	7	34
201	Christian	M	Texas	88	6	7	7	5	11	6	43
301	Sofia	F	Arizona	68	5	2	5	8	10	3	26
401	Lillian	F	Maryland	82	6	3	4	5	9	7	34
501	John	M	California	60	7	5	8	9	8	6	41
601	Joseph	M	Hawaii	72	8	6	0	5	14	4	22

T_ID	CourseName	S_ID
1101	Digital marketing	101
1102	AI and data science	301
1103	Web development	401
1104	Cyber Security	201

CASE: The above tables are extracted an examination department of an institution. The examination department wants to have a consolidated total of students scores in each subject, where, the weightage of quizzes, assignments and attendance are 25%, 35% and 5% respectively. The remaining weightage belongs to the final term result. Also, they requested the IT department for a procedure which generates Letter Grades and respective Grade Points (as mentioned in the table below). As the institution has many campuses over the country, they also need to have a City-wise students progress for campus-wise comparison.

Marks	Grade	Points
88-100	A	4
81-87	B+	3.5
74-80	B	3
67-73	C+	2.5
60-66	C	2
Below 60	F	0