1.
List all the students and their classes

SELECT students.name, classes.name
FROM enrollments
INNER JOIN students ON enrollments.student_id = students.id
INNER JOIN classes ON enrollments.class_id = classes.id;

2.

List all the students and their classes and rename the columns to “student” and “class”

SELECT students.name AS "student", classes.name AS "class"
FROM enrollments
INNER JOIN students
ON enrollments.student_id = students.id
INNER JOIN classes
ON enrollments.class_id = classes.id;

3.
List all the students and their average grade

 SELECT students.name, AVG(enrollments.grade)
 FROM enrollments JOIN students
 ON enrollments.student_id = students.id
 GROUP BY students.name;

4.
List all the students and a count of how many classes they are currently enrolled in

SELECT students.name, COUNT(classes) AS Class_Count
FROM enrollments JOIN students ON enrollments.student_id = students.id
JOIN classes ON enrollments.class_id = classes.id GROUP BY students.name;

5.
List all the students and their class count IF they are in more than 2 classes

SELECT students.name, COUNT(classes)
AS Class_Count FROM enrollments
JOIN students ON enrollments.student_id = students.id
JOIN classes ON enrollments.class_id = classes.id GROUP BY students.name HAVING COUNT(classes)>=2;

6.
List all the teachers for each student

SELECT teachers.name AS teacher, students.name
AS student FROM enrollments JOIN students
ON enrollments.student_id = students.id
JOIN classes on enrollments.class_id = classes.id
JOIN teachers ON classes.teacher_id =  teachers.id;

7.
List all the teachers for each student grouped by each student

SELECT teachers.name AS teacher, students.name
AS student FROM enrollments JOIN students
ON enrollments.student_id = students.id
JOIN classes on enrollments.class_id = classes.id
JOIN teachers ON classes.teacher_id =  teachers.id GROUP BY students.name, teachers.name ORDER BY students.name;


8.
Find the average grade for a each class

SELECT classes.name AS class, AVG(enrollments.grade)
AS average_grade FROM enrollments
JOIN classes on enrollments.class_id = classes.id GROUP BY classes.name;


9.

List students’ name and their grade IF their grade is lower than the average.

SELECT students.name, AVG(enrollments.grade)
FROM enrollments
JOIN students ON enrollments.student_id = students.id
GROUP BY students.name HAVING AVG(enrollments.grade) < ( SELECT AVG(enrollments.grade)
FROM enrollments
JOIN students ON enrollments.student_id = students.id);