sql modify 会丢失数据么_面试数据分析会遇到的SQL题

7746b4ebfc4ce32314a708fc2fd97118.png

「1」说在前面

数据存放在数据库里,以表的形式分门别类。

宜家的商品(数据)放在宜家的仓库(数据库)里,以货位的形式分门别类。

在宜家,可以通过商品上的编号,查到商品在仓库的排号和位号,取到商品。

SQL 语言是一种通用的数据库查询语言,可以通过 SQL 语言从数据库里获取到想要的数据内容。

巧妇难为无米之炊。

这是数据分析师拿到“米”的重要技能,所以说 SQL 语言对于数据分析从业者来说很重要。

SQL 语言在数据分析工作中有多重要?​www.zhihu.com
96d4e28acc69624728a0a251a2b5515e.png

但是,数据库(DBMS)有很多种,比如:MySQL、Oracle、SQLserver 等。针对每种数据库,有通用的 SQL 语句和针对具体数据库的解决方案。

如何学习 SQL 语言?​www.zhihu.com
990776f555dc6df696c7fe213ff91996.png

「2」SQL 面试题 - 技能类

ff80ebeb5206bec66b401e48e568e95c.png
假设数据库里有上述4张表,基于这4张表取数。

2.1 - 查询姓马员工的员工信息;

select *
from table_staff
where staff_name like '马%';

2.2 - 查询岗位要求(job_requirements)里提到 SQL 的岗位信息;

select *
from table_post
where job_requirements like '%SQL%';

2.3 - 查询“北京佰初数据有限公司”各个岗位上最高薪水和最低薪水;

select post_id, max(salary) as max_salary, min(salary) as min_salary
from table_staff
where company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
group by post_id;

# 此题还可以用联结语句(join)完成 #

2.4 - 查询“北京佰初数据有限公司”各个职级(post_grade)上的最高薪水、最低薪水、平均薪水;

# 思路 #

  • 关键是理清楚各表之间的关系;

81c9297c09b39609b66e84c4f3cc325b.png
  • 通过「员工表」和「公司表」联结筛选出“北京佰初数据有限公司”的数据;
  • 通过「员工表」和「岗位表」联结获取员工职级的数据;
  • 参考 2.3 题获取各个职级的最高薪水、最低薪水、平均薪水;

# 可在解题后留言答案,带上题目编号 #

2.5 - 查询“北京佰初数据有限公司”每个部门(department)的人数;

select b.department_id, count(distinct a.staff_id) as staff_num
from 
table_staff as a
left join 
table_post as b 
on a.post_id = b.post_id
where a.company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
group by b.department_id;

# 是否可以通过用「员工表」联结「部门表」完成?为什么? #

2.6 - 将“北京佰初数据有限公司”的员工两两组队,取出所有可能的组合;

# 可在解题后留言答案,带上题目编号 #

# 解题思路参考题 #

问题:一个叫 team 的表,里面只有一个字段 name,name 字段下 team_1,team_2,team_3,team_4 四个球队,选择两个球队比赛,用一条 SQL 取出所有可能的比赛组合。

select a.name, b.name
from team as a, team as b 
where a.name < b.name;

2.7 - 查询所有公司的所处行业(industry)、CEO名字、员工数量、总薪资;

# 可在解题后留言答案,带上题目编号 #

2.8 - 查询“北京佰初数据有限公司”平均薪资高于10000的岗位和平均薪资,按岗位id升序排序;

select post_id, avg(salary)
from table_staff
where company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
group by post_id
having avg(salary) > 10000
order by post_id;

2.9 - 查询“北京佰初数据有限公司”至少有5名员工的岗位和员工数;

select post_id, count(staff_id) as staff_num
from table_staff
where company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
group by post_id
having count(staff_id) >= 5;

# 思考1:having 后面可以写成 staff_num >= 5 吗?为什么? #

2.10 - 查询“北京佰初数据有限公司”的岗位名为“数据分析师”且薪资高于15000的员工id、员工名字、薪资;结果按薪资降序排列,如果薪资相同,就按员工id升序排列;

select staff_id, staff_name, salary
from table_staff
where 
  salary > 15000
  and post_id in (select post_id from table_post where post_name = '数据分析师')
  and company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
order by salary DESC, staff_id ASC;

