Rakin's Archive

DBMS Project Queries

9 min read

SQL Queries Used in SSMP (Scholarship and Stipend Management Portal)

Database and Schema Creation

Create Database

CREATE DATABASE IF NOT EXISTS ssmp;
USE ssmp;

Create Departments Table

CREATE TABLE IF NOT EXISTS departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    faculty VARCHAR(100) NOT NULL,
    budget FLOAT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT dept_budget_check CHECK (budget >= 0)
);

Create Admins Table

CREATE TABLE IF NOT EXISTS admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    dept_id INT NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    CONSTRAINT admin_email_check CHECK (email LIKE '%@bup.edu.bd')
);

Create Students Table

CREATE TABLE IF NOT EXISTS students (
    student_id BIGINT PRIMARY KEY,
    reg_no BIGINT UNIQUE NOT NULL,
    dept_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    session VARCHAR(20) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    CONSTRAINT student_email_check CHECK (email LIKE '%@%')
);

Create Academic Records Table

CREATE TABLE IF NOT EXISTS academic_records (
    reg_no BIGINT PRIMARY KEY,
    student_id BIGINT,
    cgpa FLOAT NOT NULL,
    semester_1_gpa FLOAT DEFAULT NULL,
    semester_2_gpa FLOAT DEFAULT NULL,
    semester_3_gpa FLOAT DEFAULT NULL,
    semester_4_gpa FLOAT DEFAULT NULL,
    semester_5_gpa FLOAT DEFAULT NULL,
    semester_6_gpa FLOAT DEFAULT NULL,
    semester_7_gpa FLOAT DEFAULT NULL,
    semester_8_gpa FLOAT DEFAULT NULL,
    current_semester INT NOT NULL DEFAULT 5,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT cgpa_check CHECK (cgpa >= 0 AND cgpa <= 4.0),
    CONSTRAINT current_semester_check CHECK (current_semester >= 1 AND current_semester <= 8)
);

Create Income Records Table

CREATE TABLE IF NOT EXISTS income_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    amount FLOAT NOT NULL,
    source VARCHAR(255) NOT NULL,
    family_member INT NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT income_amount_check CHECK (amount >= 0),
    CONSTRAINT family_member_check CHECK (family_member >= 0)
);

Create Applications Table

CREATE TABLE IF NOT EXISTS applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    type VARCHAR(255) NOT NULL,
    semester VARCHAR(50) NOT NULL,
    status VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Create Scholarships Table

