基础练习

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

基础查询

SQL1 查询所有列

1
select * from user_profile;

SQL2 查询多列

1
select device_id,gender,age,university from user_profile;

简单处理查询结果

SQL3 查询结果去重

1
select distinct  university from user_profile;

SQL4 查询结果限制返回行数

1
select device_id from user_profile limit 2;

SQL5 将查询后的列重新命名

1
select device_id as user_infos_example  from user_profile limit 2;

条件查询

基础排序

SQL36 查找后排序

1
select device_id,age from user_profile order by age;

SQL37 查找后多列排序

1
select device_id,gpa,age from user_profile order by gpa,age;

SQL38 查找后降序排列

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

基础操作符

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

1
select device_id,university from user_profile where university = '北京大学';

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

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

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

1
select  device_id,gender,age from user_profile where age >= 20 and age <= 23;

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

1
select device_id,gender,age,university from user_profile where not (university = '复旦大学');

SQL10 用where过滤空值练习

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

高级操作符

SQL11 高级操作符练习(1)

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

SQL12 高级操作符练习(2）

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

SQL13 Where in 和Not in

1
select device_id,gender,age,university,gpa from user_profile where university = '北京大学' or university = '复旦大学' or university = '山东大学'

SQL14 操作符混合运用

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

SQL15 查看学校名称中含北京的用户

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

高级查询

计算函数

SQL16 查找GPA最高值

1
select format(gpa,1) from user_profile where university = '复旦大学' order by gpa desc limit 1

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

1
2
3
4
select 
count(gender),round(avg(gpa),1)
from user_profile
where gender = 'male'

分组查询

SQL18 分组计算练习题

1
2
3
4
5
6
7
select 
gender,university,
count(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 分组过滤练习题

1
2
3
4
5
6
7
8
9
10
11
# HAVING子句 可以用于筛选聚合函数的结果，它作用于已经分组的数据。
# WHERE子句不能直接使用聚合函数
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 分组排序练习题

1
2
3
4
5
6
7
select
 university,
 round(avg(question_cnt),4) as avg_question_cnt
 from
 user_profile 
 group by university
 order by avg_question_cnt