目录
SQL9 查找除复旦大学的用户信息
题目:现在运营想要查看除复旦大学以外的所有用户明细,请你取出相应数据
示例:user_profile
id device_id gender age university province 1 2138 male 21 北京大学 Beijing 2 3214 male 复旦大学 Shanghai 3 6543 female 20 北京大学 Beijing 4 2315 female 23 浙江大学 ZheJiang 5 5432 male 25 山东大学 Shandong 根据输入,你的查询应返回以下结果:
device_id gender age university 2138 male 21 北京大学 6543 female 20 北京大学 2315 female 23 浙江大学 5432 male 25 山东大学
代码:
select device_id,gender,age,university from user_profile where university<>'复旦大学' ;
SQL10 用where过滤空值练习
题目:现在运营想要对用户的年龄分布开展分析,在分析时想要剔除没有获取到年龄的用户,请你取出所有年龄值不为空的用户的设备ID,性别,年龄,学校的信息。
示例:user_profile
id device_id gender age university province 1 2138 male 21 北京大学 Beijing 2 3214 male 复旦大学 Shanghai 3 6543 female 20 北京大学 Beijing 4 2315 female 23 浙江大学 ZheJiang 5 5432 male 25 山东大学 Shandong 根据输入,你的 查询应返回以下结果:
device_id gender age university 2138 male 21 北京大学 6543 female 20 北京大学 2315 female 23 浙江大学 5432 male 25 山东大学
代码:
select device_id,gender,age,university from user_profile where age is not null ;
SQL11 高级操作符练习(1)
题目:现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研,请你取出相关数据。
示例:user_profile
id device_id gender age university gpa 1 2138 male 21 北京大学 3.4 2 3214 male 复旦大学 4.0 3 6543 female 20 北京大学 3.2 4 2315 female 23 浙江大学 3.6 5 5432 male 25 山东大学 3.8 根据输入,你的查询应返回以下结果:
device_id gender age university gpa 3214 male 复旦大学 4.0 5432 male 25 山东大学 3.8
代码:
select device_id,gender,age,university,gpa from user_profile where gender='male' and gpa > 3.5 ;
SQL12 高级操作符练习(2)
题目:现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研,请你取出相关数据(使用OR实现)
示例:user_profile
id device_id gender age university gpa 1 2138 male 21 北京大学 3.4 2 3214 male 复旦大学 4.0 3 6543 female 20 北京大学 3.2 4 2315 female 23 浙江大学 3.6 5 5432 male 25 山东大学 3.8 根据输入,你的查询应返回以下结果:
device_id gender age university gpa 2138 male 21 北京大学 3.4 3214 male 复旦大学 4.0 6543 female 20 北京大学 3.2 5432 male 25 山东大学 3.8
代码:
select device_id,gender,age,university,gpa from user_profile where university='北京大学' or gpa > 3.7 ;
SQL13 Where in 和Not in
题目:现在运营想要找到学校为北大、复旦和山大的同学进行调研,请你取出相关数据。
示例:user_profile
id device_id gender age university gpa 1 2138 male 21 北京大学 3.4 2 3214 male 复旦大学 4.0 3 6543 female 20 北京大学 3.2 4 2315 female 23 浙江大学 3.6 5 5432 male 25 山东大学 3.8 根据输入,你的查询应返回以下结果:
device_id gender age university gpa 2138 male 21 北京大学 3.4 3214 male 复旦大学 4.0 6543 female 20 北京大学 3.2 5432 male 25 山东大学 3.8
代码:
select device_id,gender,age,university,gpa from user_profile where university in('北京大学','复旦大学','山东大学');
SQL14 操作符混合运用
题目:现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研,请你取出相应数据
示例:user_profile
id device_id gender age university province gpa 1 2138 male 21 北京大学 BeiJing 3.4 2 3214 male NULL 复旦大学 Shanghai 4 3 6543 female 20 北京大学 BeiJing 3.2 4 2315 female 23 浙江大学 ZheJiang 3.6 5 5432 male 25 山东大学 Shandong 3.8 根据输入,你的查询应返回以下结果:(该题对于小数点后面的0不需要计算与统计,后台系统会统一输出小数点后面1位)
device_id gender age university gpa 3214 male NULL 复旦大学 4 5432 male 25 山东大学 3.8
代码:
select device_id,gender,age,university,gpa
from user_profile
where (gpa>3.5 and university ='山东大学' ) or (gpa>3.8 and university ='复旦大学');
SQL15 查看学校名称中含北京的用户
题目:现在运营想查看所有大学中带有北京的用户的信息,请你取出相应数据。
示例:用户信息表:user_profile
id device_id gender age university gpa 1 2138 male 21 北京大学 3.4 2 3214 male 复旦大学 4.0 3 6543 female 20 北京大学 3.2 4 2315 female 23 浙江大学 3.6 5 5432 male 25 山东大学 3.8 6 2131 male 28 北京师范大学 3.3 根据示例,你的查询应返回如下结果:
device_id age university 2138 21 北京大学 6543 20 北京大学 2131 28 北京师范大学
代码:
select device_id,age,university
from user_profile
where university like '北京%' ;
SQL16 查找GPA最高值
题目:运营想要知道复旦大学学生gpa最高值是多少,请你取出相应数据
示例:某user_profile表如下:
id device_id gender age university gpa 1 2234 male 21 北京大学 3.2 2 2235 male NULL 复旦大学 3.8 3 2236 female 20 复旦大学 3.5 4 2237 female 23 浙江大学 3.3 5 2238 male 25 复旦大学 3.1 6 2239 male 25 北京大学 3.6 7 2240 male NULL 清华大学 3.3 8 2241 female NULL 北京大学 3.7 根据输入,你的查询应返回以下结果,结果保留到小数点后面1位(1位之后的四舍五入):
gpa 3.8
问题分解:
- 限定条件:复旦大学学生,
university='复旦大学'
; - gpa最高值:max(gpa);当然也可以按gpa降序排序,取第一名
细节问题:
- 表头重命名:as
完整代码:
# 方法1
# select max(gpa) as gpa
# from user_profile
# where university='复旦大学';
# 方法2
select gpa
from user_profile
where university='复旦大学'
order by gpa desc limit 1
SQL17 计算男生人数以及平均GPA
题目:现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少,用以辅助设计相关活动,请你取出相应数据。
示例:user_profile
id device_id gender age university gpa 1 2138 male 21 北京大学 3.4 2 3214 male 复旦大学 4.0 3 6543 female 20 北京大学 3.2 4 2315 female 23 浙江大学 3.6 5 5432 male 25 山东大学 3.8 6 2131 male 28 北京师范大学 3.3 根据输入,你的查询应返回以下结果,结果保留到小数点后面1位(1位之后的四舍五入):
male_num avg_gpa 4 3.6
问题分解:
- 限定条件为 男性用户;
- 有多少人,明显是计数,count函数;
- 平均gpa,求平均值用avg函数;
细节问题:
根据输出示例,有两个问题需要注意:
- 表头重命名,用as语法
- 浮点数的平均值可能小数点位数很多,按照示例保存一位小数,用round函数
完整代码:
select count(gender)
as male_num,
round (avg(gpa),1)
as avg_ghp
from user_profile
where gender='male';