SQL基本语句练习

目录

                  SQL17 (聚合函数count avg round 应用)

 SQL18 (聚合函数count avg  应用,及分组查询)

 SQL19 分组过滤(where与having的区别)

SQL21 多表联立查询(涉及到两张表,查询共同信息)

SQL22(多表联立查询进阶版)

问题分解：

SQL23 (三表联立查询)

SQL25（分别查询，叠加输出）

问题分解：

SQL26（CASE语句，when\then的使用）

SQL33 （主查询&&子查询）

---

SQL17 (聚合函数count avg round 应用)

查询男性用户人数以及平均GPA(结果保留到小数点后面一位)

输入

 SQL语句

select
  count(gender) as male_num,
  round(avg(gpa), 1) as avg_gpa
from user_profile where gender="male";

 结果展示

 SQL18 (聚合函数count avg  应用,及分组查询)

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

输入

 SQL语句

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

输出

 SQL19 分组过滤(where与having的区别)

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。(根据示例，你的查询应返回以下结果，请你保留3位小数(系统后台也会自动校正)，3位之后四舍五入)

细节:聚合函数作为筛选条件时,不能用where,而是用having

 SQL语句

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

SQL21 多表联立查询(涉及到两张表,查询共同信息，也可以对同一张表查多次)

对同一张表进行多次查询的题目

如力扣181 超过经理的收入

 在这里需要对一张表进行多次的查询，需要比对员工与其经理的薪资，从而输出结果，在这里我们可以将同一张表当作两张表来操作即可，从而确定最终的结果。

代码如下所示：

select l1.name as Employee from 
Employee as l1,
Employee as l2
where l1.managerId=l2.id
and l1.salary>l2.salary

各种join的区别

1. INNER  JOIN 产生的结果是AB的交集

2. LEFT (OUTER)　JOIN   产生表A的完全集，而表B中匹配的则有值，没有匹配的则以null值取代.

3.RIGHT（OUTER）  JOIN 产生表B的完全集，而表A中匹配的则有值，没有匹配的则以null值取代

4. FULL (OUTER)   JOIN  产生A和B的并集，对于没有匹配的记录，以null值做为值

可以通过is null将 没有匹配的值找出来；

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

限定条件：来自浙江大学的用户，学校信息在用户画像表，答题情况在用户练习明细表，因此需要通过device_id关联两个表的数据； 方法1：join两个表，用inner join(组合表)，条件是on up.device_id=qpd.device_id and up.university='浙江大学' 

输入:

 SQL语句

select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id and up.university='浙江大学'
order by question_id 

SQL22(多表联立查询进阶版)

请你写SQL查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！

问题分解：

• 限定条件：无；
• 每个学校：按学校分组，group by university
• 平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。
• 表连接：学校和答题信息在不同的表，需要做连接(inner join)

输入

SQL

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

SQL23 (三表联立查询)

个人感觉关键在于如何联立三个表,即用on语句建立起必要的关联性,再进行查询

问题:计算每个学校用户不同难度下的用户平均答题题目数

• 限定条件：无；
• 每个学校：按学校分组group by university
• 不同难度：按难度分组group by difficult_level
• 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
• 来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

输入表

 

 

 SQL语句,重要的便是on语句,将表与表联立起来

select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
 
left join user_profile as up
on up.device_id=qpd.device_id
 
left join question_detail as qd
on qd.question_id=qpd.question_id
 
group by university, difficult_level

SQL25（分别查询，叠加输出）

题目：分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，结果不去重

问题分解：

• 限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；
• 分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行（去重了），要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

SQL语句

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（CASE语句，when\then的使用）

输入：

 题目：

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

本题注意：age为null 也记为 25岁以下

要求输出为:

 SQL语句

select case when age<25 or age is null then '25岁以下'
when age>=25 then '25岁及以上'
end age_cut,count(*)number
from user_profile group by age_cut

