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

Bence Tóth

20

17