• 【SQL刷题】Day6----SQL综合专项练习

    博主昵称：跳楼梯企鹅
    博主主页面链接：博主主页传送门

    博主专栏页面连接：专栏传送门--网路安全技术
    创作初心：本博客的初心为与技术朋友们相互交流，每个人的技术都存在短板，博主也是一样，虚心求教，希望各位技术友给予指导。
    博主座右铭：发现光，追随光，成为光，散发光;
    博主研究方向：渗透测试、机器学习 ;
    博主寄语：感谢各位技术友的支持，您的支持就是我前进的动力 ;

    学习网站跳转链接：牛客刷题网

    一、综合专项 

    这里博主推荐的刷题网站为牛客网，可以点击右边链接牛客网------求职刷题
     

     通过前几次的介绍，我们已经对MySQL有了初步了解，那么我们今天介绍一款综合练习吧。

    二、刷题

    1.题目一

    （1）题目

    题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

    （2）难易程度

    难易程度：困难

    （3）示例

    1. 输入：
    2.  
    3. drop table if exists `user_profile`;
    4. drop table if  exists `question_practice_detail`;
    5. drop table if  exists `question_detail`;
    6. CREATE TABLE `user_profile` (
    7. `id` int NOT NULL,
    8. `device_id` int NOT NULL,
    9. `gender` varchar(14) NOT NULL,
    10. `age` int ,
    11. `university` varchar(32) NOT NULL,
    12. `gpa` float,
    13. `active_days_within_30` int ,
    14. `question_cnt` int ,
    15. `answer_cnt` int 
    16. );
    17. CREATE TABLE `question_practice_detail` (
    18. `id` int NOT NULL,
    19. `device_id` int NOT NULL,
    20. `question_id`int NOT NULL,
    21. `result` varchar(32) NOT NULL,
    22. `date` date NOT NULL
    23. );
    24. CREATE TABLE `question_detail` (
    25. `id` int NOT NULL,
    26. `question_id`int NOT NULL,
    27. `difficult_level` varchar(32) NOT NULL
    28. );
    29.  
    30. INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
    31. INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
    32. INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
    33. INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
    34. INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
    35. INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
    36. INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
    37. INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
    38. INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
    39. INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
    40. INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
    41. INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
    42. INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
    43. INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
    44. INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
    45. INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
    46. INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
    47. INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
    48. INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
    49. INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
    50. INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
    51. INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
    52. INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
    53. INSERT INTO question_detail VALUES(1,111,'hard');
    54. INSERT INTO question_detail VALUES(2,112,'medium');
    55. INSERT INTO question_detail VALUES(3,113,'easy');
    56. INSERT INTO question_detail VALUES(4,115,'easy');
    57. INSERT INTO question_detail VALUES(5,116,'medium');
    58. INSERT INTO question_detail VALUES(6,117,'easy');
    59.  
    60. 输出：
    61.  
    62. 3214|复旦大学|3|0
    63. 4321|复旦大学|0|0

    （4）代码

    1. SELECT u.device_id, university,
    2. sum(if(result is not null,1,0)) as question_cnt,
    3. sum(if(result='right',1,0)) as right_question_cnt
    4. FROM user_profile as u LEFT JOIN question_practice_detail as q
    5. ON u.device_id=q.device_id
    6. WHERE university='复旦大学' and (month(date)=8 or date is null)
    7. GROUP BY u.device_id;

    （5）运行结果

    2.题目二

    （1）题目

    题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

    （2）难易程度

    难易程度：困难

    （3）示例

    1. 输入：
    2.  
    3. drop table if exists `user_profile`;
    4. drop table if  exists `question_practice_detail`;
    5. drop table if  exists `question_detail`;
    6. CREATE TABLE `user_profile` (
    7. `id` int NOT NULL,
    8. `device_id` int NOT NULL,
    9. `gender` varchar(14) NOT NULL,
    10. `age` int ,
    11. `university` varchar(32) NOT NULL,
    12. `gpa` float,
    13. `active_days_within_30` int ,
    14. `question_cnt` int ,
    15. `answer_cnt` int 
    16. );
    17. CREATE TABLE `question_practice_detail` (
    18. `id` int NOT NULL,
    19. `device_id` int NOT NULL,
    20. `question_id`int NOT NULL,
    21. `result` varchar(32) NOT NULL,
    22. `date` date NOT NULL
    23. );
    24. CREATE TABLE `question_detail` (
    25. `id` int NOT NULL,
    26. `question_id`int NOT NULL,
    27. `difficult_level` varchar(32) NOT NULL
    28. );
    29.  
    30. INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
    31. INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
    32. INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
    33. INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
    34. INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
    35. INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
    36. INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
    37. INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
    38. INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
    39. INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
    40. INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
    41. INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
    42. INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
    43. INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
    44. INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
    45. INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
    46. INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
    47. INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
    48. INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
    49. INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
    50. INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
    51. INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
    52. INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
    53. INSERT INTO question_detail VALUES(1,111,'hard');
    54. INSERT INTO question_detail VALUES(2,112,'medium');
    55. INSERT INTO question_detail VALUES(3,113,'easy');
    56. INSERT INTO question_detail VALUES(4,115,'easy');
    57. INSERT INTO question_detail VALUES(5,116,'medium');
    58. INSERT INTO question_detail VALUES(6,117,'easy');
    59.  
    60. 输出：
    61.  
    62. easy|0.5000
    63. medium|1.0000

    （4）代码

    1. SELECT difficult_level,
    2. AVG(IF(result='right',1,0)) AS correct_rate
    3. FROM user_profile u, question_practice_detail qpd, question_detail qd
    4. WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id
    5. AND university='浙江大学'
    6. GROUP BY difficult_level
    7. ORDER BY correct_rate;

    （5）运行结果

    3.题目三

    （1）题目

    题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

    （2）难易程度

    难易程度：简单

    （3）示例

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

    （4）代码

    1. SELECT device_id, age 
    2. FROM user_profile 
    3. ORDER BY age;

    （5）运行结果

    三、小结

    MySQL刷题第六天完成，继续加油！

    快来和博主一起刷题学习吧刷题传送门

  • 相关阅读:
    注意力机制 -自注意力和位置编码
    《嵌入式 – GD32开发实战指南》第16章 RTC
    18亿欧元大动作，法国瞄准实现量子飞跃
    ubuntu挂载共享目录的方法
    基于PHP+MySQL汽车租赁管理系统的设计与实现
    (const char *format, ...) 可变参数在文本日志中的巧妙使用
    【部署之后的错误排查】远程不能访问
    MaskRCNN（matterport）模型搭建与实验
    元宇宙的核心技术之我见
    STM32MP157按键中断实验
  • 原文地址：https://blog.csdn.net/weixin_50481708/article/details/126415602