薪水查询

数据表如下

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

1、查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
输出描述：

emp_no	salary	from_date	to_date	dept_no
10002	72527	2001-08-02	9999-01-01	d001
10003	72537	2001-10-02	9999-01-01	d002

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

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

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

3、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
输出描述：

emp_no	t
10010	21
10021	18

利用Group by 的having进行条件筛选

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

4、找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
输出描述：

salary
94692
93221

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

5、获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’
输出描述：

dept_no	emp_no	salary
d001	10002	72527
d004	10004	74057
d003	10005	94692
d002	10006	43311
d006	10010	94409

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

6、获取所有非manager的员工emp_no

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

7、获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
输出描述：

dept_no	emp_no	salary
d001	10002	72527
d004	10004	74057
d003	10005	94692

select d.dept_no, d.emp_no, max(s.salary) from dept_emp d, salaries s
where d.emp_no = s.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by dept_no;

8、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资average。

select title, avg(salary) as average from titles t, salaries s
where t.emp_no = s.emp_no
and t.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by title

9、 获取当前（to_date=’9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries
where to_date = '9999-01-01'
order by salary desc
limit 1,1;

10、查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

select e.emp_no, max(s.salary) salary, last_name, first_name
from employees e, salaries s
where e.emp_no = s.emp_no
and to_date = '9999-01-01'
and s.salary not in (select max(salary) from salaries where to_date = '9999-01-01');