SQL题集(一)

已于 2024-09-29 11:27:44 修改
阅读量1.1k 收藏 2
点赞数 3
分类专栏： # SQL题集 Mysql 文章标签： sql mysql
于 2022-03-20 18:21:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hudas/article/details/123617023
版权

浙江大学 8月答题 正确率 平均留存率 用户行为
关键词由CSDN通过智能技术生成

1.检索产品名称和描述(一)

导入数据

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

Products表

问题:从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序 

-- 第一种解法
SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc NOT LIKE'%toy%'
ORDER BY prod_name ASC;

-- 第二种解法
SELECT prod_name, prod_desc
FROM Products
WHERE NOT (prod_desc REGEXP 'toy');

结果展示

 2.检索产品名称和描述(二)

导入数据

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego carrots toy');

Products表

问题:从 Products 表中检索产品名称(prod_name)和描述(prod_desc)，仅返回描述中同时出现 toy 和 carrots 的产品

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%carrots%'
AND prod_desc LIKE '%toy%';

结果展示:

3.顾客登录名

导入数据

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名',
	cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',
	cust_city VARCHAR(255) NOT NULL COMMENT '客户城市'
);
INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

Customers表

问题: 返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login)，其中登录名全部为大写字母，并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成

例如，登录名是 ANOAK（Andy Li，居住在 Oak Park） 

-- 第一种解法
SELECT cust_id, cust_name, 
       CONCAT(UPPER(SUBSTRING(cust_contact,1,2)),UPPER(SUBSTRING(cust_city,1,3)))AS user_login
FROM Customers;

--第二种解法(实际上就是第一种解法,函数使用顺序不同)
SELECT 
    cust_id, 
    cust_name, 
    UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) AS user_login
FROM Customers;

--第三种解法
SELECT cust_id,cust_name,
       UPPER(CONCAT(LEFT(cust_name,2),LEFT(cust_city,3))) AS user_login
FROM Customers;

结果展示

4.计算总和

导入数据

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);

OrderItems表

OrderItems表代表订单信息 

order_num: 订单号

item_price: 商品售出价格

quantity: 商品数量

问题:根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序(提示:总价 = item_price 乘以 quantity)

SELECT order_num,SUM(item_price * quantity)AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num ASC;

结果展示

5.从 Products 表中检索所有的产品名称以及对应的销售总数

导入数据

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',1100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5);

Products表 

prod_name: 产品名称

prod_id: 产品id

OrderItems表

OrderItems代表订单商品表  

prod_id: 产品id

quantity: 售出数量

问题:从 Products 表中检索所有的产品名称(prod_name)，以及名为 quant_sold 的计算列，其中包含所售产品的总数(在OrderItems 表上使用子查询和 SUM(quantity)检索）

-- 第一种解法
SELECT a.prod_name, SUM(b.quantity) AS quant_sold
FROM Products AS a, OrderItems AS b
WHERE a.prod_id = b.prod_id
GROUP BY a.prod_name;

-- 第二种解法
SELECT prod_name,
       (SELECT SUM(quantity)
        FROM OrderItems
        WHERE OrderItems.prod_id = Products.prod_id) AS quant_sold
FROM Products;

-- 第三种解法
SELECT prod_name,
       quant_sold
FROM Products, (SELECT prod_id, SUM(quantity) AS quant_sold
                FROM OrderItems
                GROUP BY prod_id) AS OI
WHERE Products.prod_id = OI.prod_id;

-- 第四种解法
SELECT prod_name, SUM(quantity)
FROM Products LEFT JOIN OrderItems
ON(Products.prod_id = OrderItems.prod_id)
GROUP BY prod_name;

结果展示

6.计算25岁以上和以下的用户数量 

导入数据

drop table if exists `user_profile`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

user_profile表

问题:现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量 

-- 第一种解法
SELECT IF(age < 25 OR age IS NULL,'25岁以下','25岁及以上')AS age_cut,
       COUNT(id) AS number
FROM user_profile
GROUP BY age_cut;

-- 第二种解法
SELECT '25岁以下' AS avg_cut,COUNT(device_id) AS number
FROM user_profile
WHERE age<25 OR age IS null
UNION ALL
SELECT '25岁及以上',COUNT(device_id)
FROM user_profile
WHERE age>=25;

结果展示

7. 查询结果限制返回行数

导入数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

user_profile表 

问题:现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果 

-- LIMIT n：从第0+1(m=0)条开始，取n条数据，是LIMIT 0,n的缩写
SELECT device_id
FROM user_profile
LIMIT 2;

-- LIMIT m,n:从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 0,2;

-- LIMIT n OFFSET m：从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 2 OFFSET 0;

结果展示

8.用where过滤空值练习 

导入数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

user_profile表

问题:现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息

解题思路

过滤空值的三种方法：

(1) Where 列名 is not null

(2) Where 列名 != 'null'

(3) Where 列名 <> 'null'

-- 第一种解法
SELECT device_id, gender, age, university
FROM user_profile
WHERE age IS NOT NULL;

-- 第二种解法
SELECT device_id, gender, age, university
FROM user_profile
WHERE age != 'NULL';

-- 第三种解法
SELECT device_id, gender, age, university
FROM user_profile
WHERE age <> 'NULL';

结果展示

9.查找GPA最高值

导入数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2234,'male',21,'北京大学',3.2);
INSERT INTO user_profile VALUES(2,2235,'male',null,'复旦大学',3.8);
INSERT INTO user_profile VALUES(3,2236,'female',20,'复旦大学',3.5);
INSERT INTO user_profile VALUES(4,2237,'female',23,'浙江大学',3.3);
INSERT INTO user_profile VALUES(5,2238,'male',25,'复旦大学',3.1);
INSERT INTO user_profile VALUES(6,2239,'male',25,'北京大学',3.6);
INSERT INTO user_profile VALUES(7,2240,'male',null,'清华大学',3.3);
INSERT INTO user_profile VALUES(8,2241,'female',null,'北京大学',3.7);

