Published / updated29 Aug 2026

SQL Server Foundation

Before you start

You need: stage 2. You will store the objects you modelled in C#.

Time: 30–36 classes.

Learning objective

Design the School Management System schema and write the queries, joins and stored procedures the application will call.

Topics

• Tables, data types, keys and constraints
• CRUD, filtering and sorting
• Aggregate, string and date functions
• Joins across several tables
• Grouping, HAVING and subqueries
• Views and stored procedures
• Transactions and indexes
• Normalisation

What this stage covers

The database outlives the application. The school portal will be rewritten in React, then in whatever comes next; the Student table will still be there. A schema mistake is the most expensive kind, because fixing it means migrating data that is already live.

Concept	Used later in
Data types	Entity properties, stage 4
Primary and foreign keys	Relationships and JOINs
Composite unique constraints	Multi-tenancy, throughout
WHERE filtering	Every repository method
Joins	The receipt, the defaulter report
Stored procedures	Called by Dapper, stage 4
Transactions	Recording a payment atomically, stage 5
Indexes	Making a search meet its two-second criterion

Three rules established here recur in every remaining stage:

• DECIMAL(18,2) for money, never FLOAT. The same rule as decimal in C#, for the same reason.
• Uniqueness is composite: UNIQUE (SchoolId, RollNumber), never RollNumber alone. Two schools may legitimately both issue NCA-2024-0012.
• SchoolId in the WHERE clause of every query. Omit it and one school reads another's records, with no error and a 200 response.

Worked flow: the fee receipt

The receipt needs three tables joined, filtered to one school.

CREATE TABLE FeeAccount
(
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    PublicId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    SchoolId        INT NOT NULL,
    StudentId       INT NOT NULL,
    AcademicYear    NVARCHAR(9) NOT NULL,
    TotalFees       DECIMAL(18,2) NOT NULL,
    PaidAmount      DECIMAL(18,2) NOT NULL DEFAULT 0,
    DiscountAmount  DECIMAL(18,2) NOT NULL DEFAULT 0,
    DueDate         DATE NOT NULL,

    CONSTRAINT FK_FeeAccount_Student FOREIGN KEY (StudentId) REFERENCES Student(Id),
    CONSTRAINT UQ_FeeAccount_Student_Year UNIQUE (SchoolId, StudentId, AcademicYear),
    CONSTRAINT CK_FeeAccount_TotalFees CHECK (TotalFees >= 0)
);

UQ_FeeAccount_Student_Year is the constraint that prevents the bug this curriculum uses throughout — a student with two accounts for the same year, and a query that silently picks the wrong one.

CREATE PROCEDURE dbo.usp_GetFeeReceipt
    @SchoolId  INT,
    @PaymentId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        p.ReceiptNumber,
        s.Name              AS StudentName,
        s.RollNumber,
        s.ClassName,
        s.Section,
        p.Amount            AS AmountPaid,
        p.PaidOn,
        p.PaymentMode,
        (a.TotalFees - a.DiscountAmount - a.PaidAmount) AS BalanceAfterPayment
    FROM FeePayment p
    INNER JOIN FeeAccount a ON a.Id = p.FeeAccountId
    INNER JOIN Student    s ON s.Id = a.StudentId
    WHERE p.SchoolId = @SchoolId
      AND p.Id = @PaymentId
      AND s.IsDeleted = 0;
END

@SchoolId is a parameter the application supplies from the signed-in user's token — never from a request field. Stage 5 shows where that value comes from.

Every join here is INNER deliberately. A payment with no account or no student is corrupt data, not a case to display blankly. Where a student legitimately might have no fee account — a defaulter report listing everyone — LEFT JOIN is correct, and the row count tells you which you have.

Where to learn it

Topic	Read
Relational concepts, schemas	Track 06 — Relational foundations
Tables and data types	Track 06 — Tables and data types
Keys and constraints	Track 06 — Keys and constraints
SELECT, INSERT, UPDATE, DELETE, filtering	Track 06 — CRUD and filtering
Aggregate, string and date functions	Track 06 — Functions
Joins	Track 06 — Joins
GROUP BY, HAVING, subqueries	Track 06 — Grouping and subqueries
Views and stored procedures	Track 06 — Views and stored procedures
Transactions and indexes	Track 06 — Transactions and indexes
The database capstone	Track 06 — Database project

Tools: Track 15 — SQL Server and SSMS.

Stage exercises

From the guided path syllabus:

Create a normalised database module. Build the Student, FeeAccount and FeePayment tables with primary keys, foreign keys, composite unique constraints and CHECK constraints. Seed 20 students, including one repeating a year with two fee accounts.

Build multi-table reports. Write the receipt query above, and a fee defaulter report joining Student and FeeAccount where the balance is above zero and the due date has passed.

Write CRUD stored procedures for FeePayment, each taking @SchoolId as its first parameter.

Debugging drills

Fix a constraint violation. Insert a duplicate (SchoolId, RollNumber) and read the error. Then create the constraint on RollNumber alone and watch a second school's legitimate insert fail.

Investigate duplicate rows from a join. Give one student two fee accounts, join Student to FeeAccount, and compare SELECT COUNT(*) before and after the join. Then SUM a fee column over the joined result and see the total inflate.

Correct a procedure parameter mismatch. Call usp_GetFeeReceipt with the parameters in the wrong order and read what comes back.

Prove the tenant bug. Remove WHERE p.SchoolId = @SchoolId and confirm that School 1's user can retrieve School 2's receipt — with no error, and a perfectly normal result set.

Practice

1. Work through Track 06's ten articles.
2. Build the full 12-table School schema with keys and constraints.
3. Seed data including a student with two fee accounts and an exam with absent students.
4. Write the receipt query and run it in SSMS.
5. Remove the SchoolId filter and confirm the cross-school leak.
6. Compare row counts before and after an inner join, and after a join that multiplies rows.
7. Store an amount as FLOAT, sum a thousand payments, and compare with DECIMAL(18,2).
8. Store an absent student's marks as 0, compute the class average, then change to NULL and compute again.
9. Create UNIQUE (RollNumber) and try inserting the same roll number for a second school.
10. Write CRUD stored procedures for FeePayment with @SchoolId first.
11. Wrap an insert and an update in a transaction, then force the second to fail and confirm the rollback.
12. Run a search with SET STATISTICS IO ON, add an index on (SchoolId, RollNumber), and compare logical reads.

Exercises 5, 8 and 9 produce no error message. Those are the ones to have seen.

You can now

• Design a normalised multi-tenant schema with real constraints
• Write joins, grouping and subqueries
• Create views and parameterised stored procedures
• Use transactions and basic indexes
• Say why uniqueness must be composite in a multi-tenant system

Review questions

1. Why must uniqueness be (SchoolId, RollNumber) rather than RollNumber alone?
2. What does an inner join do to a report that must list every student?
3. Why is DECIMAL(18,2) required for money?
4. What happens when SchoolId is missing from a WHERE clause?

Next: ADO.NET and Dapper