Complex Queries

http://backend.turing.io/module2/misc/complex_queries


## Find the average grade for each class

SELECT c.name, ROUND(AVG(e.grade),2) AS avg_grade
FROM classes c
INNER 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.

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;