Skip to content

Instantly share code, notes, and snippets.

@timomitchel
Last active January 10, 2018 19:45
Show Gist options
  • Save timomitchel/2e369e2f91b0721045ed138667a2da40 to your computer and use it in GitHub Desktop.
Save timomitchel/2e369e2f91b0721045ed138667a2da40 to your computer and use it in GitHub Desktop.
PSQL Queries from Turing School

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment