牛客网SQL 进阶篇刷题
发布时间
阅读量：
阅读量
牛客网SQL 进阶篇刷题(1-19)
- 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。
insert INTO exam_record(id,uid,exam_id,start_time,submit_time,score)
VALUES(DEFAULT,1001,9001,'2021-09-01 22:11:12','2021-09-01 22:11:12' + INTERVAL 50 minute,90),
(DEFAULT,1002,9002,'2021-09-04 07:01:02',NULL,NULL)- 现有一张试卷作答记录表exam_record，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。
INSERT INTO exam_record_before_2021
SELECT NULL,uid,exam_id,start_time,submit_time,score
FROM exam_record
WHERE YEAR(submit_time) < 2021- 现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info，不管该ID试卷是否存在，都要插入成功，请尝试插入它。
REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00')- 请把examination_info表中tag为PYTHON的tag字段全部修改为Python。
UPDATE examination_info
SET tag = 'Python'
WHERE tag = 'PYTHON'- 请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为’2099-01-01 00:00:00’，分数改为0.
UPDATE exam_record
SET submit_time = '2099-01-01 00:00:00', score = 0
WHERE start_time < '2021-09-01 00:00:00' and score is NULL- 请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录
DELETE FROM exam_record
WHERE score < 60 and TIMESTAMPDIFF (minute,start_time,submit_time) < 5- 请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。
DELETE FROM exam_record
WHERE start_time IN
(SELECT * FROM
(SELECT start_time from
exam_record where TIMESTAMPDIFF(minute,start_time,submit_time) < 5
OR submit_time IS NULL
ORDER BY start_time ASC
LIMIT 3
) AS new_table
)- 请删除exam_record表中所有记录，并重置自增主键。
TRUNCATE
TABLE
exam_record- 作为数据分析师，请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。
CREATE TABLE user_info_vip(
id int(11) not null primary key auto_increment comment '自增ID',
uid int(11) not null unique key comment '用户ID',
nick_name varchar(64) comment '昵称',
achievement int(11) default 0 comment '成就值',
level int(11) comment '用户等级',
job varchar(32) comment '职业方向',
register_time datetime default current_timestamp comment '注册时间'
)DEFAULT CHARSET = utf8- 请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，achievement的默认值设置为0。
ALTER TABLE user_info ADD school varchar(15) AFTER Level;
ALTER table user_info CHANGE job profession varchar(10);
ALTER TABLE user_info MODIFY achievement int(11) DEFAULT 0- 现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。
DROP TABLE IF EXISTS exam_record_2011;
DROP TABLE IF EXISTS exam_record_2012;
DROP TABLE IF EXISTS exam_record_2013;
DROP TABLE IF EXISTS exam_record_2014- 在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。
create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag)- 请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。
ALTER table examination_info
DROP index uniq_idx_exam_id;
ALTER table examination_info
DROP index full_idx_tag- 牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。
请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。
SELECT tag, difficulty,
ROUND((sum(score) - max(score) - min(score)) / (COUNT(score) - 2),1) AS clip_avg_score
FROM
examination_info a
JOIN exam_record b ON
a.exam_id = b.exam_id
WHERE a.difficulty = 'hard' and a.tag = 'SQL'- 有一个试卷作答记录表exam_record，请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。
SELECT
# 有开始时间视为有作答次数
count(start_time) AS total_pv,
# 有结束时间视为一次作答
COUNT(submit_time)AS complete_pv,
# 去重后的试卷和对应的分数不为空值是做完成一次作答
COUNT(DISTINCT exam_id AND score is NOT NULL)
AS complete_exam_cnt
FROM exam_record- 请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
SELECT min(a.score)
FROM exam_record a
JOIN examination_info b
ON a.exam_id = b.exam_id
WHERE b.tag = 'SQL'
and a.score >= ALL
(SELECT avg(score)
FROM exam_record a
JOIN examination_info b
ON a.exam_id = b.exam_id
WHERE b.tag = 'SQL')- 解释：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天），平均活跃天数1.5；2021年9月有4人活跃，共活跃了5天，平均活跃天数1.25，结果保留2位小数。
# 格式化日期
SELECT DATE_FORMAT(submit_time,'%Y%m') as month,
# 活跃人数---数出uid数量
# 月度活跃人数---输出格式化月份里uid数量
round((COUNT(distinct uid, DATE_FORMAT(submit_time,'%y%m%d'))) / COUNT(DISTINCT uid),2) AS avg_active_days,
COUNT(DISTINCT uid) AS mau
FROM exam_record
# 活跃---交卷时间不为空
WHERE submit_time is NOT NULL
and year(submit_time) = 2021
# 按照格式化的日期分组
group BY DATE_FORMAT(submit_time,'%y%m')- 请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况，
SELECT coalesce(DATE_FORMAT(submit_time,'%Y%m'),"2021汇总") AS submit_month,
# 每个月总刷题数量---提交时间的次数
COUNT(submit_time) as month_q_cnt,
# 日均刷题数---每天刷题数除当月的天数
# 当月天数---day(last(submit_time))
round(count(submit_time) / max(day(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where YEAR(submit_time) = 2021
group by DATE_FORMAT(submit_time,'%Y%m') with rollup- 请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。
SELECT uid, COUNT(incomplete) AS incomplete_cnt,
COUNT(complete) AS complete_cnt,
GROUP_CONCAT(distinct CONCAT_WS(':', date(start_time), tag) SEPARATOR ';') as detail
FROM
(SELECT uid,tag,start_time,
IF(submit_time is null,1,NULL) AS incomplete,
IF(submit_time is null,NULL,1) AS complete
FROM exam_record
LEFT JOIN
examination_info using (exam_id)
WHERE year(start_time) = 2021) AS new_table
GROUP BY uid
HAVING complete_cnt >= 1 and incomplete_cnt between 2 and 4
ORDER by incomplete_cnt DESC全部评论 (0)
还没有任何评论哟~