CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    grade VARCHAR(10) NOT NULL
);

CREATE TABLE Subjects (
    subject_id INT AUTO_INCREMENT PRIMARY KEY,
    subject_name VARCHAR(50) NOT NULL
);

CREATE TABLE Marks (
    mark_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    subject_id INT,
    marks_obtained INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);

INSERT INTO Students (first_name, last_name, grade) VALUES
('Mary', 'Anne', '3rd Grade'),
('Nicole', 'Kidman', '7th Grade'),
('Lara', 'Raj', '10th grade');

select *from Students;

INSERT INTO Subjects (subject_name) VALUES
('Maths'),
('Drama'),
('Physics');

select *from Subjects;

INSERT INTO Marks (student_id, subject_id, marks_obtained) VALUES
(1,1,95),
(1,2,97),
(1,3,93),
(2,1,98),
(2,2,90),
(2,3,95),
(3,1,100),
(3,2,100),
(3,3,99);

select *from Marks;

SELECT
    Students.first_name,
    Students.last_name,
    AVG(Marks.marks_obtained) AS average_marks
FROM Students
JOIN Marks ON Students.student_id = Marks.student_id
GROUP BY Students.student_id;