Mastering SQL Through a Real-World Project: Building a Student Course Management System By Kelvin Ndirangu

Why SQL Projects Matter Learning SQL through tutorials is helpful, but nothing beats applying it to a real project. I built a Student Course Management System from scratch using SQL. This project helped me understand how real-world databases are structured, how data flows between tables, and how to write powerful queries to extract insights. In this blog post, I’ll Walk you through how I applied key SQL concepts like schema design, joins, aggregate functions, views, indexes, and triggers throughout this project. Project Overview: Student Course Management System This system was designed for an EdTech school to manage: Students Courses Instructors Enrollments and Grades Step 1: Designing the Database Schema The first challenge was designing a relational schema with the right relationships. Here are the main tables: students student_id (PK) first_name, last_name, email, date_of_birth instructors instructor_id (PK) first_name, last_name, email courses course_id (PK) course_name, description, instructor_id (FK) enrollments enrollment_id (PK) student_id, course_id (FKs) enrollment_date, grade Schema Lesson: Design your foreign keys to enforce data consistency. This prevents students from enrolling in courses that don’t exist or instructors from teaching phantom subjects. Step 2: Populating the Database I created sample data using INSERT statements for: 10 students 3 instructors 5 courses 15 enrollments with grades This gave me a realistic dataset to work with in the next step queries! Step 3: Writing Real-World SQL Queries Here’s where it got exciting. I wrote a series of queries that simulate the actual reporting needs of an EdTech platform. Students who enrolled in at least one course: SELECT DISTINCT s.first_name, s.last_name FROM students s JOIN enrollments e ON s.student_id = e.student_id; Students enrolled in more than two courses: SELECT s.first_name, s.last_name, COUNT(e.course_id) AS course_count FROM students s JOIN enrollments e ON s.student_id = e.student_id GROUP BY s.student_id HAVING COUNT(e.course_id) > 2; Courses with number of enrolled students: SELECT c.course_name, COUNT(e.student_id) AS total_students FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id GROUP BY c.course_name; Average grade per course: SELECT c.course_name, AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 WHEN 'F' THEN 0 END) AS avg_gpa FROM enrollments e JOIN courses c ON e.course_id = c.course_id GROUP BY c.course_name; Top 3 students by average grade: SELECT s.first_name, s.last_name, AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 WHEN 'F' THEN 0 END) AS avg_gpa FROM students s JOIN enrollments e ON s.student_id = e.student_id GROUP BY s.student_id ORDER BY avg_gpa DESC LIMIT 3; SQL Lessons: Use CASE statements for custom logic like grade conversion. JOIN is your best friend for combining data across tables. GROUP BY + HAVING is essential for filtering aggregates. Step 5: Hosting on GitHub I wrapped everything into a public GitHub repository with: SQL scripts README.md with setup instructions Entity Relationship Diagram (ERD) GitHub link: https://github.com/KELVINNDIRANGU/SQL-PROJECT-2 Key Learnings Schema Design matters normalize wisely. Foreign Keys keep your data sane. Views simplify repeated queries. Triggers help with automation and audit logging. Indexing is crucial for performance as your data grows. Conclusion This project transformed how I view SQL not as a list of commands, but as a tool to build real, functional systems. Whether you're preparing for a job, managing data, or automating reports, SQL is foundational.

May 3, 2025 - 22:46

Mastering SQL Through a Real-World Project: Building a Student Course Management System By Kelvin Ndirangu

Why SQL Projects Matter

**
Learning SQL through tutorials is helpful, but nothing beats applying it to a real project. I built a Student Course Management System from scratch using SQL. This project helped me understand how real-world databases are structured, how data flows between tables, and how to write powerful queries to extract insights.
In this blog post, I’ll Walk you through how I applied key SQL concepts like schema design, joins, aggregate functions, views, indexes, and triggers throughout this project.

Project Overview: Student Course Management System
This system was designed for an EdTech school to manage:

Students
Courses
Instructors
Enrollments and Grades

Step 1: Designing the Database Schema
The first challenge was designing a relational schema with the right relationships.
Here are the main tables:

students student_id (PK) first_name, last_name, email, date_of_birth
instructors instructor_id (PK) first_name, last_name, email
courses course_id (PK) course_name, description, instructor_id (FK)
enrollments enrollment_id (PK) student_id, course_id (FKs) enrollment_date, grade

Schema Lesson:
Design your foreign keys to enforce data consistency. This prevents students from enrolling in courses that don’t exist or instructors from teaching phantom subjects.

Step 2: Populating the Database
I created sample data using INSERT statements for:

10 students
3 instructors
5 courses
15 enrollments with grades This gave me a realistic dataset to work with in the next step queries!

Step 3: Writing Real-World SQL Queries
Here’s where it got exciting. I wrote a series of queries that simulate the actual reporting needs of an EdTech platform.

Students who enrolled in at least one course:

SELECT DISTINCT s.first_name, s.last_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id;

Students enrolled in more than two courses:

SELECT s.first_name, s.last_name, COUNT(e.course_id) AS course_count
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id
HAVING COUNT(e.course_id) > 2;

Courses with number of enrolled students:

SELECT c.course_name, COUNT(e.student_id) AS total_students
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

Average grade per course:

SELECT c.course_name,
AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'B' THEN 3
WHEN 'C' THEN 2
WHEN 'D' THEN 1
WHEN 'F' THEN 0
END) AS avg_gpa
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
GROUP BY c.course_name;

Top 3 students by average grade:

SELECT s.first_name, s.last_name,
AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'B' THEN 3
WHEN 'C' THEN 2
WHEN 'D' THEN 1
WHEN 'F' THEN 0
END) AS avg_gpa
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id
ORDER BY avg_gpa DESC
LIMIT 3;

SQL Lessons:

Use CASE statements for custom logic like grade conversion.
JOIN is your best friend for combining data across tables.
GROUP BY + HAVING is essential for filtering aggregates.

Step 5: Hosting on GitHub
I wrapped everything into a public GitHub repository with:

SQL scripts
README.md with setup instructions
Entity Relationship Diagram (ERD) GitHub link: https://github.com/KELVINNDIRANGU/SQL-PROJECT-2

Key Learnings

Schema Design matters normalize wisely.
Foreign Keys keep your data sane.
Views simplify repeated queries.
Triggers help with automation and audit logging.
Indexing is crucial for performance as your data grows.

Conclusion
This project transformed how I view SQL not as a list of commands, but as a tool to build real, functional systems. Whether you're preparing for a job, managing data, or automating reports, SQL is foundational.