sql训练2

12 所有部门中当前员工薪水最高的相关信息

，给出dept_no/emp_no以及对应的salary

select d.dept_no,d.emp_no,max(salary)
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date=s.to_date
group by d.dept_no
1
2
3
4
5
6

13 title表获取按照title进行分组

每组个数大于等于2，给出titile以及对应的数目

select title,count(*) t
from titles
group by title
having count(*) >=2;
1
2
3
4

15 查找employees表所有emp_no为奇数

且last_name不为mary的员工信息，按照hire_date逆序

select * 
from employees e
where e.emp_no%2=1
//where e.emp_no mod 2 =1
and e.last_name is not 'mary'
//and e.last_name <> 'mary'
order by e.hire_date desc
1
2
3
4
5
6
7

16 统计当前各个title类型

对应的员工当前薪水平均工资；结果给出title以及平均工资avg

select t.title,avg(s.salary) avg
from titles t
inner join salaries 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;
1
2
3
4
5
6
7

17 获取当前薪水第二多

的员工的dept_no以及员工薪水

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

18 当前薪水排名第二的员工编号

emp_no,薪水salary,last_name以及first_name,不准使用order by

select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees e
inner join salaries s
on e.emp_no =s.emp_no
where s.to-date='lll'
and s.salary not in(
    select max(salary)
    from salaries
    where to_date='jjj'
);
1
2
3
4
5
6
7
8
9
10

19 所有员工的last_name/first_name/dept_name

包括暂时没有分配部门的员工（部门可以少，员工在左边）

不理解，三张表什么意思；

select e.last_name.e.first_name,d.dept_name
from employees
left join dept_emp de on e.dept_no=de.dept_no
left join departments d on d.dept_no=de.dept_no;
1
2
3
4

20 查找员工编号emp_no=100001员工入职以来薪水涨幅值growth

select 
(
    (select salary from salaries where emp_no=10001 order by salary)
    -(select salary from salaries where emp_no=10001 order by salary)
) growth;
1
2
3
4
5

21 查找所有员工自入职以来薪水涨幅情况

select emp_no, (s1.salary-s2.salary) as growth
from employees e
inner join salaries s1
on e.emp_no =s1.emp_no and s1.to_date='9999-01-01'
inner join salaries s2
on e.emp_no=s2.emp_no and e.hire_date=s2.from_date
order by growth asc;
1
2
3
4
5
6
7

22 各个部门对应的员工涨幅的次数总和

dept_no dept_name 次数sum

select d.dept_no,d.dept_name,count(s.salary) 'sum'
from departments d
inner join dept_emp de on d.dept_no=de.dept_no
inner join salaries s on de.emp_no=s.emp_no
group by d.dept_no;
1
2
3
4
5

23 对所有员工当前薪水按照salary进行排序

select emp_no,salary,count(distinct s2.salary) rank
from salaries s1,salaries s2
where s1.salary<=s2.salary
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'
group by s1.emp_no
order by s1.salary desc , s1.emp_no asc
1
2
3
4
5
6
7

24 所有非manager员工当前薪水情况

思路 1 查询所有员工情况

2 筛选掉manager员工

select de.depts_no,e.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no=s.emp_no
and s.to_date=''
inner join dept_emp de
on e.emp_no=de.emp_no
where de.emp_no not in(
    select emp_no
    from dept_manager dm
    where dm.to_date=''
)
1
2
3
4
5
6
7
8
9
10
11
12