dev.rean.me
database

Student Management Database Example In SQL Server with Enrollment and Payment (video inside)

In this video, we create simple student management database step-by-step from scratch, including students table, courses table, enrollments, and payment tracking. Includes SQL queries for enrollment and payment operations.

2026-08-24 · 8 min read

#student#database#sql#enrollment#payment#course#academic year#department#sql-server#mssql
Share:

Hello friend! Today we create simple student management database step by step from scratch using Microsoft SQL Server (MS SQL Server). This database help us keep and manage student info, department, academic year, enrollment, and payment history.

All SQL statement here write specifically for MS SQL Server. Some syntax is different from MySQL:

  • We use IDENTITY(1,1) instead of AUTO_INCREMENT
  • SQL Server not support ENUM, so we use CHECK constraints
  • We use GO to execute batch command

The same example for MySQL Database: Student Management Database Example with Enrollment and Payment in MySQL

student management database schema example

Tables in this database: tbStudents, tbAcademic_years, tbDepartments, tbEnrollments, tbPayments.

💡 Tip: Make sure SQL Server Management Studio (SSMS) or Azure Data Studio is connected before running query!

Step 1: Create Database

First step, we create database for hold all our tables. We call it StudentManagementDB.

CREATE DATABASE StudentManagementDB;
GO

USE StudentManagementDB;
GO

💡 Tip: GO keyword in SQL Server tell SSMS to send and execute current batch of SQL statements to server!

Step 2: Create Student Table (tbStudents)

Now, we create table to keep student basic information like name, gender, birthday, and province. We call it tbStudents.

CREATE TABLE tbStudents (
    student_id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(100) NULL,
    last_name  VARCHAR(100) NULL,
    gender     CHAR(1) NULL, -- M, F
    birth_date DATE NULL,
    province   VARCHAR(100) NULL
);

Now let insert sample student data:

INSERT INTO tbStudents (student_id, first_name, last_name, gender, birth_date, province)
VALUES (1, 'sok', 'sao', 'M', '2000-01-23', 'Kampot');

INSERT INTO tbStudents (student_id, first_name, last_name, gender, birth_date, province)
VALUES (2, 'sokheng', 'Sou', 'M', '2002-04-13', 'Takeo');

INSERT INTO tbStudents (student_id, first_name, last_name, gender, birth_date, province) VALUES
(3, 'Vanna', 'Sokha', 'M', '2003-01-10', 'Takeo'),
(4, 'Srey', 'Pich', 'F', '2002-11-05', 'Kampong Cham'),
(5, 'Rithy', 'Chantha', 'M', '2000-06-30', 'Battambang'),
(6, 'Sokun', 'Sophea', 'F', '2001-09-12', 'Siem Reap'),
(7, 'Dara', 'Vichea', 'M', '2002-04-18', 'Prey Veng'),
(8, 'Sreynich', 'Ly', 'F', '2003-02-25', 'Svay Rieng'),
(9, 'Borey', 'Kim', 'M', '2001-12-01', 'Kampot'),
(10, 'Sreyneang', 'Huot', 'F', '2002-08-09', 'Kep'),
(11, 'Piseth', 'Meng', 'M', '2000-05-20', 'Kampong Speu'),
(12, 'Sokunthea', 'Chea', 'F', '2001-10-14', 'Kampong Thom'),
(13, 'Narin', 'Phan', 'M', '2002-07-07', 'Pursat'),
(14, 'Sreymao', 'Tan', 'F', '2003-03-11', 'Koh Kong'),
(15, 'Visal', 'Heng', 'M', '2001-01-29', 'Banteay Meanchey'),
(16, 'Sopheap', 'Oun', 'F', '2002-06-16', 'Oddar Meanchey'),
(17, 'Kosal', 'Lim', 'M', '2000-09-03', 'Pailin'),
(18, 'Sreyka', 'Sin', 'F', '2003-05-22', 'Ratanakiri'),
(19, 'Chenda', 'Long', 'M', '2002-12-19', 'Mondulkiri'),
(20, 'Sreylen', 'Keo', 'F', '2001-04-27', 'Kratie'),
(21, 'Vichea', 'Touch', 'M', '2000-11-13', 'Stung Treng'),
(22, 'Sokha', 'Yim', 'F', '2002-02-08', 'Preah Vihear'),
(23, 'Ratha', 'Nhem', 'M', '2001-06-17', 'Kampong Chhnang'),
(24, 'Sreypov', 'Chun', 'F', '2003-09-01', 'Tbong Khmum'),
(25, 'Bunna', 'Sam', 'M', '2002-10-10', 'Takeo'),
(26, 'Sreychan', 'Chhim', 'F', '2001-03-23', 'Kandal'),
(27, 'Dalin', 'Sok', 'F', '2002-07-30', 'Phnom Penh'),
(28, 'Sothea', 'Mean', 'M', '2000-08-05', 'Battambang'),
(29, 'Sreyroth', 'Prak', 'F', '2003-01-14', 'Siem Reap'),
(30, 'Phalla', 'Nuon', 'M', '2001-12-28', 'Kampot'),
(31, 'Sok', 'Dara', 'M', '2002-03-15', 'Phnom Penh'),
(32, 'Chan', 'Sreymom', 'F', '2001-07-21', 'Kandal');

