Mini Project: School Database Reports

Build a mini school DB: students, courses, teachers, enrollments. Seed realistic rows. Run all capstone queries locally.

Project setup — sample school database

Build a mini school DB: students, courses, teachers, enrollments. Seed realistic rows. Run all capstone queries locally.

Real-life example: Mini project is a term-end practical exam — build the schema, insert data, answer five questions with SQL.

Seed schema and sample data

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  grade_level INT,
  city VARCHAR(80)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100),
  credits INT
);

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students VALUES
  (1, 'Aisha Khan', 10, 'Delhi'),
  (2, 'Ravi Patel', 11, 'Ahmedabad'),
  (3, 'Meera Singh', 10, 'Pune');

INSERT INTO courses VALUES
  (1, 'Mathematics', 4),
  (2, 'Physics', 4),
  (3, 'English', 3);

INSERT INTO enrollments VALUES
  (101, 1, 1, 92.0),
  (102, 1, 3, 88.5),
  (103, 2, 2, 76.0),
  (104, 3, 1, 95.0);

Question 1 — Who tops each course?

Find the highest score per course with student name. Use JOIN + window RANK or subquery pattern.

Real-life example: Course toppers for the annual day announcement — one name per subject.

Top scorer per course (window function)

WITH ranked AS (
  SELECT
    c.course_name,
    s.full_name,
    e.score,
    RANK() OVER (PARTITION BY c.course_id ORDER BY e.score DESC) AS rk
  FROM enrollments e
  JOIN students s ON e.student_id = s.student_id
  JOIN courses c ON e.course_id = c.course_id
)
SELECT course_name, full_name, score
FROM ranked
WHERE rk = 1;

Question 2 — Students below class average in any course

Compare each enrollment score to that course's average using a subquery or JOIN to aggregated stats.

Real-life example: Remedial class list — anyone below their subject's average gets extra help.

Below course average

SELECT s.full_name, c.course_name, e.score, avg_scores.avg_score
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_id
) avg_scores ON e.course_id = avg_scores.course_id
WHERE e.score < avg_scores.avg_score;

Question 3 — Final dashboard query

One query pipeline: city, student count, average score, honor count (90+). Deliver as a view for reuse.

Real-life example: Dashboard is the principal's single-screen summary before the board meeting.

City dashboard view

CREATE VIEW city_dashboard AS
SELECT
  s.city,
  COUNT(DISTINCT s.student_id) AS students,
  ROUND(AVG(e.score), 2) AS avg_score,
  SUM(CASE WHEN e.score >= 90 THEN 1 ELSE 0 END) AS honor_rows
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city;

SELECT * FROM city_dashboard ORDER BY avg_score DESC;

Project checklist

You finished this SQL syllabus when you can write these without notes and explain JOIN vs WHERE vs HAVING.

Real-life example: Checklist is packing for graduation — schema, queries, /mcq/sql-mcq, and one portfolio screenshot.

Create schema with PRIMARY KEY and FOREIGN KEY
Insert at least 10 students and 15 enrollments
Write 5 reports: inner join, left join, group by, having, case
Add one INDEX and explain why
Complete /mcq/sql-mcq and review wrong answers