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

Micozd

--

2