12-学生们参加各科测试的次数

-- 学生表中，id是唯一的，将他作为主表
--  CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积
-- 2行表,与3行表使用cross join,得到2*3=6行数据
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 
    e.student_id=st.student_id and e.subject_name=su.subject_name
group by 
    st.student_id, st.student_name,su.subject_name 
order by 
    st.student_id,st.student_name;