💡 Tip: In tbStudents, we manually specify student_id without auto increment. Make sure ID not duplicate!

Step 3: Create Academic Year Table (tbAcademic_years)

Now let create academic year table with auto increment ID that hold year information like 2025-2026, 2026-2027.

CREATE TABLE tbAcademic_years (
    academic_id TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    start_year  CHAR(4) NOT NULL, -- 2026
    end_year    CHAR(4) NOT NULL  -- 2027
);

Insert sample academic year data:

INSERT INTO tbAcademic_years (start_year, end_year)
VALUES ('2025','2026'), ('2026','2027'), ('2027','2028'), ('2028','2029'), ('2029','2030');

💡 Tip: In MS SQL Server, IDENTITY(1,1) starts at 1 and automatically increases by 1 for every new row!

Step 4: Create Department Table (tbDepartments)

What department student study? Computer Science? Khmer Literature? Math? We create tbDepartments table.

CREATE TABLE tbDepartments (
    department_id SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    is_active  BIT NOT NULL DEFAULT (1),
    created_at DATETIME DEFAULT (SYSUTCDATETIME())
);

Let insert sample department data:

INSERT INTO tbDepartments (name)
VALUES ('Computer Science'), ('Khmer Literature'), ('Enligh Language'), ('Mathematics'), ('Physics'), ('Chemistry');

💡 Tip: SQL Server does not support ON UPDATE CURRENT_TIMESTAMP. We use DATETIME DEFAULT (SYSUTCDATETIME()) for created time!

Step 5: Create Enrollment Table (tbEnrollments)

Now we create enrollment table to record which student study in which department and academic year. We link tables together using Foreign Keys.

