SQL笔试训练

查询结果去重

两种答案

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

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

三种答案

用where过滤空值练习

三种答案

查询NULL时，不能使用比较运算符(=或者< >)，需要使用IS NULL运算符或者IS NOT NULL运算符。

操作符混合运用

我这里写大括号方便区分,表示两个条件或两个条件

由于and的优先级大于or,所以可以省略括号，如果不确定可以用()来改变运算的优先级

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

考点 like

• like '%北京%'列名包括北京的字样
• like '北京%' 列名北京开头
• like '%北京' 列名北京结尾

匹配串中可包含如下四种通配符

• _：匹配任意一个字符；
• %：匹配0个或多个字符；
• [ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
• [^ ]：不匹配[ ]中的任意一个字符。

实例

查找GPA最高值

计算男生人数以及平均GPA

细节问题：根据输出示例，有两个问题需要注意

• 表头重命名，用as语法
• 此题要注意的是暗含条件，保留一位小数,使用ROUND()函数舍入到指定的长度或精度

分组计算练习题

分组过滤练习题

问题分解

限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；

按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university，这里特别注意要对学校进行分组这个隐藏条件

分组排序练习题

问题分解

• 限定条件：无；
• 不同大学：按学校分组 group by university
• 平均发帖数：avg(question_cnt)
• 升序排序：order by avg_question_cnt

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

示例：user_profile

问题分解

限定条件：来自浙江大学的用户，学校信息在用户画像表，答题情况在用户练习明细表，因此需要通过device_id关联两个表的数据；

方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university='浙江大学'

方法2：先从画像表找到浙江大学的所有学生id列表where university='浙江大学'，再去练习明细表筛选出id在这个列表的记录，用where in

两种答案

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

问题分解

限定条件：无；

每个学校：按学校分组，group by university

平均答题数量：在每个学校的分组内，该学校用户答题总次数除以答过题的不同用户个数 count(question_id) / count(distinct ques.device_id)

这个题的难点就在这里，要知道整个结果是把两个表联合在一起然后按学校分组了的，所以这个时候question_id的数量就是每个学校用户答题总次数，而distinct ques.device_id的总数量就答过题的不同用户个数,最核心的就是要站在“整个结果是把两个表联合在一起然后按学校分组了的”这个前提下去想

表连接：学校和答题信息在不同的表，需要做连接

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

题库练习明细表：question_practice_detail

表：question_detail

问题分解

限定条件：无；

每个学校：按学校分组group by university

不同难度：按难度分组group by difficult_level

平均答题数：总答题数除以总人数count(ques.question_id) / count(distinct ques.device_id)

由于结果需要三个表中的多列数据，因此进行关联

注：由于计算平均答题数的数据均来源于question_practice_detail，因此在联结时候应保全该表，以该表为本体联结另外两张表，通过device_id和question_id连接。

知识点补充

合并表格的过程中，存在几种合并方式，下面简单介绍一下这些方式（此处以两张表格的情况为例解释）

inner join 最终结果为在两张表格中都匹配上的数据项

left join 最终结果为inner join结果加上左侧表格（此处为第一张表）未匹配上的数据

right join 最终结果为inner join结果加上右侧表格（此处为第一张表）未匹配上的数据

full join 最终结果为inner join加上左侧和右侧两张表中未匹配上的数据

备注：只写一个join时默认为inner join模式

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

问题分解

限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；

分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行（因为要求不去重，而用union 会去重），要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

union 和union all的区别

• union是合并两个查询语句的结果集，并排除重复项
• union all是不排除重复项的（符合题目要求）

union使用前提

使用union合并两个表时，需要两个表的结果集字段完全一样；

• 表一（SELECT device_id,gender,age,gpa ）;
• 表二（SELECT device_id,gender,age,gpa）；

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

上面的一定要记得进行分组，这个地方我一开始就忘记了，然后end后面写的是第一列和第二列的别名，不过第二列还要统计总数量