SQl server 练习3

课后作业

在homework库下执行：

CREATE TABLE user_profile_2 (

id int NOT NULL,

device_id int NOT NULL,

gender varchar(14) NOT NULL,

age int ,

university varchar(32) NOT NULL,

gpa float,

active_days_within_30 float,

question_cnt float,

answer_cnt float

);

INSERT INTO user_profile_2 VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);

INSERT INTO user_profile_2 VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);

INSERT INTO user_profile_2 VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);

INSERT INTO user_profile_2 VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);

INSERT INTO user_profile_2 VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);

INSERT INTO user_profile_2 VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);

INSERT INTO user_profile_2 VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

注：

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

作业1：

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

查询语句：

select gender,university, count(*) as user_num,
avg(cast(active_days_within_30 as float))as avg_active_days,
avg(cast(question_cnt as float))as avg_quesition_days 
from user_profile_2 group by university, gender order by university;

最后正确结果应是：

作业2：

现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

查询语句：

select university,
avg(cast(question_cnt as float))as avg_quesition_days,
avg(cast(answer_cnt as float))as avg_answer_days 
from user_profile_2 
group by university 
having avg(cast(question_cnt as float))<5 or avg(cast(answer_cnt as float))<20;

最后正确结果应是：

作业3:

现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列

查询语句：

select university,
avg(cast(question_cnt as float))as avg_quesition_days
from user_profile_2 
group by university order by avg(cast(question_cnt as float));

最后正确结果应是：

在homework库下执行：

执行以下建表语句：

CREATE TABLE question_practice_detail (

id int NOT NULL,

device_id int NOT NULL,

question_id int NOT NULL,

result varchar(32) NOT NULL

);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');

INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');

INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');

INSERT INTO question_practice_detail VALUES(4,6543,111,'right');

INSERT INTO question_practice_detail VALUES(5,2315,115,'right');

INSERT INTO question_practice_detail VALUES(6,2315,116,'right');

INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');

INSERT INTO question_practice_detail VALUES(8,5432,118,'wrong');

INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');

INSERT INTO question_practice_detail VALUES(10,2131,114,'right');

INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');

目前我们有两张表：user_profile_2和question_practice_detail

作业4：

现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select question_practice_detail.device_id,
question_id,
result 
from question_practice_detail
inner join user_profile_2 on user_profile_2.device_id=question_practice_detail.device_id 
where user_profile_2.university='浙江大学';

最后正确结果应是：