SQL语句训练16-20

16题目描述
统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
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 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);

select t2.title,avg(t1.salary)
from salaries as t1 inner join titles as t2
on t1.emp_no = t2.emp_no
and t1.to_date = '9999-01-01'
and t2.to_date = '9999-01-01'
group by  title

17题目描述
获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
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));

先给薪水排序找到的第二多的薪水，然后按这个薪水和当前时间去查询员工号

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) 

18题目描述
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
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 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));

和上一题最大的区别是不能使用排序，所以这里要改变方法，先选出一个最大的，再从剩下的区间内选出最大的，就是第二大了

select t1.emp_no,max(t2.salary),t1.last_name,t1.first_name 
from employees as t1, salaries as t2
where t2.salary < (select max(salary) from salaries)
and t2.to_date="9999-01-01" and t1.emp_no=t2.emp_no

19题目描述
查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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 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));

需要两次左连接来进行筛选

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

20题目描述
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
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));

要求增长，我们就按照员工号找出该员工的多条记录，然后用工资最大值减去工资最小值就行了

select max(salary) - min(salary) from salaries where emp_no = 10001