以赛代练了直接。
1 查找最晚入职员工所有信息
select *
from employees
where hire_date=(select max(hire_date) from employees);
2 查找入职时间排名倒数第三
select *
from employees
where hire_date=(
select distinct hire_date
from employees
order by hire_date desc
limit 2,1
);
3 当前部门领导当前薪水以及对应部门编号
select s.*,d.dept_no
from salaries s
inner join dept_manager d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01'
and d.to_date='9999-01-01';
4 所有已分配部门的员工的last_name/first_name以及dept_no
select last_name,first_name,dept_no
from employees e
inner join dept_emp d
on e.emp_no=d.emp_no;
5 所有员工first_name和last_name以及部门编号
select last_name,first_name ,d.dept_no
from employees e
left join dept_emp d
on e.emp_no=d.emp_no
6 查找所有员工入职时候薪水信息
select e.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no =s.emp_no
where e.hire_date=s.from_date
order by e.emp_no desc
7 薪水涨幅超过15次员工号emp_no以及次数t
select s.emp_no,count(*) t
from salaries s
group by s.emp_no
having count(*)>15
8 所有员工当前薪水具体情况
相同仅显示一次,逆序输出
select distinct salary
from salaries s
where s.to_date='9999-01-01'
order by salary desc
distinct 效率比较低
方式二 使用groupby分类
select salary
from salaries s
where s.to_date='9999-01-01'
group by salary
order by salary desc
9 所有部门当前manager当前薪水情况
select d.dept_no,d.emp_no,s.salary
from dept_manager d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01';
10 所有非manager员工的emp_no
关联查询得到所有manager的emp_no.从employees中排除这些。
select emp_no
from employees
where emp_no not in(
select e.emp_no
from employees e
inner join dept_manager d
on e.emp_no=d.emp_no
);
11 获取所有员工当前的manager
如果是自己则不显示
select e.emp_no,m.emp_no
from dept_emp e
inner join dept_manager m
on e.dept_no=m.dept_no
where e.emp_no<>m.emp_no
and e.to_date="9999-01-01"
and m.to_date="9999-01-01";