CREATE TABLE IF NOT EXISTS scholarships (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    type VARCHAR(100) NOT NULL,
    amount FLOAT NOT NULL,
    semester VARCHAR(50) NOT NULL,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Create Stipends Table

CREATE TABLE IF NOT EXISTS stipends (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    type VARCHAR(100) NOT NULL,
    amount FLOAT NOT NULL,
    semester VARCHAR(50) NOT NULL,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

SELECT Queries

Authentication Queries

Find Admin by Email

SELECT * FROM admins WHERE email = ?;

Find Student by Email

SELECT * FROM students WHERE email = ?;

Find Student by Student ID

SELECT * FROM students WHERE student_id = ?;

Get User by ID (Admin)

SELECT * FROM admins WHERE id = ?;

Get User by ID (Student)

SELECT * FROM students WHERE student_id = ?;

Department Queries

Get Department by ID

SELECT * FROM departments WHERE id = ?;

Student Queries

Get Students by Department

SELECT * FROM students WHERE dept_id = ?;

Search Students with Filters

SELECT * FROM students
WHERE dept_id = ?
AND (
    name LIKE ? OR
    email LIKE ? OR
    student_id LIKE ? OR
    reg_no LIKE ? OR
    session LIKE ?
);

Get Student by Student ID

SELECT * FROM students WHERE student_id = ?;

Academic Record Queries

Get Academic Record by Student ID

SELECT * FROM academic_records WHERE student_id = ?;

Get Academic Record by Registration Number

SELECT * FROM academic_records WHERE reg_no = ?;

Get Academic Records for Department (with Join)

SELECT academic_records.*
FROM academic_records
JOIN students ON academic_records.student_id = students.student_id
WHERE students.dept_id = ?;

Scholarship Queries

Get Scholarships by Student ID

SELECT * FROM scholarships
WHERE student_id = ?
ORDER BY awarded_at DESC;

Get Scholarship by Student and Semester

SELECT * FROM scholarships
WHERE student_id = ? AND semester = ?;

Get Scholarship by Student, Type and Semester

SELECT * FROM scholarships
WHERE student_id = ? AND type = ? AND semester = ?;

Get All Scholarships for Department (with Student Join)

SELECT scholarships.*, students.*
FROM scholarships
JOIN students ON scholarships.student_id = students.student_id
WHERE students.dept_id = ?
ORDER BY scholarships.awarded_at DESC;

Stipend Queries

Get Stipends by Student ID

SELECT * FROM stipends
WHERE student_id = ?
ORDER BY awarded_at DESC;

Get Stipend by Student and Semester

SELECT * FROM stipends
WHERE student_id = ? AND semester = ?;

Get Stipend by Student, Type and Semester

SELECT * FROM stipends
WHERE student_id = ? AND type = ? AND semester = ?;

Get All Stipends for Department (with Student Join)

SELECT stipends.*, students.*
FROM stipends
JOIN students ON stipends.student_id = students.student_id
WHERE students.dept_id = ?
ORDER BY stipends.awarded_at DESC;

Application Queries

Get Applications by Student ID

SELECT * FROM applications
WHERE student_id = ?
ORDER BY created_at DESC;

Get Pending Application by Student, Type and Semester

SELECT * FROM applications
WHERE student_id = ? AND type = ? AND semester = ? AND status = 'Pending';

Get All Applications for Department (with Student Join)

SELECT applications.*
FROM applications
JOIN students ON applications.student_id = students.student_id
WHERE students.dept_id = ?;

Get Application by ID

SELECT * FROM applications WHERE id = ?;

Income Record Queries

Get Income Record by Student ID (Latest)

SELECT * FROM income_records
WHERE student_id = ?
ORDER BY date DESC
LIMIT 1;

Get Income Record by Student ID

SELECT * FROM income_records WHERE student_id = ?;

Join Queries for Scholarship Eligibility

Get Students with Academic Records for Scholarship Evaluation

SELECT students.*, academic_records.*
FROM students
JOIN academic_records ON students.student_id = academic_records.student_id
WHERE students.dept_id = ?;

INSERT Queries

Insert Scholarship

INSERT INTO scholarships (student_id, student_name, type, amount, semester)
VALUES (?, ?, ?, ?, ?);

Insert Stipend

INSERT INTO stipends (student_id, student_name, type, amount, semester)
VALUES (?, ?, ?, ?, ?);

Insert Application

INSERT INTO applications (student_id, type, semester, status)
VALUES (?, ?, ?, ?);

Insert Income Record

INSERT INTO income_records (student_id, amount, source, family_member)
VALUES (?, ?, ?, ?);

Insert New Student

INSERT INTO students (student_id, reg_no, dept_id, name, session, email, password)
VALUES (?, ?, ?, ?, ?, ?, ?);

Insert New Admin

INSERT INTO admins (name, dept_id, email, password)
VALUES (?, ?, ?, ?);

Insert New Department

INSERT INTO departments (name, faculty, budget)
VALUES (?, ?, ?);

Insert New Academic Record

INSERT INTO academic_records (reg_no, student_id, cgpa, current_semester)
VALUES (?, ?, ?, ?);

UPDATE Queries

Update Academic Record

UPDATE academic_records
SET current_semester = ?,
    semester_1_gpa = ?,
    semester_2_gpa = ?,
    semester_3_gpa = ?,
    semester_4_gpa = ?,
    semester_5_gpa = ?,
    semester_6_gpa = ?,
    semester_7_gpa = ?,
    semester_8_gpa = ?,
    cgpa = ?
WHERE student_id = ?;

Update Department Budget

UPDATE departments
SET budget = budget - ?
WHERE id = ?;

Update Application Status (Approve)

UPDATE applications
SET status = 'Approved',
    updated_at = CURRENT_TIMESTAMP
WHERE id = ?;

Update Application Status (Reject)

UPDATE applications
SET status = 'Rejected',
    updated_at = CURRENT_TIMESTAMP
WHERE id = ?;

Update Income Record

UPDATE income_records
SET amount = ?,
    source = ?,
    family_member = ?
WHERE student_id = ?;

Update Student Information

UPDATE students
SET name = ?,
    email = ?,
    session = ?
WHERE student_id = ?;

DELETE Queries

Delete Student (Cascade deletes academic_records and income_records)

DELETE FROM students WHERE student_id = ?;

Delete Scholarship

DELETE FROM scholarships WHERE id = ?;

Delete Stipend

DELETE FROM stipends WHERE id = ?;

Delete Application

DELETE FROM applications WHERE id = ?;

Aggregate and Join Queries

Count Total Students in Department

SELECT COUNT(*) FROM students WHERE dept_id = ?;

Sum of All Scholarships for Department

SELECT SUM(scholarships.amount)
FROM scholarships
JOIN students ON scholarships.student_id = students.student_id
WHERE students.dept_id = ?;

Sum of All Stipends for Department

SELECT SUM(stipends.amount)
FROM stipends
JOIN students ON stipends.student_id = students.student_id
WHERE students.dept_id = ?;

Count of Scholarships Awarded in Department

SELECT COUNT(*)
FROM scholarships
JOIN students ON scholarships.student_id = students.student_id
WHERE students.dept_id = ?;

Count of Stipends Awarded in Department

SELECT COUNT(*)
FROM stipends
JOIN students ON stipends.student_id = students.student_id
WHERE students.dept_id = ?;

Get Academic Records Statistics for Department

SELECT academic_records.*
FROM academic_records
JOIN students ON academic_records.student_id = students.student_id
WHERE students.dept_id = ?;

Get Students with Academic Records and Income Information

SELECT students.*, academic_records.*, income_records.*
FROM students
LEFT JOIN academic_records ON students.student_id = academic_records.student_id
LEFT JOIN income_records ON students.student_id = income_records.student_id
WHERE students.dept_id = ?;

Get Application Details with Student Information

SELECT applications.*, students.name, students.email, students.session
FROM applications
JOIN students ON applications.student_id = students.student_id
WHERE applications.id = ?;

Get Scholarship Distribution by Type

SELECT type, COUNT(*) as count, SUM(amount) as total_amount
FROM scholarships
JOIN students ON scholarships.student_id = students.student_id
WHERE students.dept_id = ?
GROUP BY type;

Get Stipend Distribution by Type

SELECT type, COUNT(*) as count, SUM(amount) as total_amount
FROM stipends
JOIN students ON stipends.student_id = students.student_id
WHERE students.dept_id = ?
GROUP BY type;

Get Average CGPA for Department

SELECT AVG(academic_records.cgpa)
FROM academic_records
JOIN students ON academic_records.student_id = students.student_id
WHERE students.dept_id = ?;

Get Students Above Certain CGPA Threshold

SELECT students.*, academic_records.cgpa
FROM students
JOIN academic_records ON students.student_id = academic_records.student_id
WHERE students.dept_id = ? AND academic_records.cgpa >= ?;

Get Total Financial Aid per Student

SELECT
    students.student_id,
    students.name,
    COALESCE(SUM(scholarships.amount), 0) as scholarship_total,
    COALESCE(SUM(stipends.amount), 0) as stipend_total,
    COALESCE(SUM(scholarships.amount), 0) + COALESCE(SUM(stipends.amount), 0) as total_aid
FROM students
LEFT JOIN scholarships ON students.student_id = scholarships.student_id
LEFT JOIN stipends ON students.student_id = stipends.student_id
WHERE students.dept_id = ?
GROUP BY students.student_id, students.name;

Graph View

Backlinks