数据库系统原理与应用教程(069)—— MySQL 练习题:操作题 95-100(2)

img
img

网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。

需要这份系统化的资料的朋友,可以添加戳这里获取

一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!

96、聚合函数的使用(2)

数据表:exam_record 表(uid:用户ID,exam_id:试卷ID,start_time:开始作答时间,submit_time:交卷时间, score:得分),表中数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-06-02 19:01:012021-06-02 19:31:0184
4100190022021-09-05 19:01:012021-09-05 19:40:0189
5100190012021-09-02 12:01:01(NULL)(NULL)
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-02-02 19:01:012021-02-02 19:30:0187
8100290012021-05-05 18:01:012021-05-05 18:59:0290
9100390012021-02-06 12:01:01(NULL)(NULL)
10100390012021-09-07 10:01:012021-09-07 10:31:0188
11100490012021-09-06 12:01:01(NULL)(NULL)

【问题】请统计出总答题次数:total_pv、试卷已完成答题数:complete_pv,已完成的试卷数:complete_exam_cnt。查询结果如下:

total_pvcomplete_pvcomplete_exam_cnt
1172

表结构和数据如下:

/\*
drop table if exists exam\_record;
CREATE TABLE exam\_record (
 id int PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 uid int NOT NULL COMMENT '用户ID',
 exam\_id int NOT NULL COMMENT '试卷ID',
 start\_time datetime NOT NULL COMMENT '开始时间',
 submit\_time datetime COMMENT '提交时间',
 score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8\_general\_ci;

INSERT INTO exam\_record(uid,exam\_id,start\_time,submit\_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9001, '2021-09-06 12:01:01', null, null);
\*/

解答:

/\*
select count(\*) total\_pv,
 count(submit\_time) complete\_pv,
 count(distinct if(submit\_time is not null, exam\_id, null)) complete\_exam\_cnt
from exam\_record;
\*/
mysql> select count(\*) total_pv,
    ->        count(submit_time) complete_pv,
    ->        count(distinct if(submit_time is not null, exam_id, null)) complete_exam_cnt
    -> from exam_record;
+----------+-------------+-------------------+
| total_pv | complete_pv | complete_exam_cnt |
+----------+-------------+-------------------+
|       11 |           7 |                 2 |
+----------+-------------+-------------------+
1 row in set (0.00 sec)

97、聚合函数的使用(3)

数据表:exam_record(uid:用户ID,exam_id:试卷ID,start_time:开始作答时间,submit_time:交卷时间,score:得分),表中数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0189
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-02-02 19:01:012021-02-02 19:30:0187
6100290022021-05-05 18:01:012021-05-05 18:59:0290
7100390022021-02-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)

数据表:examination_info(exam_id:试卷ID,tag:试卷类别,difficulty:试卷难度,duration:考试时长, release_time:发布时间),表中数据如下:

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002SQLeasy602020-02-01 10:00:00
39003算法medium802020-08-02 10:00:00

【问题】请从试卷答题记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。查询结果如下:

min_score_over_avg
87

表结构和数据如下:

