所有题目来自牛客网:SQL入门篇,整理了入门篇全部题目,配以相应的代码解析和思考,不足之处还请指正,所有题目配以目录超链接方便大家查询,有些难懂的知识配了相关的查询链接。
目录
SQL1 查询多列
SQL2 查询所有列
SQL3 查询结果去重
SQL4 查询结果限制返回行数
SQL5 将查询后的列重新命名
SQL6 查找学校是北大的学生信息
SQL7 查找年龄大于24岁的用户信息
SQL8 查找某个年龄段的用户信息
SQL9 查找除复旦大学的用户信息
SQL10 用where过滤空值练习
SQL11 高级操作符练习(1)
SQL12 高级操作符练习(2)
SQL13 Where in 和Not in
SQL14 操作符混合运用
SQL15 查看学校名称中含北京的用户
SQL16 查找GPA最高值
SQL17 计算男生人数以及平均GPA
SQL18 分组计算练习题
SQL19 分组过滤练习题
SQL20 分组排序练习题
SQL21 浙江大学用户题目回答情况
SQL22 统计每个学校的答过题的用户的平均答题数
SQL23 统计每个学校各难度的用户平均刷题数
SQL24 统计每个用户的平均刷题数
SQL25 查找山东大学或者性别为男生的信息
SQL26 计算25岁以上和以下的用户数量
SQL27 查看不同年龄段的用户明细
SQL28 计算用户8月每天的练题数量
SQL29 计算用户的平均次日留存率
SQL30 统计每种性别的人数
SQL31 提取博客URL中的用户名
SQL32 截取出年龄
SQL33 找出每个学校GPA最低的同学
SQL33 找出每个学校GPA最低的同学
SQL34 统计复旦用户8月练题情况
SQL35 浙大不同难度题目的正确率
题目:现在运营同学想要用户的设备id对应的性别、年龄和学校的数据,请你取出相应数据
select device_id,gender,age,university from user_profile
select * from user_profile
select distinct university from user_profile
distinct 去重
select device_id from user_profile limit 0,2
LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果只给定一个参数,它表示返回最大的记录行数目。
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1。
检索记录行1-2
select device_id from user_profile limit 0,2
select device_id as user_infos_example from user_profile limit 2
as 别名可省略
Select device_id ,university from user_profile where university="北京大学"
select device_id,gender,age,university from user_profile where age>24
select device_id,gender,age from user_profile where age BETWEEN 20 and 23 #where age >=20 and age <=23
between and 是闭区间注意
题目:现在运营想要查看除复旦大学以外的所有用户明细,请你取出相应数据
select device_id,gender,age,university from user_profile #where university <> "复旦大学" #where university != "复旦大学" #where university not like "复旦大学" where university not in ( "复旦大学")
select device_id ,gender,age,university from user_profile #where age not like" null" #where age not in (" null") #where age!=" null" where age<>" null"
select device_id,gender,age,university,gpa from user_profile where gender="male"and gpa>"3.5"
select device_id,gender,age,university,gpa from user_profile where university = "北京大学"or gpa>"3.7"
select device_id,gender,age,university,gpa from user_profile #where university in ("北京大学","复旦大学","山东大学") where university not IN ("浙江大学")
select device_id,gender,age,university,gpa from user_profile where (university='山东大学' and gpa>3.5 ) or (university="复旦大学" and gpa>3.8);
select device_id,age,university from user_profile #where university like "%北京%" WHERE university REGEXP "北京"
% :百分号 代表匹配0个或多个字符
_:一个字符
select max(gpa) from user_profile where university ="复旦大学"
select count(gender) male_num,round(avg(gpa),1) avg_gpa from user_profile where gender='male'
round(a,b)返回a的第几位小数
select gender, university, count(gender) user_num, avg(active_days_within_30 )avg_active_day, avg(question_cnt) avg_question_cnt from user_profile group by gender ,university
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 or avg(answer_cnt)<20
题目:现在运营想要查看不同大学的用户平均发帖情况,并期望结果按照平均发帖情况进行升序排列,请你取出相应数据。
select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg(question_cnt)
order by 默认升序排列
题目:现在运营想要查看所有来自浙江大学的用户题目回答明细情况,请你取出相应数据
方法1:join两个表,用inner join on a.device_id=b.device_id
select a.device_id,a.question_id,a.result from question_practice_detail a inner join user_profile b on a.device_id=b.device_id where b.university = "浙江大学"
方法2:先从画像表找到浙江大学的所有学生id列表
select device_id, question_id, result from question_practice_detail where device_id in ( select device_id from user_profile where university='浙江大学' )
题目:运营想要了解每个学校答过题的用户平均答题数量情况,请你取出数据。
select distinct a.university ,COUNT(b.question_id)/COUNT(distinct(a.device_id)) avg_answer_cnt from user_profile a inner join question_practice_detail b on a.device_id=b.device_id group by university
count(question_id) / count(distinct device_id)
。题目:运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量,请你写SQL取出相应数据
select distinct a.university,c.difficult_level,count(b.question_id)/COUNT(distinct(b.device_id)) from user_profile a ,question_practice_detail b,question_detail c where a.device_id=b.device_id and b.question_id=c.question_id group by a.university,c.difficult_level
题目:运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数,请取出相应数据
select a.university ,c.difficult_level, count(b.question_id)/ count(distinct b.device_id) avg_answer_cnt from user_profile a,question_practice_detail b,question_detail c where a.device_id=b.device_id and b.question_id=c.question_id and university="山东大学"
题目:现在运营想要分别查看学校为山东大学或者性别为男性的用户的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"
union all 不去重
题目:现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
第一种方法:
select (case when age>=25 then '25岁及以上' else '25岁以下' end) age_cut, count(device_id) as number from user_profile group by age_Cut
CASE语句有两种形式:第一种评估一个或多个条件,并返回第一个符合条件的结果。 如果没有条件是符合的,则返回ELSE子句部分的结果,如果没有ELSE部分,则返回NULL:
第二种CASE句法返回第一个value = compare_value比较结果为真的结果。 如果没有比较结果符合,则返回ELSE后的结果,如果没有ELSE部分,则返回NULL:
第二种方法:
用IF
select if(age>=25,"25岁及以上","25岁以下") age_cut, count(device_id) as number from user_profile group by age_Cut
第三种方法:
用union all将两个 SQL 语句的结果合并在一起
select '25岁以下' as age_cut,count(device_id) as number from user_profile where age<25 or age is null union all select '25岁及以上' as age_cut,count(device_id) as number from user_profile where age>=25;
题目:现在运营想要将用户划分为20岁以下,20-24岁,25岁及以上三个年龄段,分别查看不同年龄段用户的明细情况,请取出相应数据。(注:若年龄为空请返回其他。)
select device_id,gender, case when age<20 then "20岁以下" when age <25 then "20-24岁" when age >=25 then '25岁及以上' else "其他" end age_cut from user_profile
题目:现在运营想要计算出2021年8月每天用户练习题目的数量,请取出相应数据。
select day(date) as day, count(question_id) as question_cnt from question_practice_detail where month(date)= 8 group by date
根据示例,你的查询应返回以下结果:
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 outer join question_practice_detail as q2 on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1
题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个性别的用户分别有多少参赛者,请取出相应结果
SELECT right(profile,",",'-1') gender,count(device_id) number from user_submit group by gender
select ## 替换法 replace(string, '被替换部分','替换后的结果') -- device_id, replace(blog_url,'http:/url/','') as user_name --80.09 --89.35 ## 截取法 substr(string, start_point, length*可选参数*)--86.04 --87.07 -- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam 80.09 --79.79 ## 删除法 trim('被删除字段' from 列名) -- device_id, trim('http:/url/' from blog_url) as user_name ## 字段切割法 substring_index(string, '切割标志', 位置数(负号:从后面开始)) device_id, substring_index(blog_url,'/',-1) as user_name --86.14--91.95 from user_submit;
补充知识:
题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个年龄的用户分别有多少参赛者,请取出相应结果
SELECT SUBSTRING(profile,12,2) age,COUNT(device_id) FROM user_submit GROUP BY age;
第一种方法:用子查询来做
Select a.device_id ,a.university , a.gpa from user_profile a join (select university,min(gpa) as gpa from user_profile group by university) b on a.university=b.university and a.gpa=b.gpa order by a.university
思路是先将学校分组,查到每个学校对应最低的gpa,在组合到一起
第二种方法:用窗口函数(ps:对窗口函数不太了解的同学可以查看:武器库)
select device_id, university, gpa from ( select *, row_number() over (partition by university order by gpa) as a from user_profile ) as b where b.a=1
先按学校分组计算排序gpa,得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中rou_number可以得到排序后的位序,取位序为1即可得到最小值(升序时)。
select a.device_id,a.university,count( b.question_id )as question_cnt, sum( case when result="right" then 1 else 0 end) as right_question_cnt from user_profile a left join question_practice_detail b on a.device_id=b.device_id where a.university="复旦大学" and(month(b.date)=8 or month(b.date) is null) group by a.device_id
题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。
select d.difficult_level, sum(if(result = 'right', 1, 0)) / count(qp.id) as correct_rate from user_profile u, question_practice_detail qp, question_detail d where u.university = '浙江大学' and u.device_id = qp.device_id and qp.question_id = d.question_id group by d.difficult_level order by correct_rate
做一个三表连接,正确率的计算方式:判断result是否为right,是的话赋值为1,对于正确的数目,可以用count,也可以用sum,也可以直接用avg
avg(if(qpd.result='right', 1, 0)) as correct_rate # sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate # count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate