SQL笔试

SQL

• 查询结果去重

  select distinct university from user_profile;
  

• 查看前2个用户明细设备ID数据

  select device_id from  user_profile limit 2;
  

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

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

• 取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

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

• 查看所有大学中带有北京的用户的信息

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

• 复旦大学学生gpa最高值

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

• 查询男生有几人和平均gpa(结果保留到小数点后面1位(1位之后的四舍五入))

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

• 取出平均发贴数低于5的学校或平均回帖数小于20的学校

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

• 大学的用户平均发帖情况(按照平均发帖情况进行升序排列)

select distinct university,round(avg(question_cnt),4)avg_question_cnt from user_profile group by university order by avg_question_cnt asc;

• 多表查询：查看所有来自浙江大学的用户题目回答明细情况（question_practice_detail，user_profile）

  select q.device_id,question_id,result from question_practice_detail q join user_profile u on q.device_id = u.device_id where u.university = "浙江大学";
  
  

22,23,24

• 查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据,结果不去重。

  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";
  
  

• 将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量(age为null 也记为 25岁以下)

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;

• 计算出2021年8月每天用户练习题目的数量

select day(date)day,count(question_id)question_cnt from question_practice_detail 
where year(date)='2021' and month(date)='08' group by day;

• 返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date）

  select * from Orders where date_format(order_date,'%Y-%m')  = "2020-01" order by order_date 
  
  

日期函数：

1. DAYOFWEEK(date)

   返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

2. WEEKDAY(date)

   返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

3. DAYOFMONTH(date)

   返回date的月份中日期，在1到31范围内。

4. DAYOFYEAR(date)

   返回date在一年中的日数, 在1到366范围内。

5. MONTH(date)

   返回date的月份，范围1到12

6. DAYNAME(date)

   返回date的星期名字。

7. MONTHNAME(date)

   返回date的月份名字。

8. QUARTER(date)

   返回date一年中的季度，范围1到4。

9. WEEK(date)

   对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。

10. WEEK(date,first)

    2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
    如果第二个参数是0，星期从星期天开始，
    如果第二个参数是1，从星期一开始。

11. YEAR(date)

    返回date的年份，范围在1000到9999。

12. HOUR(time)

    返回time的小时，范围是0到23。

13. MINUTE(time)

    返回time的分钟，范围是0到59。

14. SECOND(time)

    回来time的秒数，范围是0到59。

15. PERIOD_ADD(P,N)

    增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

16. PERIOD_DIFF(P1,P2)

    返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。

• 计算用户的平均次日留存率:查看用户在某天刷题后第二天还会再来刷题的平均概率***(困难)***

  select count(q2.device_id) / count(q1.device_id) 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);
  
  

  因为使用的是q1左级联q2，所以q1的所有信息是显示的；而q2中只显示留存的信息，否则为null。

  最后，分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率。

  注意，MySQL中 COUNT在对列进行计数时不统计值为 null的条目

• 统计每个性别的用户分别有多少参赛者

select substring_index(profile,',',-1) gender,count(device_id) number 
from user_submit group by gender;

• 其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成

  select cust_id,cust_name,
  upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3))) user_login from Customers
  
  

• 找到每个学校gpa最低的同学

select device_id,university,gpa from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university) order by university asc

• 复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数

  select u.device_id,university,count(question_id)question_cnt,
  sum(if(q.result = 'right',1,0))right_question_cnt
  from user_profile u 
  join question_practice_detail q on u.device_id = q.device_id and month(q.date) = '08'
  where u.university = '复旦大学' group by u.device_id;
  
  

• 浙大不同难度题目的正确率(困难35）

  select difficult_level,sum(if(result = 'right',1,0)) / count(q.question_id) correct_rate
  from(question_practice_detail q join user_profile u on u.device_id = q.device_id 
  join question_detail qu on qu.question_id = q.question_id) 
  where university = '浙江大学'
  group by difficult_level
  order by correct_rate asc
  
  

• 查找后多列排序

  不写默认为升序

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

• 查找后降序排列

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

• 返回在描述中以先后顺序同时出现 toy 和 carrots 的产品

  select * from Products where prod_desc like "%toy%carrots%"