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

byHann -
0

How Make Student Management Database with MySQL Easy + 30 SQL Query Questions and Answers


Table of Contents

Hello friend. Today we create database for student management using MySQL. All sql statements specificially for MySQL database, it may little difference if you are using SQL Server Database (I will have another post for SQL Server). Now let me show you step by step from the beginning.


Check out the same example for SQL Server HERE.

Step 1: Create Database

Firstly, We create a database for holding our tables called StudentManagementDB 


CREATE DATABASE StudentManagementDB;

USE StudentManagementDB;

Step 2: Create Student Table

Now, we make table for keep student information. Like name, gender, birthday. We call tbStudents. 

CREATE TABLE tbStudents(
        student_id INT PRIMARY KEY,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        gender CHAR(1),
        birth_date DATE,
        province VARCHAR(100)
    );
Create Students table And Insert Data

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

    INSERT INTO tbStudents 
    VALUES(2,'sokheng','Sou','M','2002-4-13','Takeo');

    INSERT INTO tbStudents 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');

Boost your HTML skills: 🚀 HTML Tutorials in Khmer (with Video)


Step 3: Create Academic Year Table

Now we create academic year like 2025 to 2026, 2026 to 2027.

CREATE TABLE tbAcademic_years(
	academic_id TINYINT AUTO_INCREMENT PRIMARY KEY,
	start_year VARCHAR(4), -- 2026
	end_year VARCHAR(4), -- 2027
);
Create Academic Year table And Insert Data

Let insert some data:
INSERT INTO tbAcademic_years (start_year,end_year)
VALUES (2025, 2026),(2026, 2027),(2027, 2028),(2028, 2029);

Step 4: Create Department Table

What departmet student study? Computer Science? Math? We make tbDepartments table. 

CREATE TABLE tbDepartments(
        department_id SMALLINT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Create Department table And Insert Data

Let insert sample data:

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

Step 5: Create Enrollment Table

Now we create enrollment table which record students, department and academic year with relationship to student, department and academic year table. 

CREATE TABLE tbEnrollments(
        enroll_id INT AUTO_INCREMENT 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 TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        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
);
Create Enrollment table And Insert Data

Now 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 AUTO_INCREMENT PRIMARY KEY,
        enroll_id INT NOT NULL, -- FK
        student_id INT NOT NULL, -- FK
        semester ENUM('first', 'second', 'full'),
        amount DECIMAL(10,2) CHECK (amount >0) NOT NULL,
        payment_methods ENUM('cash', 'bank', 'KHQR','Other') NOT NULL,
        payment_types ENUM('tuition','exam','transcript','other') NOT NULL,
        status ENUM('paid','pending','refund') DEFAULT 'pending',
        remark VARCHAR(255),
        is_deletd TINYINT(1) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        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
    );
Create Payment table And Insert Data

Now insert some 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, '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 for SQL Query for Testing

Now we have database and tables with sample data. Let try to write some SQL query to test your skill.

10 Basic SQL Questions and Answers for Student Management Database

In this video, you will learn 10 basic SQL questions and answers using a Student Management Database, helping you understand essential SQL concepts through simple, real-world examples.


Another 10 SQL Questions and Answers for Student Management Database

In this video, you will learn another 10 more advance SQL questions and answers using a Student Management Database, helping you understand essential SQL concepts through simple, real-world examples.


Another 5 Advance SQL Questions and Answers for Student Management Database

In this video, we cover more advance sql query include join, join multiple tables, and subquery.


Complex 5 Advance SQL Questions and Answers for Student Management Database

In this video, we cover more advance and complex sql query include join, join multiple tables, and subquery.


🎓 Common Questions & Answers

1. Can I run this exact code in SQL Server or Oracle?

No, this specific code is for MySQL. Functions like AUTO_INCREMENT, ENUM, and ON UPDATE CURRENT_TIMESTAMP are unique to MySQL. If you use SQL Server, check out the SQL Server version link at the top of this post.

2. Why did we name the tables with "tb" (like tbStudents)?

Using "tb_" is a naming convention standard that stands for "table". It helps developers quickly identify that this object in the database is a table, especially when your database grows large with Views (vw) or Stored Procedures (sp).

3. What does "ON DELETE CASCADE" mean in the Foreign Key?

It means if you delete a parent record, all child records link to it will be deleted automatically. For example, if you delete a Student from tbStudents, all their Enrollments and Payments will be deleted too so you don't have broken records.

4. Why create tbEnrollments? Why not put department inside tbStudents?

Because a student might enroll in different departments across different academic years (e.g., they change majors, or take a double degree). Adding the tbEnrollments "bridge" table allows for a Many-to-Many relationship.

5. Where do I paste this SQL code to practice?

You can run this code using free tools like phpMyAdmin ( included in XAMPP/WAMP), MySQL Workbench, or DBeaver. Just open the SQL tab, paste the code step-by-step, and click "Run" or "Go".

Tags

You may like these posts

Previous Post Next Post