数据库SQL刷题21-25

21、题目描述
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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 T.emp_no,(T.salary-H.salary) AS growth
FROM 
(SELECT emp_no,salary FROM salaries WHERE to_date='9999-01-01' )AS T
INNER JOIN
(SELECT E.emp_no,S.salary 
FROM employees E LEFT JOIN salaries S ON E.emp_no = S.emp_no
AND E.hire_date = S.from_date) AS H
ON H.emp_no=T.emp_no
ORDER BY growth

22、题目描述
统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum
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 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 D.dept_no,D.dept_name,COUNT(S.salary) AS SUM
FROM departments D,dept_emp E,salaries S
WHERE D.dept_no = E.dept_no
AND E.emp_no = S.emp_no
GROUP BY D.dept_no

23、题目描述
对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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));
输出描述:

碎碎念：刚开始看到这个题目的时候，我是懵的，不知道什么是按照1-N的排名，但是基本看到输出的表格就比较明白了，这里使用的是DENSE_RANK，相同薪资排名相同，连续排名，而rank则可能是不连续的。
代码：

SELECT emp_no,salary,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY rank,emp_no

24、题目描述
获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’
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 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 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 D.dept_no,S.emp_no,S.salary
FROM dept_emp AS D INNER JOIN salaries AS S
ON D.emp_no = S.emp_no AND D.to_date='9999-01-01' AND S.to_date='9999-01-01'
WHERE D.emp_no NOT IN
(SELECT emp_no FROM dept_manager)

碎碎念：这里没有用到employees表
25、题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
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 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 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 E.emp_no,M.emp_no AS manager_no,emp_salary,manager_salary
FROM 
(SELECT D.dept_no,S.emp_no,S.salary AS emp_salary FROM salaries S JOIN dept_emp D
ON S.emp_no = D.emp_no WHERE S.to_date='9999-01-01') AS E JOIN
(SELECT DM.dept_no,S.emp_no,S.salary AS manager_salary FROM salaries S JOIN dept_manager DM
ON S.emp_no = DM.emp_no WHERE S.to_date='9999-01-01') AS M ON
E.dept_no = M.dept_no AND emp_salary > manager_salary