牛客网-SQL刷题

一、SQL1 查询所有列

SELECT * FROM user_profile;

二、SQL2 查询多列

select device_id,gender,age,university from user_profile;

三、SQL3 查询结果去重

select distinct(university) from user_profile;

四、SQL4 查询结果限制返回行数

select device_id from user_profile limit 0,2;

五、SQL5 将查询后的列重新命名

select device_id as user_infos_example from user_profile limit 0,2;

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

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

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

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

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

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

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

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

十、SQL10 用where过滤空值练习

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

十一、SQL11 高级操作符练习(1)

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

十二、SQL12 高级操作符练习(2）

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

十三、SQL13 Where in 和Not in

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

十四、SQL14 操作符混合运用

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

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

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

十六、SQL16 查找GPA最高值

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

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

select count(*) male_num,avg(gpa) avg_gpa from user_profile where gender='male';

十八、SQL18 分组计算练习题

select gender,university,count(*) user_num,format(avg(active_days_within_30),1) avg_active_day,format(avg(question_cnt),1) avg_question_cnt  from user_profile group by gender,university;

十九、SQL19 分组过滤练习题

select university,format(avg(question_cnt),3) avg_question_cnt,format(avg(answer_cnt),3) avg_answer_cnt from user_profile group by university having avg(question_cnt)<5 or avg(answer_cnt)<20;

二十、SQL20 分组排序练习题

select university,format(avg(question_cnt),4) avg_question_cnt from user_profile group by university order by avg(question_cnt);

二十一、SQL21 浙江大学用户题目回答情况

select q.device_id,q.question_id,q.result from question_practice_detail q,user_profile u where q.device_id=u.device_id and u.university='浙江大学' order by q.question_id;

二十二、SQL22 统计每个学校的答过题的用户的平均答题数

select u.university,count(*)/count(distinct(q.device_id)) avg_answer_cnt from user_profile u,question_practice_detail q where u.device_id=q.device_id group by u.university order by u.university;

二十三、SQL23 统计每个学校各难度的用户平均刷题数

select u.university,qd.difficult_level,format(count(*)/count(distinct(qpd.device_id)),4) avg_answer_cnt from user_profile u,question_practice_detail qpd,question_detail qd where u.device_id=qpd.device_id and qpd.question_id=qd.question_id group by u.university,qd.difficult_level;

二十四、SQL24 统计每个用户的平均刷题数

select university,difficult_level,count(qpd.question_id)/count(DISTINCT qpd.device_id) as avg_answer_cnt from user_profile AS u inner join question_practice_detail AS qpd on u.device_id=qpd.device_id inner join question_detail AS qd on qpd.question_id=qd.question_id where university='山东大学' group by difficult_level;

二十五、SQL25 查找山东大学或者性别为男生的信息

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 计算25岁以上和以下的用户数量

select if(age is null or age<25,'25岁以下','25岁及以上') age_cut,count(device_id) from user_profile group by age_cut;

二十七、SQL27 查看不同年龄段的用户明细

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

二十八、SQL28 计算用户8月每天的练题数量

select substr(date,9,2),count(*) as question_cnt from question_practice_detail where date between '2021-08-01' and '2021-08-31' group by date;

二十九、SQL29 计算用户的平均次日留存率

select count(q2.device_id)/count(q1.device_id) as avg_ret from (select distinct device_id,date from question_practice_detail) q1 left join (select distinct device_id,date from question_practice_detail) q2 on q1.device_id=q2.device_id and q2.date=date_add(q1.date,interval 1 day);

三十、SQL30 统计每种性别的人数

select case substr(profile,15) when 'male' then 'male' else 'female' end as gender,count(*) from user_submit group by gender;

三十一、SQL31 提取博客URL中的用户名

select device_id,substr(blog_url,11) as user_name from user_submit;

三十二、SQL32 截取出年龄

select substr(profile,12,2) as age,count(*) from user_submit group by age;

三十三、SQL33 找出每个学校GPA最低的同学

select up.device_id,up.university,up.gpa from user_profile as up right join (select university,min(gpa) min_gpa from user_profile group by university) as temp on up.university=temp.university and up.gpa=temp.min_gpa order by up.university;

三十四、SQL34 统计复旦用户8月练题情况

select
    up.device_id,
    up.university,
    count(question_id) as question_cnt,
    sum(if (qpd.result = 'right', 1, 0)) as right_question_cnt
from
    user_profile as up
    left join question_practice_detail as qpd on up.device_id = qpd.device_id
    and qpd.date between '2021-08-01' and '2021-08-31'
where
    up.university = '复旦大学'
group by
    up.device_id;

三十五、SQL35 浙大不同难度题目的正确率

select qd.difficult_level,sum(if(qpd.result='right',1,0))/count(qpd.question_id) as correct_rate from user_profile as up inner join question_practice_detail as qpd on up.device_id=qpd.device_id inner join question_detail as qd on qpd.question_id=qd.question_id where up.university='浙江大学' group by qd.difficult_level order by correct_rate ;    # 此处只能使用inner join，不能使用left join

left join和inner join的区别在于返回不同、数量不同、记录属性不同。inner join：inner join只返回两个表中联结字段相等的行;left join：left join返回包括左表中的所有记录和右表中联结字段相等的记录。
一、left join和inner join的区别
1.返回不同
inner join：inner join只返回两个表中联结字段相等的行。
left join：left join返回包括左表中的所有记录和右表中联结字段相等的记录。
2.数量不同
inner join：inner join的数量小于等于左表和右表中的记录数量。
left join：left join的数量以左表中的记录数量相同。
3.记录属性不同
inner join：inner join不足的记录属性会被直接舍弃。
left join：left join不足的记录属性用NULL填充。

三十六、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 查找后降序排列

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

三十九、SQL39 21年8月份练题总数

select count(distinct device_id) as did_cnt,count(question_id) as question_cnt from question_practice_detail where month(date)=8;

四十、SQL100 确定最佳顾客的另一种方式（二）

select c.cust_name,sum(oi.quantity*oi.item_price) as total_price from OrderItems as oi inner join Orders as o on oi.order_num=o.order_num  inner join Customers as c on o.cust_id=c.cust_id group by oi.order_num,o.cust_id,c.cust_name having total_price>1000;

四十一、SQL103 返回产品名称和与之相关的订单号

select
    p.prod_name,
    o.order_num
from
    Products as p
    left join OrderItems as o on p.prod_id = o.prod_id
union
select
    p.prod_name,
    o.order_num
from
    Products as p
    right join OrderItems as o on p.prod_id = o.prod_id
order by prod_name;

四十二、SQL104 返回产品名称和每一项产品的总订单数

select
    p.prod_name,
    cast(sum(if (o.order_num is NULL, 0, 1)) AS SIGNED)
from
    Products as p
    left join OrderItems as o on p.prod_id = o.prod_id
group by
    p.prod_name
union
select
    p.prod_name,
    cast(sum(if (o.order_num is NULL, 0, 1)) AS SIGNED)
from
    Products as p
    right join OrderItems as o on p.prod_id = o.prod_id
group by
    p.prod_name
order by
    prod_name;