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

   Select LastName, FirstName, tSe.ClassroomId, tSe.Grade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId

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

   Select LastName, FirstName, tC.ClassName, tSe.Grade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId

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

   Select FirstName, LastName, AVG(tSe.Grade) AS AverageGrade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   GROUP BY tS.StudentId

--What is the average score of Donald Duck?

   Select FirstName, LastName, AVG(tSe.Grade) AS AverageGrade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   WHERE tS.FirstName='Donald' AND tS.LastName='Duck'
   GROUP BY tS.StudentId

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

   Select tC.ClassName
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId
   WHERE tS.FirstName='Mickey' AND tS.LastName='Mouse'
   GROUP BY tS.StudentId

--Get the average score for each class (show the class name and average score)

   SELECT tC.ClassName, AVG(tSe.Grade) AS AverageScore
   FROM StudentEnrollments AS tSe
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId
   GROUP BY tSe.ClassroomId

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

   SELECT LastName, FirstName, tC.ClassName, ROUND(tSe.Grade * tC.Weight,2) AS WeightedGrade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId
   GROUP BY tS.StudentId, tC.ClassName

--What is the final grade of Donald Duck?

   SELECT SUM(tC.Weight*tSe.Grade) AS FinalGrade
   FROM StudentEnrollments AS tSe
   INNER JOIN
   (
       SELECT StudentId
       FROM Students AS tS
       WHERE tS.LastName='Duck' AND tS.FirstName='Donald'
   ) AS tStemp
   ON tStemp.StudentId=tSe.StudentId
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId

--What is the final grade of all of the students? Show FirstName, LastName and the Final Grade

   SELECT FirstName, LastName, ROUND(SUM(tC.Weight * tSe.Grade),2) AS FinalGrade
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   INNER JOIN Classrooms AS tC ON tC.ClassroomId=tSe.ClassroomId
   GROUP BY ts.StudentId
   ORDER BY FinalGrade DESC

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

   SELECT FirstName, LastName, AVG(tCtemp.Weight * tSe.Grade) AS AverageOfTwo
   FROM Students AS tS
   INNER JOIN StudentEnrollments AS tSe ON tSe.StudentId=tS.StudentId
   INNER JOIN
   (
       SELECT ClassroomId, ClassName, Weight
       FROM Classrooms
       WHERE ClassName IN ('Running', 'Acting')
   ) AS tCtemp
   ON tCtemp.ClassroomId=tSe.ClassroomId
   GROUP BY tS.StudentId