Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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);
Add Comment
Please, Sign In to add comment