Normalization

import sqlite3
con=sqlite3.connect(":memory:")
con.executescript('''CREATE TABLE s(id INT, name TEXT, dept TEXT, marks INT);
INSERT INTO s VALUES(1,'Rafi','CSE',88),(2,'Mim','EEE',92),(3,'Tanvir','CSE',75),(4,'Nabila','EEE',81),(5,'Sakib','CSE',95);''')
q='''SELECT name, dept, marks, RANK() OVER(PARTITION BY dept ORDER BY marks DESC) AS rnk,
 ROUND(AVG(marks) OVER(PARTITION BY dept),1) AS dept_avg FROM s ORDER BY dept, rnk'''
for r in con.execute(q): print(r)