# $1280. 学生们参加各科测试的次数
# https://leetcode-cn.com/problems/students-and-examinations/

# SQL架构
Create table If Not Exists Students (student_id int, student_name varchar(20));
Create table If Not Exists Subjects (subject_name varchar(20));
Create table If Not Exists Examinations (student_id int, subject_name varchar(20));
Truncate table Students;
insert into Students (student_id, student_name) values ('1', 'Alice');
insert into Students (student_id, student_name) values ('2', 'Bob');
insert into Students (student_id, student_name) values ('13', 'John');
insert into Students (student_id, student_name) values ('6', 'Alex');
Truncate table Subjects;
insert into Subjects (subject_name) values ('Math');
insert into Subjects (subject_name) values ('Physics');
insert into Subjects (subject_name) values ('Programming');
Truncate table Examinations;
insert into Examinations (student_id, subject_name) values ('1', 'Math');
insert into Examinations (student_id, subject_name) values ('1', 'Physics');
insert into Examinations (student_id, subject_name) values ('1', 'Programming');
insert into Examinations (student_id, subject_name) values ('2', 'Programming');
insert into Examinations (student_id, subject_name) values ('1', 'Physics');
insert into Examinations (student_id, subject_name) values ('1', 'Math');
insert into Examinations (student_id, subject_name) values ('13', 'Math');
insert into Examinations (student_id, subject_name) values ('13', 'Programming');
insert into Examinations (student_id, subject_name) values ('13', 'Physics');
insert into Examinations (student_id, subject_name) values ('2', 'Math');
insert into Examinations (student_id, subject_name) values ('1', 'Math');

# Write your MySQL query statement below
select
    st.student_id,
    st.student_name,
    su.subject_name,
    count(e.subject_name) as attended_exams
from
    Students st
    cross join Subjects su
    left join Examinations e on st.student_id = e.student_id
    and su.subject_name = e.subject_name
group by
    st.student_id,
    # sql_mode=only_full_group_by
    st.student_name,
    su.subject_name
order by
    st.student_id,
    su.subject_name;

# clean-up
drop table Students;
drop table Subjects;
drop table Examinations;