37. Real-World SMS Scenarios -- Complete Examples

Level: Project practice

ℹ️ What You'll Learn

• Complete enrollment workflow
• Grading and academic reporting
• Fee collection and payment tracking
• Attendance management and analysis
• Promotion and progression
• Report generation
• Practical debugging
• Production patterns

Theory -> practice. Real queries for SMS backend. This article teaches production patterns.

Scenario 1: Student Enrollment

Enroll new student with validations:

CREATE PROCEDURE sp_EnrollStudent
    @Name NVARCHAR(100),
    @RollNumber NVARCHAR(20),
    @ClassName NVARCHAR(10),
    @DateOfBirth DATE,
    @ParentName NVARCHAR(100),
    @ParentPhone NVARCHAR(20),
    @StudentId INT OUTPUT,
    @IsSuccess BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    SET @ErrorMessage = '';
    
    BEGIN TRY
        -- Validate class exists
        IF NOT EXISTS (SELECT 1 FROM Class WHERE ClassName = @ClassName)
        BEGIN
            THROW 50001, 'Class not found', 1;
        END;
        
        -- Check duplicate roll number
        IF EXISTS (SELECT 1 FROM Student WHERE RollNumber = @RollNumber AND ClassName = @ClassName)
        BEGIN
            THROW 50002, 'Roll number already exists in this class', 1;
        END;
        
        -- Check age (minimum 5 years)
        IF DATEDIFF(YEAR, @DateOfBirth, GETDATE()) < 5
        BEGIN
            THROW 50003, 'Student must be at least 5 years old', 1;
        END;
        
        -- Insert student
        INSERT INTO Student (Name, RollNumber, ClassName, DateOfBirth, ParentName, ParentPhone, Status)
        VALUES (@Name, @RollNumber, @ClassName, @DateOfBirth, @ParentName, @ParentPhone, 'Active');
        
        SET @StudentId = SCOPE_IDENTITY();
        SET @IsSuccess = 1;
        SET @ErrorMessage = 'Student enrolled successfully';
        
        -- Log enrollment
        INSERT INTO AuditLog (Action, EntityName, EntityId, ChangedBy, ChangedAt)
        VALUES ('Enroll', 'Student', @StudentId, SYSTEM_USER, GETDATE());
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

-- Use:
DECLARE @StuId INT, @Success BIT, @Msg NVARCHAR(500);
EXECUTE sp_EnrollStudent 
    @Name = 'Ravi Kumar',
    @RollNumber = '001',
    @ClassName = '10-A',
    @DateOfBirth = '2010-05-15',
    @ParentName = 'Raj Kumar',
    @ParentPhone = '9876543210',
    @StudentId = @StuId OUTPUT,
    @IsSuccess = @Success OUTPUT,
    @ErrorMessage = @Msg OUTPUT;
SELECT @StuId AS StudentId, @Success AS Success, @Msg AS Message;

Validates: Class, duplicate roll, age. Returns student ID.

Scenario 2: Record Exam Result

Insert exam result with validations:

CREATE PROCEDURE sp_RecordExamResult
    @StudentId INT,
    @ExamId INT,
    @MarksObtained DECIMAL(5,2),
    @IsAbsent BIT = 0,
    @IsSuccess BIT OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRY
        DECLARE @MaxMarks DECIMAL(5,2);
        
        -- Validate student
        IF NOT EXISTS (SELECT 1 FROM Student WHERE StudentId = @StudentId)
        BEGIN
            THROW 50001, 'Student not found', 1;
        END;
        
        -- Validate exam and get max marks
        IF NOT EXISTS (SELECT 1 FROM Exam WHERE ExamId = @ExamId)
        BEGIN
            THROW 50002, 'Exam not found', 1;
        END;
        
        SELECT @MaxMarks = MaxMarks FROM Exam WHERE ExamId = @ExamId;
        
        -- Validate marks
        IF @IsAbsent = 0 AND (@MarksObtained < 0 OR @MarksObtained > @MaxMarks)
        BEGIN
            THROW 50003, 'Marks out of range', 1;
        END;
        
        -- Check duplicate
        IF EXISTS (SELECT 1 FROM ExamResult WHERE StudentId = @StudentId AND ExamId = @ExamId)
        BEGIN
            THROW 50004, 'Result already recorded', 1;
        END;
        
        -- Insert result
        INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
        VALUES (@StudentId, @ExamId, CASE WHEN @IsAbsent = 1 THEN NULL ELSE @MarksObtained END, @IsAbsent);
        
        SET @IsSuccess = 1;
        SET @ErrorMessage = 'Result recorded successfully';
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Validates: Student, exam, marks range, no duplicate.