user_profile表 

问题:运营想要知道复旦大学的学生gpa最高值是多少，请你取出相应数据，结果保留到小数点后面1位(1位之后的四舍五入) 

-- 第一种解法
-- 通过gpa倒序，然后取第一条
SELECT ROUND(gpa,1) AS gpa
FROM user_profile
WHERE university = '复旦大学'
ORDER BY gpa DESC
LIMIT 1;

-- 第二种解法
-- 使用聚合函数取最大值
SELECT ROUND(MAX(gpa),1)AS gpa
FROM user_profile
WHERE university = '复旦大学';

 结果展示

10.计算男生人数以及平均GPA

导入数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

user_profile表 

问题:现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据，其中返回的结果avg_gpa保留到小数点后面1位(1位之后的四舍五入) 

-- 第一种解法
SELECT COUNT(gender) AS male_num, ROUND(AVG(gpa),1) AS avg_gpa
FROM user_profile
WHERE gender = 'male';

-- 第二种解法
SELECT COUNT(gender)AS male_num, ROUND(AVG(gpa),1)AS avg_gpa 
FROM user_profile
GROUP BY gender 
HAVING gender = 'male';

结果展示

11.分组计算

导入数据

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

user_profile表(用户信息表)

active_days_within_30: 30天内活跃天数字段

question_cnt: 发帖数量字段

answer_cnt: 回答数量字段

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

问题:现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量(avg_active_day，avg_question_cnt两列保留1位小数，1位小数之后四舍五入)

SELECT gender, university, COUNT(device_id) AS user_num, 
       ROUND(AVG(active_days_within_30),1) AS avg_active_day, 
       ROUND(AVG(question_cnt),1) AS avg_question_cnt
FROM user_profile
GROUP BY gender,university;

结果展示

12.分组过滤

导入数据

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

user_profile表(用户信息表)

active_days_within_30: 30天内活跃天数字段

question_cnt: 发帖数量字段

answer_cnt: 回答数量字段

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

问题: 现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校(avg_question_cnt，avg_answer_cnt两列保留3位小数，3位小数之后四舍五入)

SELECT university, 
       ROUND(AVG(question_cnt),3) AS avg_question_cnt, 
       ROUND(AVG(answer_cnt),3) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt < 5 OR avg_answer_cnt <20;

结果展示

13.分组排序 

导入数据

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

user_profile表(用户信息表)

active_days_within_30: 30天内活跃天数字段

question_cnt: 发帖数量字段

answer_cnt: 回答数量字段

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

问题: 现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，取出相应数据

SELECT university, AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt ASC;

结果展示

14.查找山东大学或者性别为男生的信息

导入数据

drop table if exists `user_profile`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

user_profile表 

问题:现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重 

SELECT device_id, gender, age, gpa
FROM user_profile
WHERE university = '山东大学' 
UNION ALL
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE gender = 'male';

结果展示

15.查看不同年龄段的用户明细 

导入数据

drop table if exists `user_profile`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

user_profile表 

问题:现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据(注：若年龄为空请返回其他) 

