SQL练习题-1

员工表结构：

部门表结构：

部门员工表结构：

部门经理表结构：

薪资表结构：

1.查找最晚入职员工的所有信息

分析：最晚入职即为"hire_date"最大

1）子查询，基于max()函数：

select * from employees where hire_date = (select max(hire_date) from employees);

2）基于limit关键字：(个人认为有局限性，需要考虑数据集结构，若有多人都是最晚入职则不适用）

select * from employees order by hire_date desc limit 1;

2.查找入职员工时间排名倒数第三的员工所有信息

分析：对入职时间降序排列 order by，取第三名，可用LIMIT

LIMIT m,n : 表示从第m+1条开始，取n条数据。

select * from employees where hire_date = (select distinct hire_date from employees order by hire_date desc limit 2,1)

其中考虑到每天可能有多个员工入职，所以用distinct去重，否则将得不到想要的结果。

3.查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

分析：考察连接，多表联合查询

1）基于join连接：

select s.*, d.dept_no from salaries as s inner join dept_manager as d on s.emp_no = d.emp_no where s.to_date='9999-01-01' and d.to_date = '9999-01-01';

2）联合查询

select s.*, d.dept_no from salaries s, dept_manager d where s.emp_no = d.emp_no and s.to_date = '9999-01-01' and d.to_date = '9999-01-01';

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no

分析：用到“部门员工表”和“员工表”，join连接员工表的last_name和first_name

select e.last_name, e.first_name, d.dept_no from dept_emp d inner join employees e on d.emp_no = e.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

分析：与题目4不同的是包括没有分配部门的员工也就是要没有分配部门的员工的dept_no显示null

select e.last_name, e.first_name, d.dept_no from employees e left join dept_emp d on e.emp_no = d.emp_no；

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

分析：入职时候薪水即“员工表”的hire_date和“薪水表”的from_date相等，因为薪水表中一个员工薪水可能有多次涨幅。降序则使用order by

select e.emp_no, s.salary from employees e, salaries s where e.emp_no = d.emp_no and e.hire_date = d.from_date order by e.emp_no desc;

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

分析：用group by对emp_no分组，count()函数限制条件，要使用having过滤条件

select emp_no,count(*) as t from salaries group by emp_no having count(*)>15;

8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

分析：限制当前条件即to_date='9999-01-01'，对相同薪水用distinct去重，order by降序排序

select distinct salary from salaries where to_date='9999-01-01' order by salary desc;

9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

分析：join连接两表，限制条件是emp_no相等，当前manager所以限定d.to_date='9999-01-01', 当前薪水所以限制s.to_date='9999-01-01'

select d.dept_no, d.emp_no, s.salary from dept_manager d inner join salaries 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

分析：找到在“员工表”里的emp_no而不在“部门经理表”的emp_no

1）基于not in：

select emp_no from employees where emp_no not in (select distinct emp_no from dept_manager) ;

2）基于left join：

select e.emp_no from employees e left join dept_manager d on e.emp_no=d.emp_no where d.dept_no is null;