1. Get the last name, first name, classroom name, 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 

 

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

SELECT s.FirstName, s.LastName, avg(se.grade) as Grade_Average 

FROM Students s 

INNER JOIN StudentEnrollments se on s.studentid = se.StudentId 

GROUP BY s.FirstName, s.LastName 

 

 3.What is the average score of Donald Duck? 

--What is the average score of Donald Duck?  

SELECT s.FirstName, s.LastName, avg(grade) as GradeAverage 

FROM Students s 

INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId 

WHERE s.FirstName like 'Donald' and s.LastName like 'Duck' 

 

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

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

SELECT s.FirstName, s.LastName, 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 

WHERE s.FirstName like 'Mickey' and LastName like 'Mouse' 

ORDER BY se.Grade DESC 

LIMIT 1 

 

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

SELECT c.ClassName, AVG(se.grade) as ClassAverage 

FROM StudentEnrollments se 

INNER JOIN Classrooms c on se.ClassroomId = c.ClassroomId 

GROUP BY c.ClassName 

 

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

SELECT s.LastName, s.FirstName, sum(se.grade*c.weight) as Weighted_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, s.LastName 

 

7.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(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 

WHERE s.LastName like 'Duck' and s.FirstName like 'Donald' 

 

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

SELECT s.FirstName, s.LastName, sum(se.Grade * c.Weight) as FINAL_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, s.LastName 

 

 9.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(grade) Average_Grade_of_Classes 

FROM Students s 

INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId 

INNER JOIN Classrooms c on se.ClassroomId = c.ClassroomId 

WHERE c.ClassName like 'Running' or c.ClassName like 'Acting'  

GROUP BY s.FirstName, s.LastName