Practice: Reporting Queries

Analysts deliver grouped metrics — averages, counts, totals — with clear column aliases for dashboards.

Class average report

Analysts deliver grouped metrics — averages, counts, totals — with clear column aliases for dashboards.

Real-life example: This report is the principal's Monday email — average score per subject last term.

Average and count per course

SELECT
  c.course_name AS subject,
  COUNT(e.enrollment_id) AS students_graded,
  ROUND(AVG(e.score), 2) AS class_average,
  MIN(e.score) AS lowest,
  MAX(e.score) AS highest
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name
ORDER BY class_average DESC;

City-wise enrollment summary

Combine students and enrollments to show activity by city — common in marketing and ops reports.

Real-life example: City summary tells the school board which regions send the most enrollments online.

Enrollments and average score by city

SELECT
  s.city,
  COUNT(DISTINCT s.student_id) AS student_count,
  COUNT(e.enrollment_id) AS total_enrollments,
  AVG(e.score) AS avg_score
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city
ORDER BY total_enrollments DESC;

Honor roll list

Filter, join, and sort for a printable list — score threshold, readable columns, stable ORDER BY.

Real-life example: Honor roll is the framed poster in the hallway — names above 90, sorted alphabetically.

Students scoring 90+ with course names

SELECT
  s.full_name,
  c.course_name,
  e.score
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE e.score >= 90
ORDER BY s.full_name, c.course_name;