Student Performance & Learning Analytics System
Industry-Level SQL Project
Business Scenario: Analyze student performance, course popularity, instructor
effectiveness, attendance impact, revenue, and retention.
You're right. I only gave the questions earlier. For a proper industry-style SQL
project, you also need:
1. ✅ Business Scenario
2. ✅ ERD
3. ✅ Tables
4. ✅ Columns
5. ✅ Sample Data (Values)
6. ✅ SQL Questions
Let's build it properly.
🎓 Student Performance & Learning Analytics System
🏫 Business Scenario
An EdTech company wants to analyze:
Top-performing students
Most popular courses
Best-performing instructors
Relationship between attendance and marks
Revenue generation by courses
Student retention and engagement
ERD
Students
├── StudentID
Enrollments
├── CourseID
Courses
├── InstructorID
Instructors
Enrollments
├── EnrollmentID
│
Exams
Courses
├── CategoryID
CourseCategories
👨🎓 Table 1: Students
StudentID StudentName Gender City AdmissionDate
101 Amit Sharma Male Mumbai 2024-01-10
102 Priya Singh Female Delhi 2024-02-15
103 Rahul Verma Male Pune 2024-03-20
104 Sneha Patel Female Mumbai 2024-04-01
105 Arjun Kumar Male Bangalore 2024-05-12
👨🏫 Table 2: Instructors
InstructorID InstructorName Department
1 John Smith Data Science
2 Sarah Wilson Business Analytics
3 David Lee Programming
Table 3: CourseCategories
CategoryID CategoryName
1 Data Analytics
2 Programming
3 Machine Learning
📖 Table 4: Courses
CourseID CourseName Fee InstructorID CategoryID
201 SQL for Analysts 15000 1 1
202 Python Basics 18000 3 2
203 Power BI 20000 2 1
204 Machine Learning 25000 1 3
205 Advanced Python 22000 3 2
📝 Table 5: Enrollments
EnrollmentID StudentID CourseID EnrollmentDate
1001 101 201 2024-01-15
1002 101 203 2024-02-10
1003 102 202 2024-02-20
1004 103 201 2024-03-25
1005 104 204 2024-04-10
1006 105 205 2024-05-20
📊 Table 6: Exams
ExamID EnrollmentID Marks AttendancePercent
5001 1001 88 95
5002 1002 92 98
5003 1003 75 85
5004 1004 81 90
5005 1005 95 99
5006 1006 78 82
🔑 Primary Keys
Table Primary Key
Students StudentID
Instructors InstructorID
Courses CourseID
CourseCategories CategoryID
Enrollments EnrollmentID
Exams ExamID
🔗 Foreign Keys
Child Table Foreign Key Parent Table
Courses InstructorID Instructors
Courses CategoryID CourseCategories
Enrollments StudentID Students
Enrollments CourseID Courses
Exams EnrollmentID Enrollments
Why this project is good for interviews
With just these 6 tables, you can practice:
4–5 table JOINs
GROUP BY
HAVING
Subqueries
CTEs
RANK()
DENSE_RANK()
ROW_NUMBER()
Revenue KPIs
Student Performance KPIs
Retention Analysis
And all your 60+ SQL questions can be solved using this dataset. This is very
close to the type of relational model used in real EdTech companies.
SQL Practice Questions
Basic SQL
1. Display all students and their cities.
2. Find total number of students.
3. Show courses with fee greater than 500.
4. Find students admitted after 2024-01-01.
5. Display highest and lowest course fee.
JOINs
1. Display Student Name and Course Name.
2. Display Student Name, Course Name and Instructor Name.
3. Show student marks with course name.
4. Display course and category name.
5. Show instructors and number of students taught.
GROUP BY
1. Count students by city.
2. Find average marks by course.
3. Calculate revenue by course.
4. Count students by category.
5. Find average attendance by instructor.
HAVING
1. Courses with more than 50 enrollments.
2. Cities with more than 20 students.
3. Courses with average marks above 80.
4. Instructors teaching more than 3 courses.
5. Categories generating revenue above threshold.
Subqueries
1. Students above overall average marks.
2. Courses above average revenue.
3. Students in most popular course.
4. Instructors above institute average performance.
5. Students below average attendance.
CTEs
1. Top 10 students by average marks.
2. Highest revenue course.
3. Average attendance per course.
4. Students enrolled in multiple courses.
5. Instructor-wise average marks.
Window Functions
1. Rank students by marks.
2. Dense rank courses by enrollments.
3. Row number students by marks.
4. Top student in each course.
5. Highest revenue course in each category.
KPI Analysis
1. Calculate total revenue.
2. Average revenue per student.
3. Top 5 revenue-generating courses.
4. Revenue contribution by category.
5. Instructor contributing highest revenue.