here is my all exercises solution for the course:

 

-- get the avarage score of student 1
SELECT (sum(Grade)/5) as avarage_grade_for_student_1
FROM StudentEnrollments
WHERE StudentId = 1

-- get the avarage score of student 1
SELECT avg(Grade) as avarage_grade_for_student_1
FROM StudentEnrollments
WHERE StudentId = 1

-- get the first 5 students
SELECT *
FROM Students
LIMIT 5

-- get the last 5 students
SELECT *
FROM Students
ORDER by StudentId DESC
LIMIT 5

-- get the list of unique nationalities of Students
SELECT DISTINCT (Nationality)
FROM Students

-- get the number of each nationality in the students list and return nationalities that have at least 2 people
SELECT Nationality, count (Nationality) as numberOfPeople
FROM Students
GROUP by Nationality
--WHERE count(Nationality) >= 2
HAVING count(nationality) >= 2

-- EXERCISES
-- what are the lastnames and count of each lastname in the students list
SELECT LastName, count(LastName)
FROM Students
GROUP by LastName

 

-- what are the lastnames and count of each lastname in the students list
-- ensure the list is ordered from the highest count to the lowest count
SELECT LastName, count(LastName) as No_of_People
FROM Students
GROUP by LastName
ORDER by no_of_people DESC--count(lastname) DESC

-- get all info of the 5 youngest students
SELECT *
FROM Students
ORDER by DateOfBirth DESC
LIMIT 5

 

-- what is avarage score of all exams taken?
-- (use studentsenrollement table)
SELECT avg(grade)
FROM StudentEnrollments

-- get a list of nationalities that have a letter c and have at least 2 studenst
SELECT Nationality, count(nationality)
FROM Students
where Nationality like '%c%'
GROUP by Nationality
HAVING count(nationality) >= 2

-- JOINS PRACTICE EXERCISES
-- get the FirstName, lastname, avarage of all grades of all Students
SELECT s.FirstName, s.LastName, avg(se.Grade) as AvarageScore
FROM Students s
INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId
GROUP by s.FirstName, s.LastName

-- what is the avarage of donald duck
SELECT s.FirstName, s.LastName, avg(se.Grade) as AvarageScore
FROM Students s
INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId
WHERE FirstName = 'Donald' AND LastName = 'Duck'
GROUP by s.FirstName, s.LastName

 

-- which class of micky mouse has the highest grade? give the ClassName
SELECT ClassName
FROM Students s
INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId
INNER JOIN Classrooms c on se.ClassroomId = c.ClassroomId
WHERE FirstName= 'Mickey' AND LastName= 'Mouse'
ORDER by Grade DESC
LIMIT 1

-- get the avarage score of each Class (show the ClassName and avarage score)
SELECT ClassName, avg(grade) as AvarageScore
FROM StudentEnrollments se
INNER JOIN Classrooms c on se.ClassroomId = c.ClassroomId
GROUP by ClassName

-- get the LastName, FirstName, ClassName and weighted Grade (Weight * grade) of each StudentDemo
SELECT LastName, FirstName, ClassName, Weight * grade as WeightedGrade
FROM Students s
INNER JOIN StudentEnrollments se on s.StudentId = se.StudentId
INNER JOIN Classrooms c on se.ClassroomId = c.ClassroomId

-- subqueries EXERCISES
-- 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 Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
WHERE s.FirstName = 'Donald' AND s.LastName = 'Duck'

 

-- What is the final grade of all students? show FirstName, LastName and final grade?
SELECT FirstName, LastName, sum(c.Weight * se.Grade) as FinalGrade
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN Classrooms c on c.ClassroomId = se.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 AvarageGrade
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
WHERE c.ClassName = 'Acting' or c.ClassName = 'Running'
GROUP by s.StudentId

 

-- the same solution with sub query
SELECT s.FirstName, s.LastName, avg(se.Grade) as AvarageGrade
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN
(
SELECT ClassroomId
FROM Classrooms c
WHERE c.ClassName in ('Acting' , 'Running')

) as cTemp
on cTemp.ClassroomId = se.ClassroomId
GROUP by s.StudentId

-- more sql q
-- how is the Duck family when it comes to running?
SELECT FirstName, LastName, ClassName, grade as RunningClassGrade
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
WHERE ClassName = 'Running' and LastName = 'Duck'
--GROUP by s.StudentId

 

-- what are the avarages of duck family
SELECT FirstName, LastName, avg(grade) as AvarageGrade
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
--INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
WHERE LastName = 'Duck'
GROUP by s.StudentId

 

-- Are there any missing students in any of the classes?
SELECT FirstName, LastName, count(c.ClassName) as TotalClasses
FROM Students s
INNER JOIN StudentEnrollments se on se.StudentId = s.StudentId
INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
GROUP by s.StudentId
-- another solution
SELECT c.ClassName, count(se.StudentId) AS NumberOfStudents
FROM StudentEnrollments se
INNER JOIN Classrooms c on c.ClassroomId = se.ClassroomId
GROUP by se.ClassroomId

 

-- List of students born in the month of November
SELECT FirstName, LastName, DateOfBirth
FROM Students
--WHERE DateOfBirth like '%-11-%'
--WHERE DateOfBirth like '____-11%'
--WHERE DateOfBirth like '_____11___'
--WHERE DateOfBirth like '____-11-__'
WHERE DateOfBirth like '%11___'