This is my final project for this course. It was a lot of fun!

--Question 1 Get the last name, first name, classroom id, and grades of the students
SELECT s.LastName, s.FirstName, c.ClassroomId, se.Grade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId

--Question 2 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

--Question 3 Get the first name, last name, average of all grades of each student
SELECT s.LastName, s.FirstName, AVG (se.Grade)
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
GROUP BY s.FirstName

--Question 4 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'

--Question 5 Which class of Mickey Mouse has the highest grade? (Give the class name)
SELECT s.LastName, s.FirstName, se.Grade, c.ClassName
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
WHERE s.FirstName = 'Mickey' AND
s.LastName = 'Mouse'
ORDER BY se.Grade DESC
LIMIT 1
--Question 6 Get the average score for each class (show the class name and average score)
SELECT c.ClassName, AVG (se.Grade)
FROM StudentEnrollments se
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
GROUP BY c.ClassName

--Question 7 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

-- Question 8 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 s.LastName, s.FirstName, SUM (se.Grade *c.Weight) AS FinalGrade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
WHERE s.FirstName = 'Donald' AND
LastName = 'Duck'

--Question 9 What is the final grade of all of the students? Show FirstName, LastName and the Final Grade
SELECT s.LastName, s.FirstName, SUM (se.Grade *c.Weight) 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.FirstName

--Question 10 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.LastName, s.FirstName, AVG (se.Grade) AS AverageGrade
FROM Students s
INNER JOIN StudentEnrollments se ON s.StudentId = se.StudentId
INNER JOIN Classrooms c ON se.ClassroomId = c.ClassroomId
WHERE c.ClassName = 'Running' OR 'Acting'
GROUP BY s.FirstName