首先,本题中的示例中未显示全部信息,缺少日期date的信息;
其次,存在两张表的联结,匹配出相关信息;
第三,回答正确的题目数,涉及到case when ...then ...end多条件判断后再用count()聚合函数进行统计;

SELECT q.device_id,
       u.university,
       COUNT(q.id) as question_cnt,
       COUNT(case when q.result="right" then q.id end) as right_question_cnt
FROM user_profile AS u JOIN question_practice_detail as q
ON u.device_id=q.device_id
WHERE u.university="复旦大学" AND month(q.date)="08" ;