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

Shaq

--

4