関連性
生徒情報
生徒の平均年齢
SELECT AVG(age) FROM students;
生徒の最高年齢と最低年齢
SELECT MAX(age), MIN(age) FROM students;
男子生徒の平均年齢
SELECT AVG(age) FROM students WHERE gender=0;
男子生徒の最高年齢と最低年齢
SELECT MAX(age), MIN(age) FROM students WHERE gender=0;
女子生徒の平均年齢
SELECT AVG(age) FROM students WHERE gender=1;
女子生徒の最高年齢と最低年齢
SELECT MAX(age), MIN(age) FROM students WHERE gender=1;
学科情報
学科のidと、学科ごとの所属している生徒の人数
SELECT subject_id, COUNT(subject_id) FROM students GROUP BY subject_id;
学科のidと、学科ごとの所属している男子生徒の人数
SELECT subject_id, COUNT(subject_id) FROM students WHERE gender=0 GROUP BY subject_id;
学科のidと、学科ごとの所属している女子生徒の人数
SELECT subject_id, COUNT(subject_id) FROM students WHERE gender=1 GROUP BY subject_id;
サークル情報
生徒の名前と、生徒が所属しているサークルの数（帰宅部の人のデータは取得しない）
SELECT
students.name,
COUNT(club_students.club_id)
FROM students
INNER JOIN club_students
ON students.id = club_students.student_id
INNER JOIN clubs
ON club_students.club_id = clubs.id
GROUP BY students.id, students.name
SELECT
students.name,
COUNT(club_id)
FROM
students
INNER JOIN club_students
ON students.id = club_students.student_id
GROUP BY
students.id;
生徒の名前と、生徒が所属しているサークルの数（帰宅部の人のデータも確認する）
SELECT
students.name,
COUNT(club_students.club_id)
FROM students
LEFT JOIN club_students
ON students.id = club_students.student_id
LEFT JOIN clubs
ON club_students.club_id = clubs.id
GROUP BY students.id, students.name
SELECT
students.name,
COUNT(club_id)
FROM
students
LEFT JOIN club_students
ON students.id = club_students.student_id
GROUP BY
students.id;
サークルの名前と、サークルごとの生徒の数
SELECT
clubs.name,
COUNT(students.id)
FROM students
INNER JOIN club_students
ON students.id = club_students.student_id
INNER JOIN clubs
ON club_students.club_id = clubs.id
GROUP BY clubs.id, clubs.name
サークルの名前と、サークルごとの男子生徒の数
SELECT
clubs.name,
COUNT(students.id)
FROM students
INNER JOIN club_students
ON students.id = club_students.student_id
INNER JOIN clubs
ON club_students.club_id = clubs.id
WHERE students.gender = 0
GROUP BY clubs.id, clubs.name
サークルの名前と、サークルごとの女子生徒の数
SELECT
clubs.name,
COUNT(students.id)
FROM students
INNER JOIN club_students
ON students.id = club_students.student_id
INNER JOIN clubs
ON club_students.club_id = clubs.id
WHERE students.gender = 1
GROUP BY clubs.id, clubs.name
サークルの名前と、サークルごとの男子生徒と女子生徒の数
SELECT
clubs.name,
SUM(CASE WHEN students.gender = 0 THEN 1 ELSE 0 END) as male,
SUM(CASE WHEN students.gender = 1 THEN 1 ELSE 0 END) as female
FROM students
INNER JOIN club_students
ON students.id = club_students.student_id
INNER JOIN clubs
ON club_students.club_id = clubs.id
GROUP BY clubs.id, clubs.name
試験結果
試験ごとの最高得点と最低得点
SELECT
exam_results.name,
MAX(exam_results.score),
MIN(exam_results.score)
FROM exam_results
GROUP BY exam_results.name
試験ごとの男子生徒の最高得点と最低得点
SELECT
exam_results.name,
MAX(exam_results.score),
MIN(exam_results.score)
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id
WHERE students.gender=0
GROUP BY exam_results.name
試験ごとの女子生徒の最高得点
SELECT
exam_results.name,
MAX(exam_results.score),
MIN(exam_results.score)
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id
WHERE students.gender=1
GROUP BY exam_results.name
試験ごとの平均得点
SELECT
exam_results.name,
AVG(exam_results.score)
FROM exam_results
GROUP BY exam_results.name
試験ごとの男子生徒の平均得点
SELECT
exam_results.name,
AVG(exam_results.score)
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id
WHERE students.gender=0
GROUP BY exam_results.name
試験ごとの女子生徒の平均得点
SELECT
exam_results.name,
AVG(exam_results.score)
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id
WHERE students.gender=1
GROUP BY exam_results.name