SQL 题目——入门篇

本文为牛客网SQL入门篇题目的题解，刷题地址为牛客题霸-SQL入门篇

SQL1 查询所有列

SELECT *
FROM user_profile;

SQL2 查询多列

SELECT device_id, gender, age, university
FROM user_profile;

SQL3 查询结果去重

SELECT DISTINCT university
FROM user_profile;

SQL4 查询结果限制返回行数

SELECT device_id
FROM user_profile
LIMIT 2;

SQL5 将查询后的列重新命名

SELECT device_id AS user_infos_example
FROM user_profile
LIMIT 2;

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

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

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

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

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

SELECT device_id, gender, age
FROM user_profile
WHERE age BETWEEN 20 AND 23;

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

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

SQL10 用where过滤空值练习

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

SQL11 高级操作符练习(1)

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

SQL12 高级操作符练习(2）

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

SQL13 Where in 和Not in

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

SQL14 操作符混合运用

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

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

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

SQL16 查找GPA最高值

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

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

SELECT COUNT(gpa) AS male_num, ROUND(AVG(gpa), 1) AS avg_gpa
FROM user_profile
WHERE gender = 'male';

SQL18 分组计算练习题

SELECT gender, university, count(gender) AS user_num, AVG(active_days_within_30) AS avg_active_day, AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university, gender;

SQL19 分组过滤练习题

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

SQL20 分组排序练习题

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

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

SELECT up.device_id, question_id, result
FROM user_profile AS up, question_practice_detail AS qpd
WHERE university = '浙江大学' AND up.device_id = qpd.device_id;

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

SELECT university, COUNT(question_id)/COUNT(DISTINCT up.device_id) AS avg_answer_cnt
FROM user_profile AS up, question_practice_detail AS qpd
WHERE up.device_id = qpd.device_id
GROUP BY university;

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

SELECT university, difficult_level, ROUND(COUNT(qpd.question_id)/COUNT(DISTINCT up.device_id), 4) AS avg_answer_cnt
FROM user_profile AS up, question_practice_detail AS qpd, question_detail AS qd
WHERE up.device_id = qpd.device_id AND qpd.question_id = qd.question_id
GROUP BY university, difficult_level;

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

SELECT university, difficult_level, ROUND(COUNT(qpd.question_id)/COUNT(DISTINCT up.device_id), 4) AS avg_answer_cnt
FROM user_profile AS up, question_practice_detail AS qpd, question_detail AS qd
WHERE university = '山东大学' AND up.device_id = qpd.device_id AND qpd.question_id = qd.question_id
GROUP BY difficult_level;

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

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';

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

SELECT age_cut, count(age_cut) AS number
FROM (SELECT IF(age IS NULL OR age < 25, '25岁以下', '25岁及以上') AS age_cut FROM user_profile) AS t
GROUP BY age_cut;

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

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

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

SELECT DAY(date) AS day, count(date) AS question_cnt
FROM question_practice_detail
GROUP BY date;

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

SELECT COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) AS avg_ret
FROM question_practice_detail AS q1 LEFT JOIN question_practice_detail AS q2 
ON q1.device_id = q2.device_id AND DATEDIFF(q2.date, q1.date) = 1;

SQL30 统计每种性别的人数

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

SQL31 提取博客URL中的用户名

SELECT device_id, SUBSTRING_INDEX(blog_url, '/', '-1') AS user_name
FROM user_submit;

SQL32 截取出年龄

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

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

SELECT device_id, university, gpa
FROM user_profile
WHERE (university, gpa) IN (SELECT university, MIN(gpa) FROM user_profile GROUP BY university)
ORDER BY university;

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

SELECT up.device_id, university, COUNT(question_id) AS question_cnt, SUM(IF(result = 'right', 1, 0)) AS right_question_cnt
FROM user_profile AS up LEFT JOIN question_practice_detail AS qpd ON up.device_id = qpd.device_id AND MONTH(date) = 8
WHERE university = '复旦大学'
GROUP BY up.device_id;

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

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

SQL36 查找后排序

SELECT device_id, age
FROM user_profile
ORDER BY age;

SQL37 查找后多列排序

SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa, age;

SQL38 查找后降序排列

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

SQL39 21年8月份练题总数

SELECT COUNT(DISTINCT device_id) AS did_cnt, COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date) = 2021 AND MONTH(date) = 8;