1.查找最晚入职员工的所有信息
注意：同一天入职的可能有多个人
法一：利用max函数找到最大的hire_date对应的员工信息（最优）
法二：利用order by 和 limit找到最大的一个hire_date对应的员工信息
2.查找入职员工时间排名倒数第三的员工所有信息
注意：同一天入职的可能有多个人
法一：利用order by 和 limit找到倒数第三的hire_date对应的员工信息
法二：同一天入职的如果有多个人，那么倒数第三的员工并不是按照时间日期排的，更严谨的做法，是利用子查询结合distincnt 和order by 和 limit找到入职日期倒数第三的hire_date对应的员工信息
3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号
以salary为主表,进行内连接查找
4.查找所有已经分配部门的员工的last_name和first_name
以dept_emp为主表，进行内连接查找
5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
外连接
select e.last_name,e.first_name,d.dept_no
from employees as e left join dept_emp as d
on e.emp_no=d.emp_no;
6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
注意内连接与where并列查询的区别：
内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。本题从效果上看两个方法没区别。
7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
解决本题的关键在于要记得，sql语句的执行顺序,先group by emp_no,按员工编号进行分组，然后执行select中的count(emp_no) 最后执行having中的过滤条件
一个sql查询语句的主要组成部分为
select --- from --- where --- group by --- having --- order by --- limit --- ;
执行顺序：
- from
- where
- group by
- select
- having
- ordre by
- limit
select emp_no,count(emp_no) as t from salaries group by emp_no having t>15;
8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
在单表的时候使用distinct，多表的时候使用group by，虽然一般使用group by ，但还是要知道distinct的用法
单表用distinct:
select distinct salary from salaries where to_date='9999-01-01'
order by salary desc;
多表用group by:
select salary from salaries where to_date='9999-01-01'
group by salary order by salary desc;
9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'
select d.dept_no,d.emp_no,s.salary from dept_manager as d inner join
salaries as s on d.emp_no=s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
10.获取所有非manager的员工emp_no
方法一：
select emp_no from employees where emp_no not in
(select emp_no from dept_manager)
方法二：
select emp_no from (select * from employees as e left join dept_manager as d
on e.emp_no=d.emp_no) where dept_no is NUll
11.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
select e.emp_no,m.emp_no from dept_emp as e
inner join dept_manager as m
on e.dept_no=m.dept_no
and e.emp_no<>m.emp_no
and e.to_date='9999-01-01'
and m.to_date='9999-01-01';
12.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
1.先内连接两张表
2.按dept_no进行分组
3.找出组内最大薪水的成员
select d.dept_no,d.emp_no,max(s.salary) from dept_emp as d inner join salaries as s
on d.emp_no=s.emp_no where d.to_date="9999-01-01" and s.to_date="9999-01-01"
group by d.dept_no
13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
select title,count(title) as t from titles group by title having t>=2;
14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
select title ,count(distinct emp_no) as t from titles
group by title having t>=2;
15.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
select * from employees where emp_no%2=1
and last_name<>"Mary"
order by hire_date desc;
16.统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg
select t.title,avg(s.salary) from titles as t
inner join salaries as s on t.emp_no=s.emp_no
where t.to_date="9999-01-01" and s.to_date="9999-01-01"
group by t.title;
17.获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no,salary from salaries order by salary desc limit 1,1;
改进版：针对于薪水相同的员工，上述做法无法真正得到按薪水排第二的员工信息
select emp_no,salary from salaries where to_date='9999-01-01'
and salary =(select distinct salary from salaries order by salary desc limit 1,1);
18.查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
从薪水不是第一的里面找最大值
select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees as e inner join salaries as s
on e.emp_no=s.emp_no where s.to_date='9999-01-01' and
s.salary<>(select max(salary) from salaries where to_date='9999-01-01')
19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name
from employees as e left join
(select * from dept_emp inner join departments on dept_emp.dept_no=departments.dept_no) as d
on e.emp_no=d.emp_no
20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
select (max(salary)-min(salary)) as growth from salaries where emp_no='10001'
更严谨的做法：
SELECT (
(SELECT salary FROM salaries WHERE emp_no =`10001` ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no =`10001` ORDER BY to_date ASC LIMIT 1)
) AS growth
21.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
select a.emp_no,(b.salary-c.salary) as growth
from employees as a
inner join salaries as b on a.emp_no=b.emp_no and b.to_date='9999-01-01'
inner join salaries as c on a.emp_no=c.emp_no and c.from_date=a.hire_date
order by growth
22.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
- 将员工与其薪水内连接成一张表，该表为一个员工有多种薪水
- 将部门表与上表内连接，按部门编号分组
- 每个部门中，多种薪水的员工对应多个部门编号，count部门编号即为该部门内所有员工的涨薪次数之和
select d.dept_no,d.dept_name,count(d.dept_no) as sum from departments as d inner join
(select * from dept_emp as a inner join salaries as b
on a.emp_no=b.emp_no) as c on d.dept_no=c.dept_no
group by d.dept_no
网友评论