**

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:

  1. Students
  2. Courses
  3. Instructors
  4. 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:

  1. students student_id (PK) first_name, last_name, email, date_of_birth
  2. instructors instructor_id (PK) first_name, last_name, email
  3. courses course_id (PK) course_name, description, instructor_id (FK)
  4. 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:

  1. 10 students
  2. 3 instructors
  3. 5 courses
  4. 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.

  1. 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;

  1. 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;

  1. 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;

  1. 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;

  1. 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:

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

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

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

Key Learnings

  1. Schema Design matters normalize wisely.
  2. Foreign Keys keep your data sane.
  3. Views simplify repeated queries.
  4. Triggers help with automation and audit logging.
  5. 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.