牛客SQL 非技术快速入门 39套题代码及解析

兔霸哥S

已于 2023-06-29 23:39:54 修改

阅读量502

点赞数 2

分类专栏： SQL 文章标签： sql 数据库服务器产品运营

于 2022-09-17 20:38:02 首次发布

本文链接：https://blog.csdn.net/qq118640594X/article/details/126901222

版权

SQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

练习链接：
https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=199

牛客SQL 256套代码及解析：
https://blog.csdn.net/qq118640594X/article/details/131466462?spm=1001.2014.3001.5501

1.基础查询

基础查询

SQL1 查询所有列（新增知识点： *）

select *
from user_profile

SQL2 查询多列

select device_id,gender,age,university
from user_profile

简单处理查询结果

SQL3 查询结果去重（新增知识点：distinct）

select distinct university
from user_profile

SQL4 查询结果限制返回行数（新增知识点：limit）

select device_id
from user_profile
limit 2

SQL5 将查询后的列重新命名（新增知识点：as）

select device_id as user_infos_example
from user_profile
limit 2

2.条件查询

基础操作符

SQL6 查找学校是北大的学生信息（新增知识点：where）

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

SQL7 查找年龄大于24岁的用户信息（新增知识点：>）

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

SQL8 查找某个年龄段的用户信息（新增知识点：between）

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过滤空值练习（新增知识点：is not null）

select device_id,gender,age,university
from user_profile
where age is not null

高级操作符

SQL11 高级操作符练习（1）（新增知识点：where…and…）

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

SQL12 高级操作符练习（2）（**新增知识点：or）

select device_id,gender,age,university,gpa
from user_profile
where university='北京大学' or gpa > 3.7

SQL13 where in 和 not in（新增知识点： where …in （‘…’ , ‘…’, ‘…’） ）

select device_id,gender,age,university,gpa
from user_profile
where university in ('北京大学','复旦大学','山东大学')

SQL14 操作符混合运用（新增知识点：where（…and…）or （…and… ）

select  device_id,  gender,  age,  university,  gpa
from  user_profile
where  (gpa > 3.5  and university = '山东大学' ) 
      or  (gpa > 3.8  and university = '复旦大学' )

SQL 15 查看学校名称中含北京的用户（新增知识点： like ‘%…%’ ）

select device_id,age,university
from user_profile
where university like '%北京%'

3.高级查询

计算函数

SQL16 查找GPA最高值（新增知识点： max() ）

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

SQL17 计算男生人数以及平均GPA（新增知识点： 1.count ; 2. avg ）

select
count(id) as male_num,
avg(gpa) as gpa
from user_profile
where gender='male'

分组查询

SQL18 分组计算练习题（新增知识点：group by ）

select gender,university,
count(university) as user_num,
avg(active_days_within_30) as avg_active_day,
avg(question_cnt) as avg_question_cnt
from user_profile
group by gender,university

SQL19 分组过滤练习题
（新增知识点： 1.round（…，n）; 2. 聚合函数结果作为筛选条件时，不能用where，而是用having语法）

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 avg_question_cnt<5 or avg_answer_cnt <20

SQL20 分组排序练习题（新增知识点：order by）

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

4.多表查询

子查询

SQL21 浙江大学用户题目回答情况
（新增知识点：…where…in(select…from) ）

select device_id,question_id,result
from question_practice_detail
where device_id in 
(select device_id from user_profile where university='浙江大学')

链接查询

SQL22 统计每个学校的答过题的用户平均答题数
（新增知识点：join…on…=… ）

select university,
count(question_id)/count(distinct (q.device_id)) as avg_answer_cnt
from user_profile as u
join question_practice_detail as q 
on u.device_id = q.device_id
group by university
order by university

SQL23 统计每个学校各难度用户平均刷题数
（新增知识点：join…using(…) ）

select university,
difficult_level,
round(count(question_id)/count(distinct(device_id)) ,4)as avg_answer_cnt
from user_profile
join question_practice_detail using(device_id)
join question_detail using(question_id)
group by university,difficult_level

SQL24 统计每个用户的平均刷题数
（新增知识点：where 要放到 group by 前面）

select university,difficult_level,
round(count(question_id)/count(distinct(device_id)),4) as avg_answer_cnt
from user_profile
join question_practice_detail using (device_id)
join question_detail using (question_id)
where university='山东大学'
group by university,difficult_level

综合查询

