本文为牛客网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;