2.11 - 查询“北京佰初数据有限公司”各岗位的平均薪资,按平均薪资的降序排序;

select post_id, avg(salary) as avg_salary
from table_staff
where company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
group by post_id
order by avg_salary DESC;

# 思考:order by 后面可以写成 avg(salary) DESC 吗?为什么? #

2.12 - 完成下图的转换;

05149b061f9ce11c4d18cb8594718c86.png
select 
  company_id, 
  (select staff_name from table_staff as a where a.post_id = 'P001' and a.company_id = b.company_id) as P001,
  (select staff_name from table_staff as a where a.post_id = 'P002' and a.company_id = b.company_id) as P002,
  (select staff_name from table_staff as a where a.post_id = 'P003' and a.company_id = b.company_id) as P003,
  (select staff_name from table_staff as a where a.post_id = 'P004' and a.company_id = b.company_id) as P004
from table_staff as b 
group by company_id;

2.13 - 查询每个公司薪资排名前三的岗位id、薪资,按公司id升序,按薪资降序排序;

select t1.company_id, t1.post_id, t1.salary
from table_staff as t1
where (select count(1) from table_staff as t2 where t2.salary > t1.salary and t2.company_id = t1.company_id) < 3
order by t1.company_id, t1.salary DESC;

2.14 - 取出下图的数据;

3cd58a2a1eb2b6107e021700df07d079.png

# 可在解题后留言答案,带上题目编号 #

# 参考 2.12 题 #

2.15 - 假设4张表存放在 MySQL 数据库中,查询“北京佰初数据有限公司”过去7天每天的新入职员工数量,按入职日期(date_of_entry)倒序排列;

# 可在解题后留言答案,带上题目编号 #

# 类似互联网公司查询过去一周每天的访客数 #

2.16 - 假设4张表存放在 MySQL 数据库中,查询“北京佰初数据有限公司”2019年5月每天的新入职员工数量,按入职日期(date_of_entry)倒序排列;

# 可在解题后留言答案,带上题目编号 #

2.17 - 查询“北京佰初数据有限公司”每个岗位上薪资高于岗位平均薪资的员工姓名;

# 窗口函数的使用 #

select staff_name
from (select 
           *, 
           avg(salary) over (partition by post_id) as avg_salary
      from table_staff
      where company_id in (select company_id from table_company where company_name = '北京佰初数据有限公司')
      ) as a
where salary > avg_salary;

2.18 - 查询“北京佰初数据有限公司”每个部门里薪资高于部门平均薪资的员工姓名和部门名字(department_name);

# 窗口函数、聚合函数、联结表、子查询的使用 #

「3」SQL 面试题 - 性能类

3.1 - 写出 2.8 题语句的实际执行顺序?

# 思路 #

  • SQL 语言并不按照语法顺序来执行;
  • SQL 语句有一个让大部分人都感到困惑的特性,就是 SQL 语句的执行顺序跟其语句(变量传参、循环语句、迭代、调用函数 等,这些编程思维惯式)的语法顺序完全不一样;
  • SQL 语法顺序:

88ccb91af1cc785c6e6afd6f8c1e0358.png
  • SQL 执行顺序:

80c507c2cb5da4edd4c1504c0e70a95a.png
  • 注意点:
  1. FROM 是 SQL 语句执行第一步,而不是 SELECT【因为数据库在执行 SQL 语句的第一步是将数据加载到数据缓冲区】
  2. SELECT 在 FROM 和 GROUP BY 之后执行,而在 ORDER BY 之前执行
  3. 如果要对 UNION 的总表排序,ORDER BY 放在 UNION 之后

3.2 - 我们公司每天产生几千万条新的数据,数据库体量很大。为了提高查询效率,写 SQL 查询数据时,应该注意哪些问题?

# 主要思路是避免全表扫描,如何避免全表扫描呢? #


  • 入门可以先看:如何学习 SQL 语言?
  • 本文是基于难度较高的 SQL 面试题整理而成,耗时 5 个小时完成,原创不易,求「点赞」
  • 更多 SQL 面试题会不断补充,还是基于这 4 张表;
  • 希望投身数据浪潮的盆友,可以看这篇回答:3个月拿到数据分析offer~
  • 欢迎沟通,接受正向交流~
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值