• PTA—sql补题（4）

    目录

    找出每个学校GPA最低的同学

    表结构:

    表样例

    输出样例:

    查找当前薪水详情以及部门编号dept_no

    描述

    作答试卷得分大于过80的人的用户等级分布

    描述

    试卷发布当天作答人数和平均分

    表结构:

    表样例

    输出样例:

     筛选限定昵称成就值活跃日期的用户

    描述

    找出每个学校GPA最低的同学

    现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

    提示：请使用SELECT语句作答。

    表结构:

    请在这里写定义表结构的SQL语句。例如：

    1.  
    2. drop table if exists user_profile;
    3. CREATE TABLE `user_profile` (
    4. `id` int NOT NULL,
    5. `device_id` int NOT NULL,
    6. `gender` varchar(14) NOT NULL,
    7. `age` int ,
    8. `university` varchar(32) NOT NULL,
    9. `gpa` float,
    10. `active_days_within_30` int ,
    11. `question_cnt` int ,
    12. `answer_cnt` int 
    13. );
    14. INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
    15. INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
    16. INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
    17. INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
    18. INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
    19. INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
    20. INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

    表样例

    请在这里给出上述表结构对应的表样例。例如

    user_profile表：

     

    输出样例:

    请在这里给出输出样例。例如：

    6543|北京大学|3.200

    4321|复旦大学|3.600

    2131|山东大学|3.300

    2315|浙江大学|3.600

    • 限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；
    • 用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。注意这样如果最低gpa对应多个同学，都会输出，题目没有明确此种情况，心理明白就行。
    1. SELECT device_id,university,gpa
    2. FROM user_profile
    3. WHERE (university,gpa) IN (SELECT university, min(gpa)
    4.                 FROM user_profile
    5.                 GROUP BY university)
    6. ORDER BY university

    查找当前薪水详情以及部门编号dept_no

    描述

    有一个全部员工的薪水表salaries简况如下:

    emp_nosalaryfrom_dateto_date
    10001889582002-06-229999-01-01
    10002725272001-08-029999-01-01
    10003433112001-12-019999-01-01

    有一个各个部门的领导表dept_manager简况如下:

    dept_noemp_noto_date
    d001100019999-01-01
    d002100039999-01-01

    请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

    emp_nosalaryfrom_dateto_datedept_no
    10001889582002-06-229999-01-01d001
    10003433112001-12-019999-01-01d002
    1. select d.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
    2. from dept_manager d left join salaries s on d.emp_no=s.emp_no
    3. order by s.emp_no

    作答试卷得分大于过80的人的用户等级分布

    描述

    现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

    iduidnick_nameachievementleveljobregister_time
    11001牛客1号31007算法2020-01-01 10:00:00
    21002牛客2号21006算法2020-01-01 10:00:00
    31003牛客3号15005算法2020-01-01 10:00:00
    41004牛客4号11004算法2020-01-01 10:00:00
    51005牛客5号16006C++2020-01-01 10:00:00
    61006牛客6号30006C++2020-01-01 10:00:00

    试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

    idexam_idtagdifficultydurationrelease_time
    19001SQLhard602021-09-01 06:00:00
    29002C++easy602021-09-01 06:00:00
    39003算法medium802021-09-01 10:00:00

    试卷作答信息表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

    iduidexam_idstart_timesubmit_timescore
    1100190012021-09-01 09:01:012021-09-01 09:41:0179
    2100290032021-09-01 12:01:012021-09-01 12:21:0160
    3100290022021-09-01 12:01:012021-09-01 12:31:0170
    4100290012021-09-01 19:01:012021-09-01 19:40:0180
    5100290032021-08-01 12:01:012021-08-01 12:21:0160
    6100290022021-09-01 12:01:012021-09-01 12:31:0170
    7100290012021-09-01 19:01:012021-09-01 19:40:0185
    8100290022021-09-01 12:01:01(NULL)(NULL)
    9100390032021-09-07 10:01:012021-09-07 10:31:0186
    10100390032021-09-08 12:01:012021-09-08 12:11:0140
    11100390012021-09-01 13:01:012021-09-01 13:41:0181
    12100390022021-09-01 14:01:01(NULL)(NULL)
    13100390032021-09-08 15:01:01(NULL)(NULL)
    14100590012021-09-01 12:01:012021-09-01 12:31:0190
    15100590022021-09-01 12:01:012021-09-01 12:31:0188
    16100690022021-09-01 12:11:012021-09-01 12:31:0189

    统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序（保证数量都不同）。示例数据结果输出如下：

    levellevel_cnt
    62
    51

    解释：9001为SQL类试卷，作答该试卷大于80分的人有1002、1003、1005共3人，6级两人，5级一人。

    思路：

    • 按等级分组，计算每个等级的符合条件的人数。知识点：group by
    • 只挑选类别为SQL、且得分大于80的不同的用户进行统计人数，相同的用户只统计一次：
    • 按照人数的降序，相同情况下等级降序输出。order by level_cnt desc, level desc
    1. select level,count(*) level_cnt
    2. from user_info
    3. where uid in(select uid 
    4.             from exam_record er join examination_info ei on er.exam_id=ei.exam_id
    5.             where score>80
    6.             and tag='SQL')
    7. group by level
    8. order by level_cnt desc

    试卷发布当天作答人数和平均分

    请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序。

    提示：请使用SELECT语句作答。

    表结构:

    1. drop table if exists examination_info,user_info,exam_record;
    2. CREATE TABLE examination_info (
    3.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    4.     exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    5.     tag varchar(32) COMMENT '类别标签',
    6.     difficulty varchar(8) COMMENT '难度',
    7.     duration int NOT NULL COMMENT '时长',
    8.     release_time datetime COMMENT '发布时间'
    9. )CHARACTER SET utf8 COLLATE utf8_general_ci;
    10.  
    11. CREATE TABLE user_info (
    12.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    13.     uid int UNIQUE NOT NULL COMMENT '用户ID',
    14.     `nick_name` varchar(64) COMMENT '昵称',
    15.     achievement int COMMENT '成就值',
    16.     level int COMMENT '用户等级',
    17.     job varchar(32) COMMENT '职业方向',
    18.     register_time datetime COMMENT '注册时间'
    19. )CHARACTER SET utf8 COLLATE utf8_general_ci;
    20.  
    21. CREATE TABLE exam_record (
    22.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    23.     uid int NOT NULL COMMENT '用户ID',
    24.     exam_id int NOT NULL COMMENT '试卷ID',
    25.     start_time datetime NOT NULL COMMENT '开始时间',
    26.     submit_time datetime COMMENT '提交时间',
    27.     score tinyint COMMENT '得分'
    28. )CHARACTER SET utf8 COLLATE utf8_general_ci;

    表样例

    user_info表：

    iduidnick_nameachievementleveljobregister_time
    11001牛客1号31007算法2020-01-01 10:00:00
    21002牛客2号21006算法2020-01-01 10:00:00
    31003牛客3号15005算法2020-01-01 10:00:00
    41004牛客4号11004算法2020-01-01 10:00:00
    51005牛客5号16006C++2020-01-01 10:00:00
    61006牛客6号30006C++2020-01-01 10:00:00

    输出样例:

    exam_iduvavg_score
    9001381.3
    1. select er.exam_id, count(distinct er.uid) uv,round(avg(er.score),1) avg_score
    2. from examination_info ei,exam_record er
    3. where ei.exam_id=er.exam_id
    4. and ei.tag='SQL'
    5. and date_format(ei.release_time,'%Y%m%d')=date_format(er.start_time,'%Y%m%d')
    6. and er.uid in(select uid
    7.              from user_info
    8.              where level>5)
    9. group by er.exam_id
    10. order by uv desc,avg_score asc

     筛选限定昵称成就值活跃日期的用户

    描述

    现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

    iduidnick_nameachievementleveljobregister_time
    11001牛客1号10002算法2020-01-01 10:00:00
    21002牛客2号12003算法2020-01-01 10:00:00
    31003进击的3号22005算法2020-01-01 10:00:00
    41004牛客4号25006算法2020-01-01 10:00:00
    51005牛客5号30007C++2020-01-01 10:00:00

    试卷作答记录表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:5980
    3100190022021-02-02 19:01:012021-02-02 19:30:0187
    2100190012021-05-02 10:01:01(NULL)(NULL)
    4100190012021-06-02 19:01:012021-06-02 19:32:0020
    6100190022021-09-01 12:01:01(NULL)(NULL)
    5100190022021-09-05 19:01:012021-09-05 19:40:0189
    11100290012020-01-01 12:01:012020-01-01 12:31:0181
    12100290022020-02-01 12:01:012020-02-01 12:31:0182
    13100290022020-02-02 12:11:012020-02-02 12:31:0183
    7100290022021-05-05 18:01:012021-05-05 18:59:0290
    16100290012021-09-06 12:01:012021-09-06 12:21:0180
    17100290012021-09-06 12:01:01(NULL)(NULL)
    18100290012021-09-07 12:01:01(NULL)(NULL)
    8100390032021-02-06 12:01:01(NULL)(NULL)
    9100390012021-09-07 10:01:012021-09-07 10:31:0189
    10100490022021-08-06 12:01:01(NULL)(NULL)
    14100590012021-02-01 11:01:012021-02-01 11:31:0184
    15100690012021-02-01 11:01:012021-02-01 11:31:0184

    题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

    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-09-01 19:38:0180

    请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。

    由示例数据结果输出如下：

    uidnick_nameachievement
    1002牛客2号1200

    解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；

    1002最近一次试卷区活跃为2021年9月，最近一次题目区活跃为2021年9月；1004最近一次试卷区活跃为2021年8月，题目区未活跃。

    因此最终满足条件的只有1002。

     思路：
    • 在practice_record，exam_record中筛选出月份是2021年9月
      • where date_format(submit_time, '%Y%m') = '202109' 知识点：date_format
    • 筛选出成就值在1200到2500之间，uid在上述两个任意一个中，且nick_name能匹配牛客_号。where nick_name like '牛客_号' 知识点：like、
    1. select uid,nick_name,achievement	
    2. from user_info
    3. where nick_name like '牛客_号'
    4. and achievement between 1200 and 2500
    5. and uid in (select uid
    6.     from practice_record
    7.     where date_format(submit_time, '%Y%m')='202109'
    8. union all
    9.      select uid
    10.         from exam_record
    11.         where date_format(submit_time, '%Y%m') = '202109')

  • 相关阅读:
    C++~结构体/类的对齐规则详解，为什么要对齐
    勒索病毒LockBit2.0 数据库（mysql与sqlsever）解锁恢复思路分享
    destoon 调用第三方api接口
    快速使用vscode写python
    [附源码]SSM计算机毕业设计社区医院管理系统JAVA
    Flutter CustomScrollView 的使用 及 常用的Sliver系列组件
    鹅厂练习 13 年 Coding 后，我悟了
    net-java-php-python-学生入学信息管理系统计算机毕业设计程序
    概率论中的几个重要悖论问题
    如何修改域名DNS服务器？修改DNS服务器常见问题汇总
  • 原文地址：https://blog.csdn.net/qq_62799214/article/details/127846657