Scenario 3: Fee Collection Report

Monthly collection summary:

CREATE VIEW vw_FeeCollectionReport AS
SELECT 
    s.ClassName,
    MONTH(p.PaidOn) AS Month,
    COUNT(DISTINCT s.StudentId) AS StudentsPaid,
    SUM(p.Amount) AS AmountCollected,
    CAST(MONTH(p.PaidOn) AS VARCHAR(2)) + '/' + CAST(YEAR(p.PaidOn) AS VARCHAR(4)) AS MonthYear
FROM Student s
INNER JOIN FeePayment p ON s.StudentId = (
    SELECT StudentId FROM FeeAccount fa WHERE fa.FeeAccountId = p.FeeAccountId
)
WHERE YEAR(p.PaidOn) = YEAR(GETDATE())
GROUP BY s.ClassName, MONTH(p.PaidOn);

-- Use:
SELECT ClassName, Month, StudentsPaid, AmountCollected
FROM vw_FeeCollectionReport
ORDER BY Month;

Report: Collection by class and month.

Scenario 4: Student Progress Card

Complete semester report:

CREATE VIEW vw_StudentProgressCard AS
SELECT 
    s.StudentId,
    s.Name AS Student,
    s.ClassName,
    COUNT(r.ExamResultId) AS ExamsTaken,
    AVG(r.MarksObtained) AS AvgMarks,
    MAX(r.MarksObtained) AS BestMarks,
    MIN(r.MarksObtained) AS LowestMarks,
    CASE
        WHEN AVG(r.MarksObtained) >= 90 THEN 'A+'
        WHEN AVG(r.MarksObtained) >= 80 THEN 'A'
        WHEN AVG(r.MarksObtained) >= 70 THEN 'B'
        WHEN AVG(r.MarksObtained) >= 60 THEN 'C'
        ELSE 'F'
    END AS Grade,
    ROUND(
        (SUM(CASE WHEN r.MarksObtained >= 60 THEN 1 ELSE 0 END) * 100.0 / 
         COUNT(r.ExamResultId)), 2
    ) AS PassPercentage,
    COUNT(DISTINCT a.Date) AS ClassesAttended,
    ROUND(
        (SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
         COUNT(CASE WHEN a.Date IS NOT NULL THEN 1 END)), 2
    ) AS AttendancePercentage
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
LEFT JOIN Attendance a ON s.StudentId = a.StudentId
GROUP BY s.StudentId, s.Name, s.ClassName;

-- Use:
SELECT * FROM vw_StudentProgressCard WHERE ClassName = '10-A' ORDER BY Grade DESC;

Report: Academic performance, attendance, grades per student.

Scenario 5: Attendance Analysis

Identify absent students:

WITH AttendanceAnalysis AS (
    SELECT 
        s.StudentId,
        s.Name AS Student,
        s.ClassName,
        COUNT(DISTINCT a.Date) AS ClassesHeld,
        SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) AS Present,
        COUNT(a.AttendanceId) - SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) AS Absent,
        ROUND(
            (SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
             COUNT(a.AttendanceId)), 2
        ) AS AttendancePercentage
    FROM Student s
    LEFT JOIN Attendance a ON s.StudentId = a.StudentId
    WHERE MONTH(a.Date) = MONTH(GETDATE())
    GROUP BY s.StudentId, s.Name, s.ClassName
)
SELECT * FROM AttendanceAnalysis
WHERE AttendancePercentage < 75
ORDER BY AttendancePercentage;

Report: Students below 75% attendance this month.

Scenario 6: Promotion Logic

Promote students to next class:

CREATE PROCEDURE sp_PromoteStudents
    @CurrentClass NVARCHAR(10),
    @NextClass NVARCHAR(10),
    @MinAvgMarks DECIMAL(5,2) = 60,
    @MinAttendance DECIMAL(5,2) = 75,
    @PromotedCount INT OUTPUT
