花生_TL007

这个屌丝很懒，什么也没留下！

热门标签

SQL语句练习-入门篇_sql训练

作者：花生_TL007 | 2024-04-17 03:37:35

踩

sql训练

所有题目来自牛客网：SQL入门篇，整理了入门篇全部题目，配以相应的代码解析和思考，不足之处还请指正，所有题目配以目录超链接方便大家查询，有些难懂的知识配了相关的查询链接。

SQL13 Where in 和Not in

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

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

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

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

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

SQL1 查询多列

题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据

select device_id,gender,age,university from user_profile

SQL2 查询所有列

select * from user_profile

SQL3 查询结果去重

题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。

select distinct university from user_profile

distinct 去重

SQL4 查询结果限制返回行数

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


select device_id from user_profile
limit 0,2

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果只给定一个参数，它表示返回最大的记录行数目。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。

检索记录行1-2


select device_id from user_profile
limit 0,2

SQL5 将查询后的列重新命名

题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。


select device_id as user_infos_example 
from user_profile
limit 2

as 别名可省略

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

题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。


Select device_id ,university from user_profile
where university="北京大学"

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

题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。


select device_id,gender,age,university from user_profile
where age>24

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

题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。


select device_id,gender,age from user_profile
where age BETWEEN 20 and 23
#where age >=20 and age <=23

between and 是闭区间注意

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

题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据


select device_id,gender,age,university from user_profile
#where university <> "复旦大学"
#where university != "复旦大学"
#where university not like  "复旦大学"
where university not in ( "复旦大学")

SQL10 用where过滤空值练习

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


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"

SQL11 高级操作符练习(1)

题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。


select device_id,gender,age,university,gpa from user_profile
where gender="male"and gpa>"3.5"

SQL12 高级操作符练习(2）

题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据


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 ("北京大学","复旦大学","山东大学")
where university not IN ("浙江大学")

SQL14 操作符混合运用

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据


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 "%北京%"
WHERE university REGEXP "北京"

% ：百分号代表匹配0个或多个字符

_:一个字符

SQL16 查找GPA最高值

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据


select max(gpa) from user_profile
where university ="复旦大学"

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

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。


select count(gender) male_num,round(avg(gpa),1) avg_gpa
from user_profile
where gender='male'

round(a,b)返回a的第几位小数

SQL18 分组计算练习题

用户信息表：user_profile
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）


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

SQL19 分组过滤练习题

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。


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

SQL20 分组排序练习题

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


select university,
avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg(question_cnt)

order by 默认升序排列

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

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

方法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='浙江大学'
)

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

题目：运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。


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)。

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

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写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

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

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据


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="山东大学"

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

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的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 不去重

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

题目：现在运营想要将用户划分为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;

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

题目：现在运营想要将用户划分为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

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

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


select day(date) as day, count(question_id) as  question_cnt
from question_practice_detail
where month(date)= 8
group by date

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

根据示例，你的查询应返回以下结果：

滞后一天日期且前一天上线的唯一id的总数量) / (前一天上线的唯一id的总数量)，
用datediff区分第一天和第二天在线的device_id
用left outer join做自表联结


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

SQL30 统计每种性别的人数

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


SELECT right(profile,",",'-1') gender,count(device_id) number
from user_submit
group by gender

SQL31 提取博客URL中的用户名

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


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;

补充知识：

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；
2、POSITION(substr IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；
3、LEFT(str, length)：从左边开始截取str，length是截取的长度；
4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；
5、SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;
6、SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符；
7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；
8、LENGTH(str)：计算字符串str的长度。

SQL32 截取出年龄

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


SELECT SUBSTRING(profile,12,2) age,COUNT(device_id)
FROM user_submit
GROUP BY age;

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

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

第一种方法：用子查询来做


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即可得到最小值（升序时）。

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

题目：现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.
弄清楚sum和count的区别，sum是总数，count只求真值，求和用累加sum(),求行的个数用累计count()


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

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

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。


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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/花生_TL007/article/detail/438007