/\*
drop table if exists examination\_info;
CREATE TABLE examination\_info (
 id int PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 exam\_id int UNIQUE NOT NULL COMMENT '试卷ID',
 tag varchar(32) COMMENT '类别标签',
 difficulty varchar(8) COMMENT '难度',
 duration int NOT NULL COMMENT '时长',
 release\_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8\_general\_ci;

drop table if exists exam\_record;
CREATE TABLE exam\_record (
 id int PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 uid int NOT NULL COMMENT '用户ID',
 exam\_id int NOT NULL COMMENT '试卷ID',
 start\_time datetime NOT NULL COMMENT '开始时间',
 submit\_time datetime COMMENT '提交时间',
 score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8\_general\_ci;

INSERT INTO examination\_info(exam\_id,tag,difficulty,duration,release\_time) VALUES
 (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
 (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
 (9003, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam\_record(uid,exam\_id,start\_time,submit\_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9002, '2021-02-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null);
\*/

解答:

/\*
select min(score) min\_score\_over\_avg from exam\_record where exam\_id in
 (select exam\_id from examination\_info where tag = 'SQL') and score >=
 (select avg(score) min\_score\_over\_avg from exam\_record where exam\_id in
 (select exam\_id from examination\_info where tag = 'SQL'));
\*/
mysql> select min(score) min_score_over_avg from exam_record where exam_id in
    ->     (select exam_id from examination_info where tag = 'SQL') and score >=
    ->     (select avg(score) min_score_over_avg from exam_record where exam_id in
    ->         (select exam_id from examination_info where tag = 'SQL'));
+--------------------+
| min_score_over_avg |
+--------------------+
|                 87 |
+--------------------+
1 row in set (0.00 sec)

98、分组查询(1)

数据表:exam_record(uid:用户ID,exam_id:试卷ID,start_time:开始答题时间,submit_time:交卷时间,score:得分),表中数据如下:

iduidexam_idstart_timesubmit_timescore
1100190012021-07-02 09:01:012021-07-02 09:21:0180
2100290012021-09-05 19:01:012021-09-05 19:40:0181
3100290022021-09-02 12:01:01(NULL)(NULL)
4100290032021-09-01 12:01:01(NULL)(NULL)
5100290012021-07-02 19:01:012021-07-02 19:30:0182
6100290022021-07-05 18:01:012021-07-05 18:59:0290
7100390022021-07-06 12:01:01(NULL)(NULL)
8100390032021-09-07 10:01:012021-09-07 10:31:0186
9100490032021-09-06 12:01:01(NULL)(NULL)
10100290032021-09-01 12:01:012021-09-01 12:31:0181
11100590012021-09-01 12:01:012021-09-01 12:31:0188
12100690022021-09-02 12:11:012021-09-02 12:31:0189
13100790022020-09-02 12:11:012020-09-02 12:31:0189

【问题】请计算 2021 年每个月试卷答题用户的月度活跃天数 active_days 和月度活跃人数 mau。查询结果如下:

monthactive_daysmau
20210732
20210954

表结构和数据如下:

/\*
drop table if exists exam\_record;
CREATE TABLE exam\_record (
 id int PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 uid int NOT NULL COMMENT '用户ID',
 exam\_id int NOT NULL COMMENT '试卷ID',
 start\_time datetime NOT NULL COMMENT '开始时间',
 submit\_time datetime COMMENT '提交时间',
 score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8\_general\_ci;

INSERT INTO exam\_record(uid,exam\_id,start\_time,submit\_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89),
(1007, 9002, '2020-09-02 12:11:01', '2020-09-02 12:31:01', 89);
\*/

解答:

/\*
select concat(left(start\_time,4), substr(start\_time, 6, 2)) month,
 count(distinct uid) active\_days,
 count(distinct date(submit\_time)) mau
from exam\_record
where year(start\_time) = 2021
group by month;
\*/
mysql> select concat(left(start_time,4), substr(start_time, 6, 2)) month,
    ->        count(distinct uid) active_days,
    ->        count(distinct date(submit_time)) mau
    -> from exam_record
    -> where year(start_time) = 2021
    -> group by month;
+--------+-------------+-----+
| month  | active_days | mau |
+--------+-------------+-----+
| 202107 |           3 |   2 |
| 202109 |           5 |   4 |
+--------+-------------+-----+
2 rows in set (0.00 sec)

99、分组查询(2)

数据表:practice_record,表中数据如下:

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380022021-08-01 19:38:0180

【问题】请统计出 2021 年每个月用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt(按月份升序排序)。查询结果如下:

submit_monthmonth_q_cntavg_day_q_cnt
20210820.065
20210930.100

表结构和数据如下:

/\*
drop table if exists practice\_record;
CREATE TABLE practice\_record (
 id int PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 uid int NOT NULL COMMENT '用户ID',
 question\_id int NOT NULL COMMENT '题目ID',
 submit\_time datetime COMMENT '提交时间',
 score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8\_general\_ci;

INSERT INTO practice\_record(uid,question\_id,submit\_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-08-01 19:38:01', 80);
\*/

解答:

/\*
select submit\_month,
 count(\*) month\_q\_cnt,
 round(count(\*)/day(last\_day(concat(submit\_month,'01'))),3) avg\_day\_q\_cnt
from
(select \*,concat(left(submit\_time,4),substr(submit\_time,6,2)) submit\_month
from practice\_record
where year(submit\_time) = 2021) a
group by submit\_month
order by submit\_month;
\*/
mysql> select submit_month,
    ->        count(\*) month_q_cnt,
    ->        round(count(\*)/day(last_day(concat(submit_month,'01'))),3) avg_day_q_cnt
    -> from
    -> (select \*,concat(left(submit_time,4),substr(submit_time,6,2)) submit_month
    -> from practice_record
    -> where year(submit_time) = 2021) a
    -> group by submit_month
    -> order by submit_month;
+--------------+-------------+---------------+
| submit_month | month_q_cnt | avg_day_q_cnt |
+--------------+-------------+---------------+
| 202108       |           2 |         0.065 |
| 202109       |           3 |         0.100 |
+--------------+-------------+---------------+
2 rows in set (0.00 sec)

100、分组查询(3)

img
img

网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。

需要这份系统化的资料的朋友,可以添加戳这里获取

一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!

------------±--------------+
2 rows in set (0.00 sec)


100、分组查询(3)




[外链图片转存中...(img-xp8uWg7d-1715808925534)]
[外链图片转存中...(img-Df2sh7FK-1715808925534)]

**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友,可以添加戳这里获取](https://bbs.csdn.net/topics/618668825)**


**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值