SQL实战笔记，第三篇

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`));

注意：这与上一篇的20题相关，但这里有两张表，用select 嵌套

SELECT scurrent.emp_no, (scurrent.salary-shired.salary) AS growth
FROM
(SELECT a.emp_no, b.salary FROM employees as a inner join salaries as b 
on a.emp_no = b.emp_no where b.to_date = '9999-01-01') AS scurrent,
(SELECT a.emp_no, b.salary FROM employees as a inner join salaries as b
 on a.emp_no = b.emp_no where a.hire_date=b.from_date ) AS shired
WHERE scurrent.emp_no = shired.emp_no
ORDER BY growth asc

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