CREATE TABLE tbEnrollments (
    enroll_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    student_id INT NOT NULL,        -- FK
    academic_id TINYINT NOT NULL,   -- FK
    department_id SMALLINT NOT NULL,-- FK
    study_year TINYINT NOT NULL,
    is_active BIT NOT NULL DEFAULT (1),
    created_at DATETIME DEFAULT (SYSUTCDATETIME()),
     
    FOREIGN KEY (student_id) REFERENCES tbStudents (student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (academic_id) REFERENCES tbAcademic_years (academic_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (department_id) REFERENCES tbDepartments (department_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Let insert sample enrollment data:

INSERT INTO tbEnrollments (student_id, academic_id, department_id, study_year)
VALUES (1,1,1,1), (2,1,1,2), (3,1,2,1), (4,2,1,1), (5,1,1,2), (6,1,1,1), (7,1,1,2), (8,1,2,2), (9,1,2,2);

INSERT INTO tbEnrollments (student_id, academic_id, department_id, study_year)
VALUES (1,1,3,1), (2,1,2,2), (3,1,1,1);

💡 Tip: One student can enroll in multiple departments and academic years!

Step 6: Create Payment Table (tbPayments)

After student enrolled in department, they must pay money for tuition fee, transcript, or exam. We make tbPayments table for manage all payment history.

CREATE TABLE tbPayments (
    payment_id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    enroll_id INT NOT NULL,   -- FK
    student_id INT NOT NULL,  -- FK

    semester VARCHAR(10) CHECK (semester IS NULL OR semester IN ('first','second','full')),
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    payment_methods VARCHAR(10) NOT NULL CHECK (payment_methods IN ('cash','bank','KHQR','Other')),
    payment_types   VARCHAR(20) NOT NULL CHECK (payment_types IN ('tuition','exam','transcript','other')),
    status          VARCHAR(10) NOT NULL CHECK (status IN ('paid','pending','refund')) DEFAULT ('pending'),
    remark VARCHAR(255) NULL,
    is_deletd BIT NOT NULL DEFAULT (0),
    created_at DATETIME DEFAULT (SYSUTCDATETIME()),
    
    FOREIGN KEY (student_id) REFERENCES tbStudents (student_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (enroll_id) REFERENCES tbEnrollments (enroll_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Let insert sample payment data:

INSERT INTO tbPayments
(enroll_id, student_id, semester, amount, payment_methods, payment_types, status, remark)
VALUES
(1, 1, 'first', 250.00, 'cash', 'tuition', 'paid', 'First semester tuition fee'),
(2, 2, NULL,   10.00, 'cash', 'transcript', 'paid', NULL);

INSERT INTO tbPayments (enroll_id, student_id, semester, amount, payment_methods, payment_types, status, remark) VALUES
(1, 1, 'first', 250.00, 'bank', 'tuition', 'paid', 'First semester tuition fee'),
(2, 2, 'first', 250.00, 'KHQR', 'tuition', 'paid', 'First semester tuition fee'),
(3, 3, 'first', 250.00, 'cash', 'tuition', 'pending', 'Awaiting payment'),
(4, 4, 'first', 250.00, 'bank', 'tuition', 'paid', 'Full payment'),
(5, 5, 'first', 250.00, 'KHQR', 'tuition', 'paid', 'Full payment'),
(1, 1, NULL,    15.00, 'cash', 'transcript', 'paid', 'Official transcript request'),
(2, 2, NULL,    20.00, 'cash', 'other', 'paid', 'ID card replacement fee'),
(6, 6, 'first', 250.00, 'bank', 'tuition', 'paid', 'Full payment'),
(7, 7, 'first', 100.00, 'cash', 'tuition', 'pending', 'Partial payment'),
(8, 8, NULL,     5.00, 'cash', 'exam', 'paid', 'Resit exam fee'),
(3, 3, 'first', 500.00, 'bank', 'tuition', 'paid', 'First semester tuition fee'),
(4, 4, 'first', 250.00, 'KHQR', 'tuition', 'pending', 'First installment'),
(5, 5, 'first', 500.00, 'bank', 'tuition', 'paid', 'Full payment for first semester'),
(6, 6, 'first', 500.00, 'cash', 'tuition', 'paid', 'Full payment'),
(7, 7, 'second',500.00, 'KHQR', 'tuition', 'paid', 'Second semester tuition fee'),
(8, 8, NULL,    25.00, 'cash', 'exam', 'paid', 'Resit exam fee'),
(9, 9, 'first', 500.00, 'bank', 'tuition', 'paid', 'Full payment'),
(10, 1,'second',500.00, 'KHQR', 'tuition', 'pending', 'Awaiting payment for second semester'),
(11, 2, NULL,    15.00, 'cash', 'other', 'paid', 'Library fine'),
(12, 3, NULL,    10.00, 'cash', 'transcript', 'paid', 'Transcript request');

💡 Tip: Because MS SQL Server does not have ENUM type, we use CHECK (column IN (...)) to limit allowed values!

10 SQL Practice Questions for Testing

Now database and sample data are ready! Let try to write SQL Server queries to answer these 10 practice questions:

  1. Show student name and study year (study_year) from tbEnrollments table.
  2. Show student name who study in department ID 2 (department_id = 2).
  3. Show student name and payment info for students with payment status 'pending'.
  4. Show student name and study year for students studying year 1 in academic year ID 1 (academic_id = 1).
  5. Show student name and payment details for payments made via 'cash'.
  6. Show student name and payment type (payment_types) for all students.
  7. Show student list who registered in any academic year (using tbEnrollments).
  8. Show student info where first_name starts with "Srey" and lives in "Takeo" or "Kandal".
  9. Show student list who live in "Takeo", "Kandal", or "Phnom Penh".
  10. Show all payment info where status is NOT 'paid' or 'pending' (only 'refund').

💡 Tip: Practice INNER JOIN between tbStudents, tbEnrollments, and tbPayments to select combined data easily!

← Back to database
Share: