刷题记录｜牛客网-SQL篇-非技术快速入门（39题）

SQL1：查询所有列

标签：SELECT

SELECT * FROM user_profile;
1

SQL2：查询多列

标签：SELECT

SELECT device_id, gender, age, university FROM user_profile;
1

SQL3：查询结果去重

标签：SELECT、DISTINCT

SELECT DISTINCT university FROM user_profile;
1

SQL4：查询结果限制返回行数

标签：SELECT、LIMIT

SELECT device_id FROM user_profile LIMIT 2;
1

SQL5：将查询后的列重新命名

标签：SELECT、LIMIT

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2;
1

SQL6：查找学校是北大的学生信息

标签：SELECT、WHERE

SELECT device_id, university FROM user_profile WHERE university='北京大学';
1

SQL7：查找年龄大于24岁的用户信息

标签：SELECT、WHERE

SELECT device_id, gender, age, university FROM user_profile WHERE age > 24;
1

SQL8：查找某个年龄段的用户信息

标签：SELECT、WHERE

SELECT device_id, gender, age FROM user_profile WHERE age >= 20 AND age <= 23;
1

SQL9：查找除复旦大学的用户信息

标签：SELECT、WHERE

SELECT device_id, gender, age, university FROM user_profile WHERE university != "复旦大学";
1

SQL10：用where过滤空值练习

标签：SELECT、WHERE

SELECT device_id, gender, age, university FROM user_profile WHERE age IS NOT NULL;
1

SQL11：高级操作符练习(1)

标签：SELECT、WHERE

SELECT device_id, gender, age, university, gpa FROM user_profile WHERE gpa > 3.5 AND gender = "male";
1

SQL12：高级操作符练习(2）

标签：SELECT、WHERE

SELECT device_id, gender, age, university, gpa FROM user_profile WHERE university = "北京大学" OR gpa > 3.7;
1

SQL13：Where in 和Not in

标签：SELECT、WHERE

SELECT device_id, gender, age, university, gpa FROM user_profile WHERE university IN ("北京大学", "复旦大学", "山东大学");
1

SQL14：操作符混合运用

标签：SELECT、WHERE

SELECT device_id, gender, age, university, gpa 
FROM user_profile
WHERE (gpa > 3.5 AND university = "山东大学") OR (gpa > 3.8 AND university = "复旦大学");
1
2
3

SQL15：查看学校名称中含北京的用户

标签：SELECT、WHERE、通配符

SELECT device_id, age, university FROM user_profile WHERE university LIKE "%北京%";
1

SQL16：查找GPA最高值

标签：SELECT、WHERE、聚集函数、数据处理函数

SELECT ROUND(MAX(gpa), 1) AS gpa 
FROM user_profile
WHERE university = "复旦大学";
1
2
3

SQL17：计算男生人数以及平均GPA

标签：SELECT、WHERE、聚集函数

SELECT 
    COUNT(*) AS male_num,
    AVG(gpa) AS avg_gpa
FROM user_profile
WHERE gender = "male";
1
2
3
4
5

SQL18：分组计算练习题

标签：SELECT、聚集函数、数据处理函数、GROUP BY

SELECT
    gender,
    university,
    COUNT(*) 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;
1
2
3
4
5
6
7
8

SQL 19：分组过滤练习题

标签：SELECT、聚集函数、数据处理函数、GROUP BY、HAVING

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;
1
2
3
4
5
6
7

SQL20：分组排序练习题

标签：SELECT、聚集函数、数据处理函数、GROUP BY、HAVING

SELECT
    university,
    ROUND(AVG(question_cnt), 4) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt ASC;
1
2
3
4
5
6

SQL21：浙江大学用户题目回答情况

标签：SELECT、关联、WHERE

SELECT
    user.device_id AS device_id,
    question.question_id AS question,
    question.result AS result
FROM
    question_practice_detail AS question
    LEFT JOIN user_profile AS user ON question.device_id = user.device_id
WHERE
    user.university = "浙江大学";
1
2
3
4
5
6
7
8
9

SQL22：统计每个学校的答过题的用户的平均答题数

标签：SELECT、数据处理函数、聚集函数、DISTINCT、关联、GROUP BY

SELECT
    user.university AS university,
    ROUND(COUNT(*) / COUNT(DISTINCT user.device_id), 4) AS avg_answer_cnt
FROM
    question_practice_detail AS question
    LEFT JOIN user_profile AS user ON question.device_id = user.device_id
GROUP BY
    user.university;
1
2
3
4
5
6
7
8

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

标签：SELECT、数据处理函数、聚集函数、DISTINCT、关联、GROUP BY

SELECT
    user.university AS university,
    detail.difficult_level AS difficult_level,
    ROUND(COUNT(*) / COUNT(DISTINCT question.device_id), 4) AS avg_answer_cnt
FROM
    question_practice_detail AS question
    LEFT JOIN user_profile AS user ON question.device_id = user.device_id
    LEFT JOIN question_detail AS detail ON question.question_id = detail.question_id
GROUP BY
    user.university,
    detail.difficult_level;
1
2
3
4
5
6
7
8
9
10
11

SQL24：统计每个用户的平均刷题数

标签：SELECT、数据处理函数、聚集函数、DISTINCT、关联、WHERE、GROUP BY

SELECT
    user.university AS university,
    detail.difficult_level AS difficult_level,
    ROUND(COUNT(*) / COUNT(DISTINCT question.device_id), 4) AS avg_answer_cnt
FROM
    question_practice_detail AS question
    LEFT JOIN user_profile AS user ON question.device_id = user.device_id
    LEFT JOIN question_detail AS detail ON question.question_id = detail.question_id
WHERE
    user.university = "山东大学"
GROUP BY
    detail.difficult_level
1
2
3
4
5
6
7
8
9
10
11
12

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

标签：SELECT、WHERE、UNION

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";
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL26：计算25岁以上和以下的用户数量

标签：SELECT、聚集函数、GROUP BY、数据处理函数

SELECT
    CASE
        WHEN age < 25
        OR age IS NULL THEN "25岁以下"
        ELSE "25岁及以上"
    END AS age_cut,
    COUNT(*) AS number
FROM
    user_profile
GROUP BY
    age_cut;
1
2
3
4
5
6
7
8
9
10
11

SQL27：查看不同年龄段的用户明细

标签：SELECT、数据处理函数

SELECT
    device_id,
    gender,
    CASE
        WHEN age < 20 THEN "20岁以下"
        WHEN age >= 20
        AND age <= 24 THEN "20-24岁"
        WHEN age >= 25 THEN "25岁及以上"
        ELSE "其他"
    END AS age_cut
FROM
    user_profile;
1
2
3
4
5
6
7
8
9
10
11
12

SQL28：计算用户8月每天的练题数量

标签：SELECT、WHERE、数据处理函数、GROUP BY、聚集函数

SELECT
    DAY(`date`) AS day,
    COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE YEAR(`date`) = 2021 AND MONTH(`date`) = 8
GROUP BY day;
1
2
3
4
5
6

SQL29：计算用户的平均次日留存率

标签：SELECT、聚集函数、数据处理函数、DISTINCT、关联

SELECT
    ROUND(
        COUNT(tomorrow.device_id) / COUNT(today.device_id),
        4
    ) AS avg_ret
FROM
    (
        SELECT DISTINCT
            device_id,
            date
        FROM
            question_practice_detail
    ) AS today
    LEFT JOIN (
        SELECT DISTINCT
            device_id,
            DATE_SUB(date, INTERVAL 1 DAY) AS date
        FROM
            question_practice_detail
    ) AS tomorrow ON today.device_id = tomorrow.device_id AND today.date = tomorrow.date;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SQL30：统计每种性别的人数

标签：SELECT、GROUP BY、数据处理函数

SELECT
    IF (RIGHT (profile, 6) = "female", "female", "male") AS gender,
    COUNT(*) AS number
FROM
    user_submit
GROUP BY
    gender;
1
2
3
4
5
6
7

标签：SELECT、GROUP BY、数据处理函数

SELECT
    SUBSTRING_INDEX(profile, ",", -1) AS gender,
    COUNT(*) AS number
FROM
    user_submit
GROUP BY
    gender;
1
2
3
4
5
6
7

SQL31：提取博客URL中的用户名

标签：SELECT、数据处理函数

SELECT
    device_id, 
    SUBSTRING_INDEX(blog_url, "/", -1) AS user_name
FROM user_submit
1
2
3
4

SQL32：截取出年龄

标签：SELECT、数据处理函数、GROUP BY

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ",", 3), ",", -1) AS age,
    COUNT(*) AS number
FROM user_submit
GROUP BY age;
1
2
3
4
5

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

标签：SELECT、关联、GROUP BY、WHERE、ORDER BY

SELECT
    profile.device_id,
    profile.university,
    profile.gpa
FROM
    user_profile AS profile
    INNER JOIN (
        SELECT
            university,
            MIN(gpa) AS gpa
        FROM
            user_profile
        GROUP BY university
    ) AS lowest ON profile.university = lowest.university AND profile.gpa = lowest.gpa
ORDER BY
    profile.university ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

标签：SELECT、WHERE、ORDER BY、窗口函数、子查询

SELECT
    device_id,
    university,
    gpa
FROM
    (
        SELECT
            device_id,
            university,
            gpa,
            RANK() OVER (PARTITION BY university ORDER BY gpa ASC) AS ranking
        FROM
            user_profile
    ) AS temp
WHERE ranking = 1
ORDER BY
    university ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL34：统计复旦用户8月练题情况

标签：SELECT、关联、WHERE、GROUP BY、聚集函数、数据处理函数

SELECT
    profile.device_id AS device_id,
    profile.university AS university,
    IFNULL(question.question_cnt, 0) AS question_cnt,
    IFNULL(question.right_question_cnt, 0) AS right_question_cnt
FROM
    user_profile AS profile
    LEFT JOIN (
        SELECT
            device_id,
            COUNT(*) AS question_cnt,
            SUM(IF(result = "right", 1, 0)) AS right_question_cnt
        FROM
            question_practice_detail
        WHERE
            MONTH(date) = 8
        GROUP BY
            device_id
    ) AS question ON profile.device_id = question.device_id
WHERE university = "复旦大学";
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

标签：SELECT、关联、WHERE、GROUP BY、聚集函数、数据处理函数

SELECT
    profile.device_id AS device_id,
    profile.university AS university,
    COUNT(question.result) AS question_cnt,
    SUM(IF(question.result = "right", 1, 0)) AS right_question_cnt
FROM
    user_profile AS profile
    LEFT JOIN question_practice_detail AS question ON profile.device_id = question.device_id
    AND MONTH(question.date) = 8
WHERE
    profile.university = "复旦大学"
GROUP BY
    profile.device_id;
1
2
3
4
5
6
7
8
9
10
11
12
13

SQL35：浙大不同难度题目的正确率

标签：SELECT、关联、WHERE、GROUP BY、ORDER BY、聚集函数、数据处理函数

SELECT
    level.difficult_level AS difficult_level,
    ROUND(SUM(IF(result="right", 1, 0)) / COUNT(result), 4) AS correct_rate
FROM question_practice_detail as ques
LEFT JOIN user_profile AS user ON ques.device_id = user.device_id
LEFT JOIN question_detail AS level ON ques.question_id = level.question_id
WHERE user.university = "浙江大学"
GROUP BY level.difficult_level
ORDER BY correct_rate ASC;
1
2
3
4
5
6
7
8
9

SQL36：查找后排序

标签：SELECT、ORDER BY

SELECT device_id, age FROM user_profile ORDER BY age ASC;
1

SQL37：查找后多列排序

标签：SELECT、ORDER BY

SELECT
    device_id, 
    gpa,
    age
FROM user_profile
ORDER BY gpa ASC, age ASC;
1
2
3
4
5
6

SQL38：查找后降序排列

标签：SELECT、ORDER BY

SELECT device_id, gpa, age FROM user_profile
ORDER BY gpa DESC, age DESC;
1
2

SQL39：21年8月份练题总数

标签：SELECT、聚集函数、WHERE

SELECT 
    COUNT(DISTINCT device_id) AS did_cnt,
    COUNT(*) AS question_cnt
FROM question_practice_detail
WHERE MONTH(date) = 8
1
2
3
4
5