AS
BEGIN
    BEGIN TRY
        WITH EligibleStudents AS (
            SELECT 
                s.StudentId,
                AVG(r.MarksObtained) AS AvgMarks,
                ROUND(
                    (SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
                     COUNT(a.AttendanceId)), 2
                ) AS Attendance
            FROM Student s
            LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
            LEFT JOIN Attendance a ON s.StudentId = a.StudentId
            WHERE s.ClassName = @CurrentClass AND s.Status = 'Active'
            GROUP BY s.StudentId
            HAVING 
                AVG(r.MarksObtained) >= @MinAvgMarks
                AND ROUND(
                    (SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
                     COUNT(a.AttendanceId)), 2
                ) >= @MinAttendance
        )
        
        UPDATE Student
        SET ClassName = @NextClass, Status = 'Promoted'
        WHERE StudentId IN (SELECT StudentId FROM EligibleStudents);
        
        SET @PromotedCount = @@ROWCOUNT;
        
        INSERT INTO AuditLog (Action, EntityName, ChangedBy, ChangedAt)
        VALUES ('Promotion', 'Student', SYSTEM_USER, GETDATE());
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END;

-- Use:
DECLARE @Count INT;
EXECUTE sp_PromoteStudents '10-A', '11-A', 60, 75, @Count OUTPUT;
SELECT @Count AS StudentsPromoted;

Promotion: Marks >= 60, attendance >= 75%.

Scenario 7: Academic Performance Dashboard

Dashboard summary:

CREATE VIEW vw_AcademicDashboard AS
SELECT 
    'Overall' AS Category,
    COUNT(DISTINCT s.StudentId) AS TotalStudents,
    ROUND(AVG(r.MarksObtained), 2) AS AvgMarks,
    ROUND(MAX(r.MarksObtained), 2) AS HighestMarks,
    ROUND(MIN(r.MarksObtained), 2) AS LowestMarks,
    ROUND(
        (SUM(CASE WHEN r.MarksObtained >= 60 THEN 1 ELSE 0 END) * 100.0 / 
         COUNT(r.ExamResultId)), 2
    ) AS PassPercentage
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId

UNION ALL

SELECT 
    s.ClassName,
    COUNT(DISTINCT s.StudentId),
    ROUND(AVG(r.MarksObtained), 2),
    ROUND(MAX(r.MarksObtained), 2),
    ROUND(MIN(r.MarksObtained), 2),
    ROUND(
        (SUM(CASE WHEN r.MarksObtained >= 60 THEN 1 ELSE 0 END) * 100.0 / 
         COUNT(r.ExamResultId)), 2
    )
FROM Student s
LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
GROUP BY s.ClassName;

-- Use:
SELECT * FROM vw_AcademicDashboard;

Dashboard: School-wide and class-wise performance.

---

🎯 Q1: How do I prevent invalid data during enrollment?

Validate before insert:

• Class exists
• Roll number unique in class
• Age minimum (e.g., 5 years)

Use procedure with TRY-CATCH, THROW.

🎯 Q2: How do I record exam results safely?

Validate: Student exists, exam exists, marks in range (0-MaxMarks), no duplicate result.

Use transaction + stored procedure.

🎯 Q3: How do I generate fee collection reports?

View with JOINs:

• Student -> FeeAccount -> FeePayment
• GROUP BY class, month
• SUM amounts, count students

Monthly summary by class.

🎯 Q4: How do I track student progress?

View with aggregates:

• AVG marks (grade)
• COUNT exams taken
• Attendance percentage
• Pass/fail rate

Progress card per student.

🎯 Q5: How do I promote students?

CTE with eligibility:

• AVG marks >= threshold (e.g., 60)
• Attendance >= threshold (e.g., 75%)

Update ClassName for eligible students.

🎯 Q6: How do I identify attendance issues?

SELECT Student, AttendancePercentage
FROM vw_StudentAttendance
WHERE AttendancePercentage < 75
ORDER BY AttendancePercentage;

Find students below threshold, take action.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Real-world SMS scenarios and complete examples. Try these prompts:

• "Show enrollment procedure with validation (age, duplicate)"
• "How do I record exam result with duplicate check?"
• "Create fee collection report grouped by month and class"
• "How do I promote students based on marks and attendance?"
• "Quiz me: 5 questions about enrollment, grading, fee, promotion"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

38. Complex Business Logic -- Multi-Step Processes ->