Attendance, Exams and Marks Tables – SQL Server | Lesson 13
Introduction
Attendance and marks are the data teachers touch every day. They are also the fastest-growing tables in the school.
In this lesson, you create four tables: AttendanceRecords, Exams, ExamSubjects
(the papers of an exam), and Marks.
By the end of this lesson, you will be able to:
- Create tables that grow to millions of rows, with
BIGINTkeys. - Stop duplicate attendance for the same student and day.
- Model an exam, its papers, and the marks for each paper.
- Write a
CHECKthat compares two columns of the same row. - Explain which rules a
CHECKconstraint cannot enforce.
Why this matters
A teacher taps "Save" twice on a slow connection. Without a rule, Kabir is marked absent twice for the same day, and his attendance percentage is wrong for the rest of the term.
Marks need care too. An absent student must not look like a student who scored zero. A report card that shows "0" for Meera, who was ill on exam day, is a real complaint from a real parent.
The concept
Example: the attendance table
AttendanceRecords holds one row per enrolled student per school day. It links to the
enrollment, not to the student, so every record already knows the year and section.
Two design choices:
- The key is
BIGINT. One big school writes about 2 million rows a year; many schools together pass theINTlimit (Lesson 8). UNIQUE (EnrollmentId, AttendanceDate)means one record per student per day. A second save for the same day fails instead of creating a duplicate.
Status allows four values, and each one counts differently in reports:
| Status | Meaning | Counts as present? |
|---|---|---|
| Present | In class on time | Yes |
| Late | Arrived after the bell | Yes, but reported separately |
| Absent | Not in school, no permission | No |
| Leave | Not in school, with permission | No, but excused |
Defining these meanings now saves arguments later, when the principal asks why one report shows 92% attendance and another 95%.
Example: exams and exam papers
An exam is an event, such as "Unit Test 1" or "Half Yearly". Each exam has many
papers: one per class and subject, each with its own date, maximum marks, and pass
marks. That is the ExamSubjects table.
Think of a school sports day. The sports day is the exam, and each race (under-10 sprint, under-14 relay) is a paper. Each runner's time is a mark.
Example: the exams table
Exams belongs to one academic year. UNIQUE (AcademicYearId, Name) allows "Unit
Test 1" every year, but only once per year. ExamType is one of three values:
| ExamType | SMS example | Typical use |
|---|---|---|
| UnitTest | Unit Test 1 | Short tests during a term |
| Term | Half Yearly | The mid-year exam |
| Annual | Annual Exam | The final exam that decides promotion |
Example: a report card as rows
Aarav's Unit Test 1 report card line for Mathematics comes from three tables:
| Report card shows | Comes from |
|---|---|
| "Unit Test 1, 2026-27" | Exams row 1 |
| "Mathematics, out of 100, pass 35" | ExamSubjects row 8 (Class 10, Mathematics) |
| "92" | Marks row for paper 8 and Aarav's enrollment 13 |
Nothing is repeated. Change the paper's maximum once, and every student's report card uses the new value.
Whether Aarav passed is not stored anywhere. It is calculated by comparing 92 with the paper's pass marks, 35. Storing a "Passed" column would be a copy that can disagree with the marks, exactly the problem Lesson 7 warned about.
Example: the marks table
Marks holds one row per paper per enrollment. UNIQUE (ExamSubjectId, EnrollmentId) stops a student getting two marks for the same paper.
Example: absent is not zero
When a student is absent, MarksObtained is NULL and IsAbsent is 1. A CHECK
constraint can test several columns of the same row together:
CONSTRAINT CK_Marks_AbsentOrMarks CHECK (
(IsAbsent = 1 AND MarksObtained IS NULL)
OR (IsAbsent = 0 AND MarksObtained >= 0)
)
Read it as: either absent with no marks, or present with marks of zero or more. "Absent with 45 marks" and "present with no marks" are both refused.
Example: a rule a CHECK cannot enforce
"Marks cannot be more than the paper's maximum" needs MaxMarks, which lives in
another table, ExamSubjects. A CHECK constraint can only see the row being
saved, so it cannot enforce this rule.
| Rule | Where it can live |
|---|---|
| Absent means no marks | CHECK on Marks (same row) |
| Pass marks not above maximum | CHECK on ExamSubjects (same row) |
| Marks not above the paper's maximum | A trigger (Lesson 23) or the API (Module 11) |
A trigger is code that runs automatically when rows change. You will write this exact trigger in Lesson 23.
Apply it to the SMS
Save this as database\013-attendance-exams.sql:
Example: the attendance and exams script
-- Lesson 13: attendance, exams and marks: AttendanceRecords, Exams, ExamSubjects, Marks.
-- Safe to run more than once.
USE SchoolDb;
GO
-- One row per enrolled student per school day. BIGINT: this table grows fastest.
IF OBJECT_ID(N'dbo.AttendanceRecords', N'U') IS NULL
BEGIN
CREATE TABLE dbo.AttendanceRecords
(
Id BIGINT IDENTITY(1, 1) NOT NULL,
EnrollmentId INT NOT NULL,
AttendanceDate DATE NOT NULL,
Status VARCHAR(7) NOT NULL,
Remarks NVARCHAR(200) NULL,
CONSTRAINT PK_AttendanceRecords PRIMARY KEY (Id),
CONSTRAINT FK_AttendanceRecords_Enrollments FOREIGN KEY (EnrollmentId) REFERENCES dbo.Enrollments (Id),
CONSTRAINT UQ_AttendanceRecords_EnrollmentId_AttendanceDate UNIQUE (EnrollmentId, AttendanceDate),
CONSTRAINT CK_AttendanceRecords_Status CHECK (Status IN ('Present', 'Absent', 'Late', 'Leave'))
);
END;
GO
IF OBJECT_ID(N'dbo.Exams', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Exams
(
Id INT IDENTITY(1, 1) NOT NULL,
AcademicYearId INT NOT NULL,
Name NVARCHAR(50) NOT NULL, -- Unit Test 1, Half Yearly, ...
ExamType VARCHAR(10) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
CONSTRAINT PK_Exams PRIMARY KEY (Id),
CONSTRAINT FK_Exams_AcademicYears FOREIGN KEY (AcademicYearId) REFERENCES dbo.AcademicYears (Id),
CONSTRAINT UQ_Exams_AcademicYearId_Name UNIQUE (AcademicYearId, Name),
CONSTRAINT CK_Exams_ExamType CHECK (ExamType IN ('UnitTest', 'Term', 'Annual')),
CONSTRAINT CK_Exams_Dates CHECK (EndDate >= StartDate)
);
END;
GO
-- One paper of an exam: one subject for one class.
IF OBJECT_ID(N'dbo.ExamSubjects', N'U') IS NULL
BEGIN
CREATE TABLE dbo.ExamSubjects
(
Id INT IDENTITY(1, 1) NOT NULL,
ExamId INT NOT NULL,
ClassId INT NOT NULL,
SubjectId INT NOT NULL,
ExamDate DATE NOT NULL,
MaxMarks DECIMAL(5, 2) NOT NULL,
PassMarks DECIMAL(5, 2) NOT NULL,
CONSTRAINT PK_ExamSubjects PRIMARY KEY (Id),
CONSTRAINT FK_ExamSubjects_Exams FOREIGN KEY (ExamId) REFERENCES dbo.Exams (Id),
CONSTRAINT FK_ExamSubjects_Classes FOREIGN KEY (ClassId) REFERENCES dbo.Classes (Id),
CONSTRAINT FK_ExamSubjects_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (Id),
CONSTRAINT UQ_ExamSubjects_ExamId_ClassId_SubjectId UNIQUE (ExamId, ClassId, SubjectId),
CONSTRAINT CK_ExamSubjects_MaxMarks CHECK (MaxMarks > 0),
CONSTRAINT CK_ExamSubjects_PassMarks CHECK (PassMarks <= MaxMarks)
);
END;
GO
IF OBJECT_ID(N'dbo.Marks', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Marks
(
Id BIGINT IDENTITY(1, 1) NOT NULL,
ExamSubjectId INT NOT NULL,
EnrollmentId INT NOT NULL,
MarksObtained DECIMAL(5, 2) NULL, -- NULL when the student was absent
IsAbsent BIT NOT NULL CONSTRAINT DF_Marks_IsAbsent DEFAULT (0),
Remarks NVARCHAR(200) NULL,
CONSTRAINT PK_Marks PRIMARY KEY (Id),
CONSTRAINT FK_Marks_ExamSubjects FOREIGN KEY (ExamSubjectId) REFERENCES dbo.ExamSubjects (Id),
CONSTRAINT FK_Marks_Enrollments FOREIGN KEY (EnrollmentId) REFERENCES dbo.Enrollments (Id),
CONSTRAINT UQ_Marks_ExamSubjectId_EnrollmentId UNIQUE (ExamSubjectId, EnrollmentId),
CONSTRAINT CK_Marks_AbsentOrMarks CHECK (
(IsAbsent = 1 AND MarksObtained IS NULL)
OR (IsAbsent = 0 AND MarksObtained >= 0)
)
);
END;
GO
-- Check: list the tables in SchoolDb.
SELECT name AS TableName
FROM sys.tables
ORDER BY name;
GO
Press F5. The list now shows 15 tables.
Example: how the four tables connect
| Question | Answered by |
|---|---|
| Was Kabir present on 1 July? | AttendanceRecords row for Kabir's 2026-27 enrollment and that date |
| Which papers does Class 10 write in Unit Test 1? | ExamSubjects rows for that exam and class |
| What did Aarav score in Unit Test 1 Mathematics? | Marks row for that paper and Aarav's enrollment |
| Did he pass? | Compare his mark with that paper's PassMarks |
You will write these exact queries in Module 4.
Common mistakes
- Linking attendance to the student instead of the enrollment. You then cannot
tell which section or year it belongs to. Fix: link to
Enrollments. - Storing absent as 0 marks. Averages and reports become wrong. Fix:
NULLmarks plusIsAbsent = 1. - No unique rule on attendance. Double clicks create duplicate days. Fix:
UNIQUE (EnrollmentId, AttendanceDate). - Expecting a
CHECKto look at another table. It cannot. Fix: use a trigger or the application for cross-table rules. "A paper's date falls inside its exam's dates" is the same kind of rule: the dates live in two tables. INTkeys on fast-growing tables. Fix: useBIGINTwhere rows can pass 2 billion.
In real projects
In school systems I have worked on, attendance is the table that decides performance. Every class teacher saves at the same time, a few minutes after the first bell. The unique constraint on student and date was the hero there: when the network retried a save, the database refused the duplicate instead of doubling the data.
The absent-versus-zero rule came from a real escalation. A parent saw "0" on a report card for an exam their child missed because of hospital admission. Since then, I never let "no value" and "zero" share the same meaning in any table.
Best practices
- Link daily records to the enrollment, which carries year and section.
- Add a unique rule for "one record per thing per day" on every daily table.
- Use
NULLplus a flag for "did not happen", never a fake zero. - Write same-row rules as
CHECKconstraints; plan cross-table rules explicitly. - Choose
BIGINTkeys for tables that grow every day.
Recap
AttendanceRecords: one row per enrollment per day, with aBIGINTkey.Examsare events;ExamSubjectsare their papers, each with maximum and pass marks.Marks: one row per paper per enrollment.- A
CHECKcan compare columns of the same row, such asIsAbsentandMarksObtained. - Rules that need another table need a trigger or application code.
- Results such as "passed" are calculated from marks, never stored.
Homework
Library module task: The school allows a book to be borrowed for at most 30
days. Add a CHECK to BookIssues that refuses a due date more than 30 days after
the issue date. Use DATEADD(DAY, 30, IssuedOn), which adds 30 days to a date.
A correct result looks like: the script runs twice without errors, and the
constraint CK_BookIssues_MaxLoan exists.
Show the solution
-- Library homework, Lesson 13: a book can be borrowed for at most 30 days.
USE SchoolDb;
GO
IF OBJECT_ID(N'dbo.CK_BookIssues_MaxLoan', N'C') IS NULL
ALTER TABLE dbo.BookIssues
ADD CONSTRAINT CK_BookIssues_MaxLoan CHECK (DueOn <= DATEADD(DAY, 30, IssuedOn));
GO
Both dates are in the same row, so a CHECK works here.
Quiz
Why does AttendanceRecords link to Enrollments instead of Students?
Show answer
Answer: B. The enrollment already knows the year and section Each attendance record then belongs to one student in one section in one year.
What stops a student being marked twice on the same day?
Show answer
Answer: B. UNIQUE (EnrollmentId, AttendanceDate) The pair of enrollment and date must be unique.
Meera was absent for the Science paper. How is that stored?
Show answer
Answer: B. MarksObtained = NULL and IsAbsent = 1 NULL means no marks; IsAbsent explains why.
What is an ExamSubjects row?
Show answer
Answer: B. One paper of an exam for one class and subject Each paper has its own date, maximum marks, and pass marks.
Why can't a CHECK constraint stop marks above the paper's maximum?
Show answer
Answer: B. The maximum is in another table, and CHECK sees only the current row Cross-table rules need a trigger (Lesson 23) or application code.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.
Fresher (0-2 yrs)
F Why is the key of AttendanceRecords a BIGINT? concept
Attendance grows by one row per student per school day. One school of 10,000 students
adds about 2 million rows a year, and many schools together pass the 2.1 billion limit
of INT. BIGINT costs four more bytes per row and removes that risk.
F How does the database prevent marking a student's attendance twice on the same day? concept
With UNIQUE (EnrollmentId, AttendanceDate) on AttendanceRecords. A second record for
the same enrollment and date is refused, so a double click or a network retry cannot
create a duplicate.
F Why store NULL marks for an absent student instead of 0? concept
Zero means "attended and scored nothing"; absent means "did not write the paper". Storing
0 would lower averages and show a misleading report card. MarksObtained is NULL and
IsAbsent is 1, so reports can treat absence correctly.
Mid (3-5 yrs)
M Explain the CHECK constraint that combines IsAbsent and MarksObtained. coding
CHECK ((IsAbsent = 1 AND MarksObtained IS NULL) OR (IsAbsent = 0 AND MarksObtained >= 0))
allows only two valid states: absent with no marks, or present with marks of zero or
more. It refuses "absent with 45 marks" and "present with no marks". A CHECK can compare
columns of the same row like this.
M Why is there an ExamSubjects table between Exams and Marks? design
An exam such as "Unit Test 1" has many papers: one per class and subject, each with its
own date, maximum marks, and pass marks. ExamSubjects stores those papers, and each mark
belongs to one paper. Without it, every mark would have to repeat the paper's details.
M Why can't a CHECK constraint enforce 'marks cannot exceed the paper's maximum'? concept
A CHECK constraint can only see the row being saved. The maximum lives in
ExamSubjects, another table. Cross-table rules need a trigger, a stored procedure, or
application code; the SMS adds a trigger in Lesson 23 and the same check in the API.
Senior (6-9 yrs)
S Every class teacher saves attendance between 8:45 and 9:00 AM. What design concerns does that create? scenario
Thousands of inserts arrive in a few minutes, so saves must be fast and safe to retry. Save a whole section in one set-based statement inside a transaction, rely on the unique constraint to stop duplicates, use an "insert or update" approach for corrections, and keep the table's indexes minimal so inserts stay cheap.
How to answer: Describe the load pattern first (short, intense peak). Then cover correctness (unique rule, idempotent saves), efficiency (one statement per section, not one per student), and the trade-off between extra indexes for reports and insert speed at peak time.
S A mark must be corrected after report cards were printed. How should the system handle it? scenario
Allow the correction only for authorized roles, record who changed it and when in the audit columns, and keep the old value in an audit log or history table. Reprint the affected report card and keep the reason for the change. A silent overwrite would make the printed and stored marks disagree with no explanation.
How to answer: Combine process and design: permissions, audit trail, and a reason field. Name the SQL Server options for keeping old values (an audit table written by a trigger, or a system-versioned temporal table) and the cost of each in storage and complexity.
Architect / Lead (10+ yrs)
A How would you store ten years of attendance for 1,000 schools? design
That is billions of rows, so plan for size from the start: BIGINT keys, partitioning by
date so old periods can be managed separately, archiving closed years to cheaper storage,
and summary tables for reports that need totals rather than single days. Daily screens
then only touch the current year's data.
How to answer: Estimate the volume with numbers first. Then explain each technique and what it solves: partitioning (maintenance and fast removal of old data), archiving (cost), summaries (report speed). Close with the trade-off: more moving parts to operate, justified only at that scale.
Resources
- Code for this lesson: git tag
lesson-13-attendance-exams-marks(code repository coming soon) - Files:
database/013-attendance-exams.sql,library-assignment/013-max-loan.sql - Video: coming soon
What's next
The academic tables are done. Next: fees, logins, and the columns that record who changed what. Lesson 14: Fees, users, audit columns and soft delete
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.