School Database Case Study

About this Project

Database Schema

CREATE TABLE classes (class_id INT PRIMARY KEY, class_name VARCHAR(20));
CREATE TABLE students (roll_no INT PRIMARY KEY, name VARCHAR(50), class_id INT, marks INT,
  FOREIGN KEY(class_id) REFERENCES classes(class_id));
CREATE TABLE fees (fee_id INT PRIMARY KEY, roll_no INT, amount DECIMAL(8,2), paid_on DATE,
  FOREIGN KEY(roll_no) REFERENCES students(roll_no));

Useful Queries

-- topper of each class
SELECT class_id, name, marks FROM students s1
WHERE marks = (SELECT MAX(marks) FROM students s2 WHERE s2.class_id=s1.class_id);

-- total fee collected per class
SELECT c.class_name, SUM(f.amount) AS collected
FROM fees f JOIN students s ON f.roll_no=s.roll_no
           JOIN classes c ON s.class_id=c.class_id
GROUP BY c.class_name;

Summary

• This project shows a real, exam-ready database design with working queries.
• Create the tables, insert sample data, and run each query to learn by doing.

इस Project के बारे में

Database Schema

CREATE TABLE classes (class_id INT PRIMARY KEY, class_name VARCHAR(20));
CREATE TABLE students (roll_no INT PRIMARY KEY, name VARCHAR(50), class_id INT, marks INT,
  FOREIGN KEY(class_id) REFERENCES classes(class_id));
CREATE TABLE fees (fee_id INT PRIMARY KEY, roll_no INT, amount DECIMAL(8,2), paid_on DATE,
  FOREIGN KEY(roll_no) REFERENCES students(roll_no));

उपयोगी Queries

-- हर class का topper
SELECT class_id, name, marks FROM students s1
WHERE marks = (SELECT MAX(marks) FROM students s2 WHERE s2.class_id=s1.class_id);

-- हर class में collect हुई कुल fee
SELECT c.class_name, SUM(f.amount) AS collected
FROM fees f JOIN students s ON f.roll_no=s.roll_no
           JOIN classes c ON s.class_id=c.class_id
GROUP BY c.class_name;

सारांश

• यह project एक real, exam-ready database design working queries के साथ दिखाता है।
• Tables बनाएं, sample data डालें, और हर query चलाकर करके सीखें।