sql训练01

以赛代练了直接。

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

select * 
from employees
where hire_date=(select max(hire_date) from employees);
1
2
3

2 查找入职时间排名倒数第三

select * 
from employees
where hire_date=(
	select distinct hire_date
    from employees
    order by hire_date desc
    limit 2,1
);
1
2
3
4
5
6
7
8

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';
1
2
3
4
5
6

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;
1
2
3
4

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
1
2
3
4

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
1
2
3
4
5
6

7 薪水涨幅超过15次员工号emp_no以及次数t

select s.emp_no,count(*) t
from salaries s
group by s.emp_no
having count(*)>15
1
2
3
4

8 所有员工当前薪水具体情况

相同仅显示一次，逆序输出

select distinct salary
from salaries s
where s.to_date='9999-01-01'
order by salary desc
1
2
3
4

distinct 效率比较低

方式二 使用groupby分类

select salary
from salaries s
where s.to_date='9999-01-01'
group by salary
order by salary desc
1
2
3
4
5

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';
1
2
3
4
5
6

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
);
1
2
3
4
5
6
7
8

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";
1
2
3
4
5
6
7