List all the students and their classes:
SELECT students.name, classes.name FROM enrollments JOIN students ON enrollments.student_id = students.id JOIN classes ON enrollments.class_id = classes.id;
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 JOIN students ON enrollments.student_id = students.id JOIN classes ON enrollments.class_id = classes.id;
List all the students and their average grade:
SELECT students.name, AVG(enrollments.grade) FROM enrollments JOIN students ON enrollments.student_id = students.id JOIN classes ON enrollments.class_id = classes.id GROUP BY students.name;
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;
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;
List all the teachers for each student:
SELECT students.name, teachers.name 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;
List all the teachers for each student grouped by each student:
SELECT students.name, teachers.name 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;
Find the average grade for each class:
SELECT classes.name, AVG(enrollments.grade) FROM enrollments JOIN students ON enrollments.student_id = students.id JOIN classes ON enrollments.class_id = classes.id GROUP BY classes.name;
List students’ name and their grade IF their grade is lower than the average:
SELECT students.name, AVG(enrollments.grade) AS "average_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);