SQL in 30 days for a final-year student: a day-by-day plan

import sqlite3, random

random.seed(7)
con = sqlite3.connect("college.db")
cur = con.cursor()
cur.executescript("""
DROP TABLE IF EXISTS students; DROP TABLE IF EXISTS courses; DROP TABLE IF EXISTS enrolments;
CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT, branch TEXT, city TEXT, join_year INTEGER);
CREATE TABLE courses (course_id TEXT PRIMARY KEY, title TEXT, credits INTEGER);
CREATE TABLE enrolments (student_id INTEGER, course_id TEXT, semester INTEGER, marks INTEGER);
""")
branches = ["CSE", "ECE", "ME", "CE", "BCom"]
cities = ["Noida", "Ghaziabad", "Delhi", "Meerut", None]
cur.executemany("INSERT INTO students VALUES (?,?,?,?,?)",
    [(i, f"Student{i:03d}", random.choice(branches), random.choice(cities), random.choice([2023, 2024]))
     for i in range(1, 121)])
cur.executemany("INSERT INTO courses VALUES (?,?,?)",
    [("DB101", "Databases", 4), ("ST201", "Statistics", 3), ("PY101", "Python", 4), ("EC105", "Economics", 3)])
rows = []
for sid in range(1, 121):
    for cid in random.sample(["DB101", "ST201", "PY101", "EC105"], k=random.randint(1, 4)):
        marks = random.randint(25, 98) if random.random() > 0.05 else None   # a few missing marks
        rows.append((sid, cid, random.choice([5, 6]), marks))
cur.executemany("INSERT INTO enrolments VALUES (?,?,?,?)", rows)
con.commit()
print(cur.execute("SELECT COUNT(*) FROM students").fetchone()[0], "students,",
      cur.execute("SELECT COUNT(*) FROM enrolments").fetchone()[0], "enrolments")
con.close()

SELECT student_id, name, city
FROM students
WHERE city IN ('Noida', 'Delhi') AND join_year = 2024
ORDER BY name;

SELECT course_id, COUNT(marks) AS marked, ROUND(AVG(marks), 1) AS avg_marks
FROM enrolments
GROUP BY course_id
HAVING COUNT(marks) >= 50
ORDER BY avg_marks DESC;

SELECT s.branch,
       ROUND(SUM(e.marks * c.credits) * 1.0 / SUM(c.credits), 1) AS weighted_avg
FROM enrolments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
WHERE e.marks IS NOT NULL
GROUP BY s.branch
ORDER BY weighted_avg DESC;

WITH ranked AS (
  SELECT course_id, student_id, marks,
         ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY marks DESC, student_id) AS rn
  FROM enrolments
  WHERE marks IS NOT NULL
)
SELECT course_id, student_id, marks
FROM ranked
WHERE rn <= 2
ORDER BY course_id, rn;