SQL实战笔记,第一篇

1. 查找最晚入职员工的所有信息

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

注意最晚入职的人可能不止一个的可能，所以desc排序后用top 1或者limit 1(limit 0,1)只能提取到一个人
但有可能多个人晚入职所以保险来做：

--答案1：
select * from employees
where 
hire_date=( select hire_date from employees
            order by hire_date desc limit 1)
--答案2：
select * from employees
where 
hire_date=( select max(hire_date) from employees）

2. 查找入职员工时间排名倒数第三的员工所有信息

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

注意：入职时间排名倒数第三，我只学 SQL server 的时候没法做，后来了解了MySQL的limit语句后就很好做了
LIMIT 1 OFFSET 2等价于limit 2，1，跳过前两条数据返回后面的第一条数据
这里需要注意用到distinct语句，要防止出现相同的时间

select * from employees
where 
hire_date=(select distinct hire_date from employees
           order by hire_date desc limit 2,1)

3. 查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号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`));

注意：两张表，考察表的连接，注意隐藏条件

--答案1：
select a.*,b.dept_no
from salaries as a
inner join dept_manager as b
on a.emp_no=b.emp_no
and a.to_date='9999-01-01'
and b.to_date='9999-01-01'
--答案2：
select sa.*,de.dept_no
from salaries sa,dept_manager de
where sa.emp_no=de.emp_no
and sa.to_date='9999-01-01'
and de.to_date='9999-01-01'

4. 查找所有已经分配部门的员工的last_name和first_name以及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 a.last_name,a.first_name,b.dept_no
from employees as a
inner join dept_emp as b
on a.emp_no=b.emp_no

5. 查找所有员工的last_name和first_name以及对应部门编号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`));

注意：考察“展示没有分配具体部门的员工”，left join right join

select a.last_name,a.first_name,b.dept_no
from employees as a
left join dept_emp as b
on a.emp_no=b.emp_no 

6. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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 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
order by a.emp_no desc

7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

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

注意：这道题缺乏很多背景，让人很摸不着头脑，其中有一个答案分析如下，也有说涨幅超过15，应该t>16才行，
应该是默认薪水一直在增长

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

答疑 - HAVING VS WHERE
在一个sql语句中可以有where子句和having子句。having与where子句类似，均用于设置限定条件；
1）where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行；
2）having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数。
来源：来源

8. 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水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`));

注意：“只显示一次”

--答案1：
select distinct salary from salaries
where to_date='9999-01-01'
order by salary desc 
--答案2：
SELECT salary FROM salaries 
WHERE to_date = '9999-01-01'
GROUP BY salary
ORDER BY salary DESC

9. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01’

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

注意：表的连接 inner join

select a.dept_no,a.emp_no,b.salary
from dept_manager as a
inner join salaries as b
on a.emp_no=b.emp_no
and a.to_date='9999-01-01'
and b.to_date='9999-01-01'

10. 获取所有非manager的员工emp_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`));

注意：非manager的emp_no，用到SQL server的集合命令，except（intersect，union，union all)
也可以使用 not in

--答案1：
select emp_no from dept_manager
except
select emp_no from employees
--答案2：
select emp_no from employees 
where emp_no not in (SELECT emp_no FROM dept_manager)