SQL-Server: Student Management Database Example

How to Make Student Database with SQL Server (Easy)

Hello friend. Today we create a database for student management using Microsoft SQL Server. The SQL statements below are written for SQL Server Database. Some syntax is different from MySQL such as IDENTITY instead of AUTO_INCREMENT, and SQL Server does not support ENUM so we use CHECK constraints instead.

Check out the same example for MySQL HERE.

Tables: tbStudents, tbAcademic_years, tbDepartments, tbEnrollments, tbPayments.
Relationship: Students 1-M Enrollments, Departments 1-M Enrollments, AcademicYears 1-M Enrollments, Enrollments 1-M Payments.

Step 1: Create Database

Firstly, we create a database for holding our tables called StudentManagementDB.

CREATE DATABASE StudentManagementDB;
GO

USE StudentManagementDB;
GO

Step 2: Create Student Table

Now, we create a table to keep student information like name, gender, birthday. 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 insert some 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');

Step 3: Create Academic Year Table

Now let create academic year table with auto increment ID that hold information like 2025 to 2026, 2026 to 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 some sample data:

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

Step 4: Create Department Table

What department student study? Computer Science? Khmer? or Other, We create tbDepartments table as below.

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())
);

Note: SQL Server does not support ON UPDATE CURRENT_TIMESTAMP. If you want automatic update of updated_at, use a trigger (optional).

Now let insert sample data:

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

Step 5: Create Enrollment Table

Now let create enrollment table which records students, department and academic year with relationship to student, department and academic year table. Note that student can enroll in multiple departments and academic years, so we need to use foreign keys to link them together.

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 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);

Step 6: Create Payment Table

After student enrolled in any department, then they need to pay for it. It can be pay for semester or full year of tuition fee, certificate or other.

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 some sample 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);

Let insert more sample data:

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');

Some Questions SQL Query for Testing

១. បង្ហាញឈ្មោះសិស្ស និងឆ្នាំសិក្សា (study_year) របស់ពួកគេតាមរយៈតារាង enrollments
២. បង្ហាញឈ្មោះសិស្សដែលរៀនក្នុងដេប៉ាតឺម៉ងលេខ ២ (department_id = 2)
៣. បង្ហាញឈ្មោះសិស្ស និងព័ត៌មានបង់ប្រាក់សម្រាប់សិស្សដែលមានស្ថានភាពបង់ប្រាក់ជា 'pending'
៤. បង្ហាញឈ្មោះសិស្ស និងឆ្នាំសិក្សា (Study Year) សម្រាប់សិស្សដែលរៀនឆ្នាំទី ១ ក្នុងកូដឆ្នាំសិក្សាទី ១ (academic_id = 1)
៥. បង្ហាញឈ្មោះសិស្ស និងព័ត៌មានបង់ប្រាក់សម្រាប់អ្នកដែលបង់តាមរយៈ 'cash'
៦. បង្ហាញឈ្មោះសិស្ស និងប្រភេទនៃការបង់ប្រាក់ (pay_types) សម្រាប់សិស្សទាំងអស់
៧. បង្ហាញឈ្មោះសិស្សដែលបានចុះឈ្មោះក្នុងឆ្នាំសិក្សាណាមួយ (ប្រើ enrollments)
៨. បង្ហាញព័ត៌មានសិស្សដែលមានឈ្មោះខ្លួន (first_name) ចាប់ផ្តើមដោយពាក្យ "Srey" ហើយរស់នៅក្នុងខេត្ត "Takeo" ឬ "Kandal"
៩. បង្ហាញបញ្ជីសិស្សដែលរស់នៅក្នុងខេត្ត "Takeo", "Kandal" ឬ "Phnom Penh"
១០. បង្ហាញព័ត៌មានបង់ប្រាក់ទាំងអស់ដែល មិនមាន ស្ថានភាពជា 'paid' ឬ 'pending' (មានន័យថាបង្ហាញតែ 'refund')

SQL-Server: Student Management Database Example

How to Make Student Database with SQL Server (Easy)

Step 1: Create Database

Step 2: Create Student Table

Step 3: Create Academic Year Table

Step 4: Create Department Table

Step 5: Create Enrollment Table

Step 6: Create Payment Table

Some Questions SQL Query for Testing

MySQL Part1: How to Get Start MySQL in Khmer

Latest Posts

Popular Posts

MySQL Part1: How to Get Start MySQL in Khmer

Student Management Database Example with Enrollment and Payment (video in side)

XAMPP Download Install and Configure for PHP or Laravel in Khmer

🔷វិបសាយរៀនបន្ថែម🔷