Intermediate_SQL_II.md

Intermediate SQL

https://gist.github.com/case-eee/5affe7fd452336cef2c88121e8d49f5d

Practice!!

• List all the students and their classes

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

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

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

• List all the students and their average grade

SELECT s.name students, avg(e.grade) average_grade FROM students s
JOIN enrollments e ON e.student_id=s.id
JOIN classes c ON e.class_id=c.id
GROUP BY s.name;

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

SELECT s.name students, count(c.*) classes_enrolled FROM students s
JOIN enrollments e ON e.student_id=s.id
JOIN classes c ON e.class_id=c.id
GROUP BY s.name;

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

SELECT s.name students, count(c.*) classes_enrolled FROM students s
JOIN enrollments e ON e.student_id=s.id
JOIN classes c ON e.class_id=c.id
GROUP BY s.name
HAVING count(c.*) > 2;

• List all the teachers for each student

SELECT s.name student, t.name teacher FROM students s
JOIN enrollments e ON e.student_id=s.id
JOIN classes c ON e.class_id=c.id
JOIN teachers t ON c.teacher_id=t.id
GROUP BY s.name, t.name;

• List all the teachers for each student grouped by each student

SELECT s.name student, t.name teacher FROM students s
JOIN enrollments e ON e.student_id=s.id
JOIN classes c ON e.class_id=c.id
JOIN teachers t ON c.teacher_id=t.id
ORDER BY s.name;

• Find the average grade for a each class

SELECT c.name, avg(e.grade) avg_grade FROM classes c
JOIN enrollments e ON c.id=e.class_id
GROUP BY c.name;

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

This one still gives me fits...

SELECT s.name AS student, c.name AS class, grade
FROM students s                   
JOIN enrollments e ON s.id=e.student_id JOIN classes c ON c.id=e.class_id
JOIN (SELECT c.name, ROUND(AVG(e.grade),2) AS avg_grade
FROM classes c
JOIN enrollments e ON c.id=e.class_id
GROUP BY c.name) AS class_score ON class_score.name=c.name
WHERE grade < avg_grade;