case 语句拓展练习(用到了模糊查询）

题目

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。返回的结果集请按照employee_id排序。

select employee_id,case when name not like 'M%'and employee_id%2!=0 then salary
else 0
end  bonus
from Employees
order by employee_id

SQL33 （主查询&&子查询）

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa，并按university进行升序排序。

输入:

这里的子查询，便是在同一个大学里面挑出GPA最低的值，和学生的id，非常牛掰 

SELECT
    device_id,
    university,
    gpa
FROM user_profile u
WHERE gpa = 
    (SELECT MIN(gpa)
     FROM user_profile
     WHERE university = u.university)
ORDER BY university

拓展：子查询

 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

 查询返回所有不订购任何东西的客户，返回的不止一行

SELECT Name 'Customers'
FROM Customers
WHERE Id NOT IN(
    SELECT CustomerId 
    FROM Orders
)

力扣178 窗口函数 dense_rank()使用（用于排名序号）

 使用窗口函数记录排序序号

select 
score,
dense_rank() over (order by score desc) as 'rank'
from scores;

力扣1795 行转列问题，采用union解决

 需要将原表的列，输出为行，则需采用union查询，分别查询store1,store2,store3查询得到不同的数据，全部结合放在一张表内。

select product_id,'store1' as store,store1 as price from Products where store1 is not null
union all
select product_id,'store2' as store,store2 as price from Products where store2 is not null
union all
select product_id,'store3' as store,store3 as price from Products where store3 is not null

力扣1158 市场分析I LEFT JOIN 

采用left join 的关联方式，其中有个小知识点

外连接时要注意where和on的区别，on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容，where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。如果这里用的是 where year(order_date)='2019' 那么得到的结果将会把不满足条件的user_id为3，4的行给删掉。用on的话会保留user_id为3，4的行。

具体的代码如下

select Us.user_id as 'buyer_id' ,Us.join_date,count(Os.item_id) as 'orders_in_2019' from 
Users as Us left join Orders as Os 
on Us.user_id=Os.buyer_id
and year(Os.order_date)=2019
group by Us.user_id

力扣197 窗口函数DATEDIFF、ADDDATE的使用

采用DATEDIFF窗口函数，只要减去二者日期即可 

select l1.id from 
weather l1,
weather l2
where l1.Temperature>l2.Temperature
and
DATEDIFF(l1.recordDate,l2.recordDate)=1

使用ADDDATE窗口函数，SQL如下所示：

SELECT
	w2.id 
FROM
	Weather w1,
	Weather w2 
WHERE
	w2.recordDate = ADDDATE( w1.recordDate, INTERVAL 1 DAY ) 
	AND w2.temperature > w1.temperature

力扣1484 采用GROUP_CONCAT 拼接字符串

GROUP_CONCAT(attribute1 ORDER BY attribute2 SEPARATOR '-') attribute1 表明按照 sell_date 分组后的组内各值按照 attribute1 进行连接；

attribute2 表明连接的顺序按照 attribute2 进行排序

SEPARATOR 指明各个 attribute1 之间使用哪个字符进行连接。

SELECT
    sell_date,
    COUNT(DISTINCT(product)) num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date

力扣601 体育馆的人流量，采用row_number() over窗口函数

已知题目求id连续三行，这里就用到row_number() over窗口函数

select id,visit_date,people
from(
    select *,count(*)over(partition by t_rank) t2_rank
    from(
        select *,id-row_number()over(order by id) t_rank
        from stadium  
        where people>99
        )t
    )t2
where t2.t2_rank>2
order by id

具体解析链接力扣

力扣1667 修复表中的名字

采用CONCAT拼接字符串，UPPER大写转换，LOWER小写转换，Left截取左边开始的字符串，RIGHT截取从右边往左的字符串

select user_id, CONCAT(UPPER(left(name, 1)), LOWER(RIGHT(name, length(name) - 1))) as name
from Users
order by user_id

力扣1084 count小技巧，容易出错

 思路就是根据商品id分组，左连接，统计售出日期在一定区间内的个数，与总个数进行对比，从而统计出只在这个区间销售的商品有哪些

这里值得注意的是count的条件用法。举个例子count(age > 20 or null)这个语句，里面or null必须加，否则就等价于count(*)了

select sales.product_id as product_id, product.product_name as product_name
from sales left join product on sales.product_id = product.product_id
group by product_id
having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)