SQL25 查找山东大学或者性别为男生的信息
（新增知识点：1.union all ；2.union all 不去重，union去重，两者不一样，在这里不能换成union；3.union的结果和用or的结果一样；4.总体来说，用and是交集，用or或者union是并集，用union all 是并集再加上重复的区域，即重复的地方算了两遍，所以没有去重）

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'

5.必会的常用函数

条件函数

SQL 26 计算25岁以上和以下用户数量
（新增知识点：if（满足的条件，要么为… ,否则为…））

select if(age>=25,'25岁及以上','25岁以下') as age_cut,
count(device_id) as number
from user_profile
group by age_cut

SQL27 查看不同年龄段的用户明细
（新增知识点：case when…then… when…then… else… end）

select device_id,gender,
case 
when age<20 then '20岁以下'
when age between 20 and 24 then '20-24岁'
when age>=25 then '25岁及以上'
else '其他'
end as age_cut
from user_profile

日期函数

SQL28 计算用户8月每天的练题数量
（新增知识点：1.date_format（列名，‘%Y%m%d’）返回时间年月日；2. day（列名）year（列名）month（列名）同样可以返回时间日年月；）

select   
    date_format(date, '%d') day,
    count(question_id) question_cnt
from  question_practice_detail
where 
    date_format(date, '%m') = '08'
group by day

SQL29 计算用户的平均次日留存率
（新增知识点：1. DATEDIFF()函数返回两个日期值之间的天数 ; 2. lead（字段名，n）over（partition by …order by…）表示一列数据以…顺序，按照…分割，向前推动n行，空出后面，如果没写n, n默认为1）

select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
        order by date) as a
) as b

文本函数

SQL30 统计每种性别的人数
（新增知识点：*1.substring_index（列名，‘分割符号’ ，n）如果n为1，表示取第一个分隔符号左边的那部分，n为-2，表示取倒数第二个分隔符号右边的部分； *）

select
substring_index(profile,',',-1) as gender,
count(substring_index(profile,',',-1))as number
from user_submit
group by gender

SQL31 提取博客URL中的用户名
（新增知识点：1.substring(列名, start, length) start表示起始项，length表示取字符串的长度 ; 2.replace（列名，‘被替换的’，‘替换成这玩意’）； 3. trim（‘被删除的’ from "从这里删除））

-- 方法1 替换
select device_id, replace(blog_url,'http:/url/','') as user_name
from user_submit;

-- 方法2 截取
select device_id, substr(blog_url,11,length(blog_url)-10) as user_name
from user_submit;

-- 方法3 删除
select device_id, trim('http:/url/' from blog_url) as user_name
from user_submit;

-- 方法4 字段切割
select device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit;

SQL32 截取出年龄

select
substring_index(substring_index(profile,',',3),',',-1)as age,
count(device_id) as number
from user_submit
group by age

窗口函数

SQL33 找出每个学校GPA最低的同学
（新增知识点：* 窗口函数与聚和函数的区别：聚合函数都会减少查询返回的行数。如一个8行的表格，sum一下，就只剩下一行的总额。与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减少查询返回的行数。对于一个8行的表格，用窗口函数sum，还会有8行，每行都会出现总额。*）

select device_id,university,gpa
from
(select device_id,university,gpa
min(gpa) over (partition by university order by university) as min_gpa
from user_profile )s1
where min_gpa = gpa

6.综合练习

SQL34 统计复旦用户8月练题情况
（新增知识点：left join包含左表全部，right join包含右表全部，inner join只包含交集（join和inner join一个意思），full join 包含全部）

select device_id, university,
count(question_id) as question_cnt,
sum(if(result='right',1,0)) as right_question_cnt
from user_profile
left join question_practice_detail using(device_id) 
where university='复旦大学'  and  (month(date)='08' or month(date) is null)
group by device_id

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

select difficult_level,
 (sum(if(result='right',1,0)) / count(question_id)) as correct_rate
from user_profile
join question_practice_detail using (device_id)
join question_detail using( question_id)
where university='浙江大学'
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 查找后降序排列
（新增知识点：升序：asc ，降序：desc）

select device_id,gpa,age
from user_profile
order by gpa desc,age desc

SQL39 21年8月的练题总数
（新增知识点：count（）中count和括号之间不能有空格，否则会报错）

SELECT 
count(distinct device_id) did_cnt,
count(question_id) as question_cnt
FROM question_practice_detail
where year(date)='2021' AND month(date)='08'