SELECT device_id, gender, 
       (CASE WHEN age >= 25 THEN '25岁及以上'
             WHEN age < 25 AND age >= 20 THEN '20-24岁'
             WHEN age < 20 THEN '20岁以下'
             ELSE '其他' 
        END)AS age_cut
FROM user_profile;

结果展示

16.计算用户8月每天的练题数量 

导入数据

drop table if  exists `question_practice_detail`;

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

question_practice_detail表

问题:运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据 

-- 第一种解法
SELECT DAY(date)AS day, COUNT(id)AS question_cnt
FROM question_practice_detail
WHERE YEAR(date) = 2021 AND MONTH(date) = 08 
GROUP BY day;

-- 第二种解法
SELECT DAY(date)AS day, COUNT(id)AS question_cnt
FROM question_practice_detail
WHERE SUBSTRING(date,1,7) = '2021-08' 
GROUP BY day;

-- 第三种解法
SELECT DAY(date)AS day, COUNT(id)AS question_cnt
FROM question_practice_detail
WHERE date LIKE'2021-08%'
GROUP BY day;

结果展示

17.统计每种性别的人数 

导入数据

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(2,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(3,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(4,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(5,2131,'168cm,45kg,22,female','http:/url/sysdney');

user_submit表 

问题:现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如上所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果 

SELECT SUBSTRING_INDEX(profile,',',-1)AS gender, 
       COUNT(device_id)AS number
FROM user_submit
GROUP BY gender;

结果展示

18.提取博客URL中的用户名 

导入数据

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(2,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(3,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(4,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(5,2131,'168cm,45kg,22,female','http:/url/sysdney');

user_submit表 

问题:对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据 

-- 第一种解法
SELECT device_id, 
       SUBSTRING_INDEX(blog_url,'/',-1)AS user_name
FROM user_submit;

-- 第二种解法
SELECT device_id,
       REPLACE(blog_url,'http:/url/','')AS user_name
FROM user_submit;

结果展示

19.截取出年龄

导入数据

drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(2,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(3,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(4,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(5,2131,'168cm,45kg,22,female','http:/url/sysdney');

user_submit表 

问题:现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如上所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1)AS age, 
       COUNT(device_id)AS number
FROM user_submit
GROUP BY age;

结果展示

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

导入数据

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

user_profile表 

问题:现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa(输出结果按university升序排序) 

SELECT a.device_id, a.university, a.gpa
FROM user_profile AS a
INNER JOIN (SELECT *,
                   DENSE_RANK() OVER(PARTITION BY university ORDER BY gpa ASC)AS rk
            FROM user_profile
           )AS b
 ON a.id = b.id
 WHERE b.rk = 1
 ORDER BY a.university ASC;

21.浙江大学用户题目回答情况 

导入数据

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

question_practice_detail表 

举例解释:第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误 

user_profile表 

举例解释:第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 

问题:现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据 (查询结果返回device_id,question_id,result三列，根据question_id升序排序)

-- 第一种解法:子查询
SELECT device_id, question_id, result
FROM question_practice_detail
WHERE device_id IN(SELECT device_id
                   FROM user_profile
                   WHERE university = '浙江大学');

-- 第二种解法                   
SELECT a.device_id, a.question_id, a.result
FROM question_practice_detail AS a
INNER JOIN user_profile AS b
ON a.device_id = b.device_id
AND b.university = '浙江大学';

结果展示

22.统计每个学校的答过题的用户的平均答题数

导入数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);
CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');

question_practice_detail表 (答题情况明细表)

question_id: 题目编号

result: 答题结果

举例解释:第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误 

user_profile表 (用户信息表)

device_id: 终端编号(认为每个用户有唯一的一个终端)

ender: 性别

age: 年龄

university: 用户所在的学校

gpa: 该用户平均学分绩点

active_days_within_30: 30天内的活跃天数

举例解释:第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12 

问题:用SQL查找每个学校用户的平均答题数目(说明: 某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)，查询应返回如下结果展示呈现形式(avg_answer_cnt保留4位小数)，注意：结果按照university升序排序

SELECT a.university, ROUND((COUNT(b.question_id) / COUNT(DISTINCT a.device_id)),4)AS avg_answer_cnt
FROM user_profile AS a
INNER JOIN question_practice_detail AS b
ON a.device_id = b.device_id
GROUP BY a.university
ORDER BY a.university ASC;

结果展示 

举例解释:

第一行：北京大学总共有2个用户，2138和6543，2个用户在question_practice_detail里面答了2题，平均答题数目为2/2=1.0000

....

最后一行:浙江大学总共有1个用户，2315，这个用户在question_practice_detail里面答了3题，平均答题数目为3/1=3.0000

23.统计每个用户的平均刷题数

导入数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

user_profile表 (用户信息表)

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

question_practice_detail表 (题库练习明细表)

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误 

question_detail表 

举例解释: 第一行表示: 题目id为111的难度为hard

问题:使用SQL查询，计算山东、不同难度的用户平均答题量，查询应返回如下结果展示呈现形式(avg_answer_cnt保留4位小数)

SELECT a.university, c.difficult_level, ROUND(COUNT(b.question_id) / COUNT(DISTINCT a.device_id),4)AS avg_answer_cnt
FROM user_profile AS a
INNER JOIN question_practice_detail AS b
ON a.device_id = b.device_id
INNER JOIN question_detail AS c
ON b.question_id = c.question_id
WHERE a.university = '山东大学'
GROUP BY a.university, c.difficult_level;

结果展示

举例解释:

山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

24.统计每个学校各难度的用户平均刷题数 

导入数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

user_profile表 (用户信息表)

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

question_practice_detail表 (题库练习明细表)

举例解释: 第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误 

question_detail表 

举例解释: 第一行表示: 题目id为111的难度为hard

问题:使用SQL查询，计算不同学校、不同难度的用户平均答题量，查询应返回如下结果展示呈现形式(avg_answer_cnt保留4位小数)

SELECT a.university, c.difficult_level, ROUND(COUNT(b.question_id) / COUNT(DISTINCT a.device_id),4)AS avg_answer_cnt
FROM user_profile AS a
INNER JOIN question_practice_detail AS b
ON a.device_id = b.device_id
INNER JOIN question_detail AS c
ON b.question_id = c.question_id
GROUP BY a.university, c.difficult_level;

结果展示

举例解释:

第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000 

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000 

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000 

....

25.统计复旦用户8月练题情况

导入数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;

CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

user_profile表 (用户信息表) 

question_practice_detail表

问题:现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0 (查询应返回结果展示呈现形式)

SELECT a.device_id, a.university,
       SUM(IF(result IS NOT NULL, 1, 0))AS question_cnt, 
       SUM(IF(result = 'right', 1, 0))AS right_question_cnt
FROM user_profile AS a
LEFT JOIN question_practice_detail AS b
ON a.device_id = b.device_id
WHERE a.university = '复旦大学' AND (MONTH(b.date) = '08' OR date is null)
GROUP BY a.device_id;

结果展示

26.浙大不同难度题目的正确率

导入数据

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

user_profile表

question_practice_detail表

question_detail表 

问题:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出 (查询应返回结果展示呈现形式)

SELECT c.difficult_level, (SUM(IF(result = 'right',1,0)) / COUNT(b.id))AS correct_rate
FROM user_profile AS a
INNER JOIN question_practice_detail AS b
ON a.device_id = b.device_id
INNER JOIN question_detail AS c
ON b.question_id = c.question_id
WHERE a.university = '浙江大学'
GROUP BY c.difficult_level
ORDER BY correct_rate ASC;

结果展示

27.计算用户的平均次日留存率 

导入数据

drop table if  exists `question_practice_detail`;

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

question_practice_detail表 

问题:现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率 (查询应返回结果展示呈现形式)

SELECT (COUNT(ld) / COUNT(fd))AS avg_ret
FROM (SELECT DISTINCT a.device_id, a.date AS fd, b.date AS ld
      FROM question_practice_detail AS a
      LEFT JOIN question_practice_detail AS b
      ON a.device_id = b.device_id
      AND b.date = a.date + 1)AS m1;

结果展示

山茶花开时。
关注
专栏目录
牛客网SQL错题集及知识点整理（倒序）
Yating260的博客
08-01 390
牛客网61题中35道没有一次通过的题目整理
sqlzoo错题集-SELECT from WORLD Tutorial
sun_8923的博客
06-13 321
@[TOC]SELECT from WORLD Tutorial 12，Matching name and capital The capital of Sweden is Stockholm. Both words start with the letter ‘S’. Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capi
SQL数据库案例1——创建商店数据库
weixin_64841524的博客
01-10 5850
创建商店数据库Commodities_Management。库名：Commodities_Management。创建商品表、供应商表、进货表、销售表。
MYSQL基础语法-01
wdian2012的博客
04-15 104
MYSQL基本操作 1.DDL DDL 是数据定义语言，就是对数据库，表层面的操作，如 CREATE,ALTER，DROP。 1.1.数据库操作 查询所有数据库 SHOW DATABASES; 查询mysql数据库的创建语句 SHOW CREATE DATABASE mysql; 创建db1数据库 CREATE DATABASE db1; 创建数据库db2(判断，如果不存在则创建) CREATE DATABASE IF NOT EXISTS db2; 创建数据库d
sql入门练习
最新发布
qq_61604352的博客
07-08 312
8.现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。14.现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。4.现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。5.题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。
【SQL练习】创建表格，并添加记录
aigo_2021的博客
02-18 3782
1.创建表格结构 2.添加记录 代码： 1.创建表格： drop table work; drop table employee; drop table department; create table employee( empid varchar(4) primary key, empname varchar(20) not null, age int, address varchar(50), sal int ); create table department...
牛客sql练习记录：sql30、31、32
cookies_duoduo的博客
07-12 88
思路：使用字符截取函数SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;
SQL89 计算总和
llew的博客
02-21 1129
SQL89 计算总和
SQLserver2008数据库练习题集.doc
09-30
SQLserver2008数据库练习题集.doc
sql经典练习题集~面试有用哦
03-18
根据提供的文件内容，我们可以归纳出一系列与SQL...以上就是从给定的文件中提取出的SQL练习题集及其解答。这些题目覆盖了SQL的基本操作和一些较为复杂的查询技巧，对于准备面试或提升SQL技能的人来说是非常有用的资源。
SQL.rar_sql exercises pdf
09-19
这份名为“SQL.rar”的压缩包中包含了一份“SQL.pdf”，它很可能是一份精心设计的SQL练习题集，旨在帮助学习者提升对数据库操作的理解和技能。对于任何想要深入理解和运用SQL的人来说，这样的资源无疑是宝贵的。 ...
牛客sql题目练习
qq_52425315的博客
03-03 1082
牛客SQL题练习
SQL2K数据库开发六之表操作创建产品表products
weixin_34293059的博客
01-18 573
          1.在SQL Server企业管理器中的数据库下的“表”节点上右击鼠标，在弹出的菜单上点击“新建表”。           2.在出现的表设计器中，为表中加入五个列：ProductID、ProductName、QuantityPerUnit、UnitPrice、SupplierID。其数据类型和长度如下图。由于每一件产品必须有唯一标识的ID和产品名称，所以前两列是不允许为...
商品表的sql
倾城一笑stu
11-16 4685
# tinyint : 0~255 # smallint : 0~ 65535 # mediumint : 0~1千6百多万 # int : 0~40多亿 # char 、varchar 、 text容量？ # char :0~255个字符 # varchar : 0~65535 字节 看表编码，如果是utf8存2万多汉字 gbk存3万多汉字 # text : 0~65535 字符 C
mysql中产品表怎么写_产品操作MySQL第5篇 – 数据查询-SELECT
weixin_35288487的博客
02-01 217
MYSQL本资料为产品岗位作为日常工作参考，语言口语化At 2019/4/22 By David.Yang普通查询(使用简单的SELECT语句来查询单个表中的数据)SELECT语句用来从数据库中进行数据查询并返回，Excel表格我们在构建时有【行】与【列】，一行在我们的理解下是一行数据，一列是相同属性的一组数据，多条行数据或者多列数据是一个集合，在数据库中使用SELECT的到的结果我们称之为结果集...
数据查询--单表查询（SQL）
赵旭的博客
03-26 5527
    SQL为使用者提供SELECT语句进行数据查询，语句一般格式为：SELECT [ALL|DISTINCT]&lt;目标列表达式1&gt;,&lt;目标列表达式2&gt;,... FROM &lt;表名或视图名&gt; WHERE &lt;条件表达式&gt; GROUP BY &lt;列名1&gt; [HAVING &lt;条件表达式&gt;] ORDER BY &lt;列名&gt; [AS
【牛客网SQL篇】非技术快速入门
随缘博客
05-25 859
sql-quick-study 01 基础查询 概念：基础查询 SQL1 查询多列 题目： 现在运营同学想要用户的设备 id 对应的性别、年龄和学校的数据，请你取出相应数据 语法： select 要显示的列名 from 表名; SELECT 关键字后给出多个列名，列名之间必须以逗号分隔 SELECT device_id, gender, age, university FROM user_profile; SQL2 查询所有列 题目： 现在运营想要查看用户信息表中所有的数据，请你取
SQL语句练习-入门篇
你隔壁的小王的博客
01-06 5991
​ 所有题目来自牛客网：SQL入门篇，整理了入门篇全部题目，配以相应的代码解析和思考，不足之处还请指正，所有题目配以目录超链接方便大家查询，有些难懂的知识配了相关的查询链接。 ​
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值