SQL server 练习题7

最新推荐文章于 2024-09-15 08:03:51 发布

W6-zzz

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量744

点赞数 8

文章标签：数据库 sql sqlserver

本文链接：https://blog.csdn.net/m0_67688142/article/details/140414402

版权

课后作业

作业 1：分群并计算群体人数

建表语句
CREATE TABLE customers_info (
customer_id int NOT NULL, -- 客户 id
gender varchar(10) NOT NULL, -- 客户性别
city varchar(32) NOT NULL, -- 客户所在城市
country varchar(32) NOT NULL, -- 客户所在国家
age int, -- 客户年龄
latest_place_order_date date ); -- 客户最近购买日期

数据插入语句

INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23,'2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66,'2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null,'2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);

INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19,'2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
查询语句：

按照国家分和按照年龄段来分
select country ,count(country) as 属于该国的人数 
from customers_info group by country;
select  case when age>=18 and age<30 then '青年人' 
  when age >=30 and age<50 then '壮年人'
else '老年人' end as 年龄段,
count(case when age>=18 and age<30 then '青年人' 
  when age >=30 and age<50 then '壮年人'
else '老年人' end )as 人数
from customers_info group by 
case when age>=18 and age<30 then '青年人' 
  when age >=30 and age<50 then '壮年人'
else '老年人' end;
结果应是：

作业 2：输出提交次数大于 3 次的用户 ID 且倒序排列

建表语句
create table done_questions_record (
user_id int not null,
question_id int not null ,
question_type varchar(24) not null ,
done_time datetime not null,
result_info int not null
);
数据插入语句
insert into done_questions_record values (101,1,'python','2022-01-01 12:30:21',0);
insert into done_questions_record values (101,1,'python','2022-01-01 12:30:22',1);
insert into done_questions_record values (102,1,'python','2022-01-01 14:30:23',1);
insert into done_questions_record values (101,2,'sql','2022-01-01 16:30:24',1);
insert into done_questions_record values (102,2,'sql','2022-01-02 08:30:25',1);
insert into done_questions_record values (103,1,'python','2022-01-03 10:30:26',0);
insert into done_questions_record values (104,1,'python','2022-01-03 11:30:27',0);
insert into done_questions_record values (103,2,'sql','2022-01-03 19:30:28',1);
insert into done_questions_record values (105,1,'python','2022-01-03 12:30:29',0);
insert into done_questions_record values (103,3,'java','2022-01-03 12:30:30',0);
insert into done_questions_record values (105,3,'java','2022-01-03 12:30:31',1);
insert into done_questions_record values (105,4,'js','2022-01-03 12:30:32',0);
insert into done_questions_record values (104,5,'c++','2022-01-03 12:30:33',1);
insert into done_questions_record values (106,5,'c++','2022-01-03 12:30:34',0);
insert into done_questions_record values (101,5,'c++','2022-01-03 12:30:35',1);
insert into done_questions_record values (106,5,'c++','2022-01-03 12:30:36',1);
insert into done_questions_record values (102,5,'c++','2022-01-03 12:30:37',1);
insert into done_questions_record values (103,4,'js','2022-01-03 12:30:38',1);
insert into done_questions_record values (105,3,'java','2022-01-03 12:30:39',1);
insert into done_questions_record values (103,2,'sql','2022-01-03 12:30:40',0);
insert into done_questions_record values (105,1,'python','2022-01-03 12:30:41',0);
insert into done_questions_record values (105,1,'python','2022-01-03 12:30:42',1);
insert into done_questions_record values (104,2,'sql','2022-01-03 12:30:43',1);
查询语句：
select user_id,count(question_id) 提交次数 from done_questions_record  
 group by user_id 
 having count(question_id)>3 
 order by user_id desc;
结果应是：

作业 3：输出提交且通过次数大于 2 的用户 ID 且升序排列

表数据用作业 2 的即可
查询语句：
select user_id,count(question_id) 提交通过次数 from done_questions_record  
where result_info =1
 group by user_id 
 having count(question_id)>2 
 order by user_id ;
结果应是：

作业 4：计算题目通过率

计算逻辑：question_pass_rate=（通过的题目数/总题目数）
比如用户 101，通过了三个题目(result_info=1 表示此题目通过），总题目数是三个
(question_id=1,2,5)，所以 question_pass_rate=3/3
表数据用作业 2 的即可
查询语句：
select tb1.user_id,format(cast(tb2.通过数 as float)/tb1.提交数,'p') as question_passed_rate
from (select user_id,count(distinct question_id) as 提交数 
from done_questions_record  group by user_id)as tb1 
join 
(select user_id,count(distinct case when result_info = 1 then question_type end) as 通过数 
from done_questions_record where result_info=1 group by user_id,result_info) as tb2 
on tb1.user_id=tb2.user_id;
结果应是：

作业 5：计算题目正确率

正确率 pass_rate=（通过的次数/总答题次数）=3/4
表数据用作业二的即可
查询语句：
select user_id,
format(sum(cast(result_info as float))/count(question_id),'p')pass_rate
 from done_questions_record 
 group by user_id;
结果应是：

作业 6：每题目平均提交次数

question_per_cnt=(总提交次数/总题目数)4/3，比如：
user_id question_id result_info
101 1 0
101 1 1
101 2 1
101 5 1
表数据用作业 2 的即可
查询语句：
select tb1.user_id,tb1.总提交次数/cast(tb2.总题目数 as float) as question_per_cnt
from (select user_id,count(question_id) as 总提交次数 from done_questions_record  group by user_id) as tb1 
join (select user_id,count(distinct question_id) as 总题目数 from done_questions_record group by user_id) as tb2 
on tb1.user_id=tb2.user_id;
查询结果：