📅 Portfolio Project

Project 1: School Database Analytics

Design and query a complete school database with students, teachers, subjects, grades, and attendance. Produce real analytics reports using JOINs, aggregation, window functions, and CTEs.

Core Tasks

60-90

Minutes

Tables

✓

Portfolio Ready

Schema

Build the Database

Start by creating all tables. Run these CREATE TABLE statements in order — note the foreign key dependencies.

SQL — Schema

-- 1. Teachers
CREATE TABLE teachers (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) UNIQUE,
  department VARCHAR(80),
  joined_on  DATE
);

-- 2. Subjects
CREATE TABLE subjects (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  teacher_id INT,
  credits    INT DEFAULT 3,
  FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

-- 3. Students
CREATE TABLE students (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) UNIQUE,
  age        INT,
  enrolled_on DATE DEFAULT (CURRENT_DATE)
);

-- 4. Grades (many students, many subjects)
CREATE TABLE grades (
  student_id  INT,
  subject_id  INT,
  score       DECIMAL(5,2),
  exam_date   DATE,
  PRIMARY KEY (student_id, subject_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

-- 5. Attendance
CREATE TABLE attendance (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  student_id  INT,
  subject_id  INT,
  att_date    DATE,
  status      ENUM('Present', 'Absent', 'Late'),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Task 1

Basic Reporting

Task 1

Enrollment & Score Summary

Write queries to count total students, find average score, and list the top 10 students by score.

SQL

-- Total students enrolled
SELECT COUNT(*) AS total_students FROM students;

-- Average score across all subjects
SELECT ROUND(AVG(score), 2) AS avg_score FROM grades;

-- Top 10 students by average score
SELECT
  s.name,
  ROUND(AVG(g.score), 2) AS avg_score,
  COUNT(g.subject_id)      AS subjects_taken
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
ORDER BY avg_score DESC
LIMIT 10;

Task 2

JOIN Queries

Task 2

Students with Teachers & Subjects

Join all five tables to show each student, their subjects, the teacher for each subject, and their score.

SQL

-- Full student → subject → teacher report
SELECT
  s.name    AS student,
  sub.name  AS subject,
  t.name    AS teacher,
  g.score,
  g.exam_date
FROM students s
JOIN grades g    ON s.id = g.student_id
JOIN subjects sub ON sub.id = g.subject_id
JOIN teachers t  ON t.id = sub.teacher_id
ORDER BY s.name, sub.name;

-- Students with NO grades recorded yet
SELECT s.name, s.email
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
WHERE g.student_id IS NULL;

Task 3

Analytics — Distribution & Attendance

Task 3

Grade Distribution & Attendance Rate

Use GROUP BY and CASE to build grade distribution. Calculate attendance rates per student. Find highest and lowest performers.

SQL

-- Grade distribution: count students per letter grade
SELECT
  CASE
    WHEN avg_score >= 90 THEN 'A+'
    WHEN avg_score >= 80 THEN 'A'
    WHEN avg_score >= 70 THEN 'B'
    WHEN avg_score >= 60 THEN 'C'
    ELSE 'F'
  END AS grade,
  COUNT(*) AS student_count
FROM (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades GROUP BY student_id
) graded
GROUP BY grade
ORDER BY grade;

-- Attendance rate per student (% present)
SELECT
  s.name,
  COUNT(*)                                                   AS total_classes,
  SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END)     AS present_count,
  ROUND(SUM(CASE WHEN a.status = 'Present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS attendance_pct
FROM students s
JOIN attendance a ON s.id = a.student_id
GROUP BY s.id, s.name
ORDER BY attendance_pct DESC;

-- Best and worst performer per subject
SELECT
  sub.name AS subject,
  MAX(g.score) AS highest,
  MIN(g.score) AS lowest,
  ROUND(AVG(g.score),2) AS avg_score
FROM grades g
JOIN subjects sub ON sub.id = g.subject_id
GROUP BY sub.id, sub.name
ORDER BY avg_score DESC;

⭐ Stretch Goals

Use a window function to rank students within each subject by score (RANK() OVER PARTITION BY subject_id)

Write a CTE that first calculates per-student averages, then filters students below the overall average

Create a stored procedure GenerateReportCard(student_id INT) that returns all subjects, scores, and letter grades for one student

Add an index on grades(student_id) and grades(subject_id), then EXPLAIN your JOIN query to verify they are used

Create a view student_summary that shows each student's name, average score, attendance rate, and overall rank