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