-- Get the last name, first name, classroom id, and grades of the students

SELECT s.LastName, s.FirstName, se.ClassroomId, se.Grade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId

-- Get the last name, first name, classroom name, and grades of the students

SELECT s.LastName, s.FirstName, c.ClassName, se.Grade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId

-- Get the first name, last name, average of all grades of each student

SELECT s.FirstName, s.LastName, AVG(se.Grade) AS AverageGrade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
GROUP BY s.StudentId

-- What is the average score of Donald Duck?

SELECT s.FirstName, s.LastName, AVG(se.Grade)
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
WHERE s.FirstName = 'Donald' AND
s.LastName = 'Duck'

-- Which class of Mickey Mouse has the highest grade? (Give the class name)

SELECT s.FirstName, s.LastName, se.Grade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
WHERE s.FirstName = 'Mickey' AND
s.LastName = 'Mouse'
ORDER BY se.Grade DESC
LIMIT 1

 -- Get the average score for each class (show the class name and average score)
 
 SELECT c.ClassName, AVG(se.Grade) AS AverageGrade
 FROM StudentEnrollments se 
 INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
 GROUP BY c.ClassName

-- Get the last name, first name, Class Name and weighted grade (weight * grade) of each student

SELECT s.LastName, s.FirstName, c.ClassName, (c.Weight * se.Grade) AS WeightedGrade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId

 --   What is the final grade of Donald Duck?
      -- To compute the final grade, sum all the weight * grade of each class
      -- Example: (Class #1 Weight * Class #1 Grade) + (Class #2 Weight * Class #2 Grade) and so on

 SELECT SUM(c.Weight * se.Grade) AS FinalGrade
 FROM StudentEnrollments se
 INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
 WHERE StudentId IN
 (
 SELECT StudentId
 FROM Students
 WHERE LastName = 'Duck' AND FirstName = 'Donald'
 )

 --   What is the final grade of all of the students? Show FirstName, LastName and the Final Grade
 
 SELECT s.FirstName, s.LastName, SUM(c.Weight * se.Grade) AS FinalGrade
 FROM Students s
 INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
 INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
 GROUP BY s.StudentId

 --  Get the Average grade of each student for the Running and Acting classes. Show FirstName, LastName, and Average Grade of the 2 classes

 SELECT s.FirstName, s.LastName, AVG(se.Grade) AS AverageGrade
 FROM Students s
 INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
 WHERE ClassroomId IN
 (
 SELECT ClassroomId
 FROM Classrooms
 WHERE ClassName IN ('Running', 'Acting')
 )
 GROUP BY s.StudentId