文章目录
- 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最低的同学**
SQL语句练习
SQL1 查询所有列
select * from user_profile;
SQL2 查询多列
select
device_id,
gender,
age,
university
from
user_profile;
SQL3 查询结果去重
查询结果去重_牛客题霸_牛客网 (nowcoder.com)
select distinct
university
from
user_profile;
SQL4 查询结果限制返回行数
查询结果限制返回行数_牛客题霸_牛客网 (nowcoder.com)
select
device_id
from
user_profile
limit
2
SQL5 将查询后的列重新命名
将查询后的列重新命名_牛客题霸_牛客网 (nowcoder.com)
select
device_id as user_infos_example
from
user_profile
limit
2
SQL6 查找学校是北大的学生信息
查找学校是北大的学生信息_牛客题霸_牛客网 (nowcoder.com)
select
device_id, university
from
user_profile
where
university = '北京大学'
SQL7 查找年龄大于24岁的用户信息
查找年龄大于24岁的用户信息_牛客题霸_牛客网 (nowcoder.com)
select
device_id,
gender,
age,
university
from
user_profile
where
age > 24
SQL8 查找某个年龄段的用户信息
查找某个年龄段的用户信息_牛客题霸_牛客网 (nowcoder.com)
select
device_id,
gender,
age
from
user_profile
where
age >= 20
and age <= 23
SQL9 查找除复旦大学的用户信息
查找除复旦大学的用户信息_牛客题霸_牛客网 (nowcoder.com)
select
device_id,
gender,
age,
university
from
user_profile
where
university != '复旦大学'
SQL10 用where过滤空值练习
用where过滤空值练习_牛客题霸_牛客网 (nowcoder.com)
select
device_id,
gender,
age,
university
from
user_profile
where
age is not null
SQL11 高级操作符练习(1)
高级操作符练习(1)_牛客题霸_牛客网 (nowcoder.com)**
现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研,请你取出相关数据
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
gpa > 3.5
and gender = 'male'
SQL12 高级操作符练习(2)
高级操作符练习(2)_牛客题霸_牛客网 (nowcoder.com)
现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研,请你取出相关数据(使用OR实现)
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
gpa > 3.7
or university = '北京大学'
SQL13 Where in 和Not in
Where in 和Not in_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想要找到学校为北大、复旦和山大的同学进行调研,请你取出相关数据。
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
university = '北京大学'
or university = '复旦大学'
or university = '山东大学'
或者
SQL14 操作符混合运用
操作符混合运用_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想要找到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 查看学校名称中含北京的用户
查看学校名称中含北京的用户_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想查看所有大学中带有北京的用户的信息,请你取出相应数据。
select
device_id,
age,
university
from
user_profile
where
university like '%北京%'
SQL16 查找GPA最高值
查找GPA最高值_牛客题霸_牛客网 (nowcoder.com)
题目:运营想要知道复旦大学学生gpa最高值是多少,请你取出相应数据
你的查询应返回以下结果,结果保留到小数点后面1位(1位之后的四舍五入):
select
round(max(gpa), 1)
from
user_profile
where
university = '复旦大学'
SQL17 计算男生人数以及平均GPA
[计算男生人数以及平均GPA_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/4e22fc5dbd16414fb2c7683557a84a4f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少,用以辅助设计相关活动,请你取出相应数据。
select
count(gender) as male_num,
round(avg(gpa), 1) as avg_gpa
from
user_profile
where
gender = 'male'
★SQL18 分组计算练习题
[分组计算练习题_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/4e22fc5dbd16414fb2c7683557a84a4f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析,请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
你的查询返回结果需要对性别和学校分组,示例如下,结果保留1位小数,1位小数之后的四舍五入:
select
gender,
university,
count(device_id) 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
★SQL19 分组过滤练习题
[分组过滤练习题_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/4e22fc5dbd16414fb2c7683557a84a4f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
根据示例,你的查询应返回以下结果,请你保留3位小数(系统后台也会自动校正),3位之后四舍五入:
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关键字
having
(
avg_question_cnt < 5
or avg_answer_cnt < 20
)
SQL20 分组排序练习题
[分组排序练习题_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/4e22fc5dbd16414fb2c7683557a84a4f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要查看不同大学的用户平均发帖情况,并期望结果按照平均发帖情况进行升序排列,请你取出相应数据。
select
university,
round(avg(question_cnt), 4) as avg_question_cnt
from
user_profile
group by
university
order by
avg_question_cnt
#不需要having
★SQL21 浙江大学用户题目回答情况
[浙江大学用户题目回答情况_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/4e22fc5dbd16414fb2c7683557a84a4f?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要查看所有来自浙江大学的用户题目回答明细情况,请你取出相应数据
select
q.device_id,
q.question_id,
q.result
from
question_practice_detail as q
left join user_profile as u on q.device_id = u.device_id
where
u.university = '浙江大学'
★SQL22 统计每个学校的答过题的用户的平均答题数
统计每个学校的答过题的用户的平均答题数_牛客题霸_牛客网 (nowcoder.com)
题目:运营想要了解每个学校答过题的用户平均答题数量情况,请你取出数据。
select
university,
round(
count(q.question_id) / count(distinct (q.device_id)),
4
) as avg_answer_cnt
from
user_profile as u,
question_practice_detail as q
where
q.device_id = u.device_id
group by
university
★SQL23 统计每个学校各难度的用户平均刷题数
[统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量,请你写SQL取出相应数据
select
university,
difficult_level,
round(
count(qpd.question_id) / count(distinct (qpd.device_id)),
4
) as avg_answer_cnt
from
user_profile as u,
question_practice_detail as qpd,
question_detail as qd
where
qpd.device_id = u.device_id
and qpd.question_id = qd.question_id
#这样就可以分组多个结果了
group by
university,
difficult_level
SQL24 统计每个用户的平均刷题数
[统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数,请取出相应数据
select
university,
difficult_level,
round(
count(qpd.question_id) / count(distinct (qpd.device_id)),
4
) as avg_answer_cnt
from
user_profile as u,
question_practice_detail as qpd,
question_detail as qd
where
qpd.device_id = u.device_id
and qpd.question_id = qd.question_id
and university = '山东大学'
#这样就可以分组多个结果了
group by
university,
difficult_level
★SQL25 查找山东大学或者性别为男生的信息
[查找山东大学或者性别为男生的信息_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要分别查看学校为山东大学或者性别为男性的用户的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'
★SQL26 计算25岁以上和以下的用户数量
计算25岁以上和以下的用户数量_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段,分别查看这两个年龄段用户数量
本题注意:age为null 也记为 25岁以下
select
if (
age < 25
or age is null,
'25岁以下',
'25岁及以上'
) as age_cut,
count(*) as number
from
user_profile
group by
age_cut
#或者
select
case
when age >= 25 then '25岁及以上'
else '25岁以下'
end age_cnt,
count(*) number
from
user_profile
group by
age_cnt
★SQL27 查看不同年龄段的用户明细
查看不同年龄段的用户明细_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想要将用户划分为20岁以下,20-24岁,25岁及以上三个年龄段,分别查看不同年龄段用户的明细情况,请取出相应数据。(注:若年龄为空请返回其他。)
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 age_cnt
from
user_profile
★SQL28 计算用户8月每天的练题数量
[计算用户8月每天的练题数量_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/ae44b2b78525417b8b2fc2075b557592?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要计算出2021年8月每天用户练习题目的数量,请取出相应数据。
select
day (date) as date,
count(question_id) as question_cnt
from
question_practice_detail
where
month (date) = 8
group by
date
★SQL29 计算用户的平均次日留存率
[计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/ae44b2b78525417b8b2fc2075b557592?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
select
count(distinct q2.device_id, q2.date) / count(distinct q1.device_id, q1.date)
from
(
select
device_id,
date
from
question_practice_detail
) as q1
left join (
select
device_id,
date
from
question_practice_detail
) as q2 on q1.device_id = q2.device_id
and datediff (q1.date, q2.date) = 1
★SQL30 统计每种性别的人数
[统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/ae44b2b78525417b8b2fc2075b557592?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个性别的用户分别有多少参赛者,请取出相应结果
select
case
when profile like ('%female') then 'female'
else 'male'
end as gender,
count(*) as number
from
user_submit
group by
gender
★SQL31 提取博客URL中的用户名
[提取博客URL中的用户名_牛客题霸_牛客网 (nowcoder.com)](https://www.nowcoder.com/practice/ae44b2b78525417b8b2fc2075b557592?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fcompany)
题目:对于申请参与比赛的用户,blog_url字段中url字符后的字符串为用户个人博客的用户名,现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段,请取出所需数据。
select
device_id,
substring_index(substring_index(blog_url, ':', -1), '/', -1)
from
user_submit
#blog_url = http:/ur/bigboy777
#substring_index(blog_url, ':', -1) 输出/ur/bigboy777
#substring_index('/ur/bigboy777', '/', -1) 输出 bigboy777
#SUBSTRING_INDEX() 是 MySQL 中的一个非常实用的字符串处理函数,用于从一个字符串中返回指定分隔符出现计数次数之前的子串。这个函数有两个必需的参数和一个可选参数:
#str:原始字符串。
#delim:分隔符,用来界定子串的字符或字符串。
#count:一个整数,可正可负。如果 count 是正数,函数返回从左边开始到分隔符第 count 次出现之前的子串;如果 count 是负数,则返回从右边开始到分隔符第 count 次出现之前的子串。
★SQL32 截取出年龄
题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个年龄的用户分别有多少参赛者,请取出相应结果
select
substring_index (substring_index (profile, ',', -2), ',', 1) as age,
count(device_id) as number
from
user_submit
group by
age
★SQL33 找出每个学校GPA最低的同学
找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)
题目:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa。
select
device_id,
university,
gpa
from
(
select
*,
row_number() over (
partition by
university
order by
gpa
) as rn
from
user_profile
) as uni_min
where
rn = 1
order by
university
#ROW_NUMBER(): 这是一个窗口函数,用于为每个分组(按照university分组)中的行分配一个唯一的、连续的整数。行号是基于ORDER BY gpa DESC确定的,这意味着每个大学内部,GPA最高的学生将获得rn=1,次高为rn=2,依此类推。
r.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tqId=1980672&ru=%2Fpractice%2F847373e2fe8d47b4a2c294bdb5bda8b6&qru=%2Fta%2Fsql-quick-study%2Fquestion-ranking&sourceUrl=%2Fexam%2Fcompany)
题目:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa。
```sql
select
device_id,
university,
gpa
from
(
select
*,
row_number() over (
partition by
university
order by
gpa
) as rn
from
user_profile
) as uni_min
where
rn = 1
order by
university
#ROW_NUMBER(): 这是一个窗口函数,用于为每个分组(按照university分组)中的行分配一个唯一的、连续的整数。行号是基于ORDER BY gpa DESC确定的,这意味着每个大学内部,GPA最高的学生将获得rn=1,次高为rn=2,依此类推。