SQL16 查找GPA最高值
select max(gpa) from user_profile where university='复旦大学';
SQL17 计算男生人数以及平均GPA
select count(gender) as male_num,avg(gpa) as avg_gpa from user_profile where gender='male';
SQL18 分组计算练习题
select gender,university,count(*) as user_num,avg(active_days_within_30) as avg_active_day, avg(question_cnt) as avg_question_cnt from user_profile group by gender,university;
SQL19 分组过滤练习题
select university,avg(question_cnt) as avg_question_cnt ,avg(answer_cnt) as avg_answer_cnt from user_profile group by university having (avg(question_cnt)<5 or avg(answer_cnt)<20);
SQL20 分组排序练习题
select university,avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg(question_cnt) asc;
SQL21 浙江大学用户题目回答情况
select device_id,question_id,result from question_practice_detail where device_id=(select device_id from user_profile where university='浙江大学');
SQL22 统计每个学校的答过题的用户的平均答题数
select university,
count(question_id) / count(distinct q1.device_id) as avg_answer_cnt
from question_practice_detail as q1
inner join user_profile as q2
on q1.device_id=q2.device_id
group by university;
SQL23 统计每个学校各难度的用户平均刷题数
select university,qd.difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id=qpd.device_id
join question_detail as qd
on qpd.question_id=qd.question_id
group by university,qd.difficult_level
SQL24 统计每个用户的平均刷题数
select university,qd.difficult_level,count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id=qpd.device_id
join question_detail as qd
on qpd.question_id=qd.question_id
where university='山东大学'
group by university,qd.difficult_level;
SQL25 查找山东大学或者性别为男生的信息
select device_id,gender,age,gpa
from user_profile
where university='山东大学'
union all
select device_id,gender,age,gpa
from user_profile
where gender='male';