恶补SQL中）牛客SQL题（二）

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';