intermediate sql workshop 2 answers

inter_2_answers.markdown

• List all the students and their classes

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

• List all the students and their classes and rename the columns to "student" and "class"

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

• List all the students and their average grade

SELECT students.name, avg(grade)
FROM students
INNER JOIN enrollments
ON enrollments.student_id = students.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)
FROM students
INNER JOIN enrollments
ON enrollments.student_id = students.id
INNER 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)
FROM students
INNER JOIN enrollments
ON enrollments.student_id = students.id
INNER 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 students
INNER JOIN enrollments
ON enrollments.student_id = students.id
INNER JOIN classes
ON enrollments.class_id = classes.id
INNER 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 students
INNER JOIN enrollments
ON enrollments.student_id = students.id
INNER JOIN classes
ON enrollments.class_id = classes.id
INNER JOIN teachers
ON classes.teacher_id = teachers.id
GROUP BY students.name, teachers.name;

• Find the average grade for a each class

SELECT classes.name, avg(grade)
FROM classes
INNER JOIN enrollments
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(grade)
FROM students
INNER JOIN enrollments
ON enrollments.student_id = students.id
GROUP BY students.name
HAVING avg(grade) < (SELECT avg(grade)
FROM students
INNER JOIN enrollments
ON enrollments.student_id = students.id);