sample students subjects marks data

students_marks.sql

-- Create the subjects table
CREATE TABLE subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Create the students table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Create the student_subjects table (many-to-many relationship)
CREATE TABLE student_subjects (
    student_id INT,
    subject_id INT,
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Create the marks table
CREATE TABLE marks (
    student_id INT,
    subject_id INT,
    marks INT CHECK (marks BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

-- Insert sample subjects
INSERT INTO subjects (name) VALUES
('Mathematics'),
('Physics'),
('Chemistry'),
('Biology'),
('Computer Science');

-- Insert sample students
INSERT INTO students (name) VALUES
('Alice Johnson'), ('Bob Smith'), ('Charlie Davis'), ('David Brown'), ('Emma Wilson'),
('Fiona Clark'), ('George White'), ('Hannah Lewis'), ('Ian Walker'), ('Jack Hall'),
('Karen Allen'), ('Liam Young'), ('Mia Scott'), ('Noah King'), ('Olivia Adams'),
('Peter Wright'), ('Quinn Baker'), ('Rachel Nelson'), ('Samuel Carter'), ('Tina Perez'),
('Umar Robinson'), ('Violet Reed'), ('William Carter'), ('Xavier Howard'), ('Yasmine Hill'),
('Zachary Wood'), ('Eleanor Brooks'), ('Frank Turner'), ('Grace Simmons'), ('Henry Evans'),
('Isabella Thomas'), ('James Moore'), ('Natalie Green'), ('Oscar Parker'), ('Sophia Collins'),
('Benjamin Phillips'), ('Charlotte Harris'), ('Daniel Edwards'), ('Emily Thompson'), ('Lucas Martin');

-- Assign up to three random subjects per student
INSERT INTO student_subjects (student_id, subject_id)
SELECT student_id, subject_id FROM (
    SELECT s.id AS student_id, sub.id AS subject_id, ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY RAND()) AS rn
    FROM students s CROSS JOIN subjects sub
) t WHERE t.rn <= 3;

-- Insert marks (random marks between 40 and 100)
INSERT INTO marks (student_id, subject_id, marks)
SELECT ss.student_id, ss.subject_id, FLOOR(RAND() * 71) + 30
FROM student_subjects ss;