《牛客网在线编程数据库SQL实战1-20题》

本贴主要在刷牛客网数据库SQL实战时记录的笔记

建立员工表
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 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));

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

select * 
from employees
where hire_date = 
(select max(hire_date) from employees);

知识点：
1、order by 默认的排序是升序（ASC），可省略
2、LIMIT m,n ：从第m+1条开始，取n条数据)
3、LIMIT n : 从第0条开始，取n条数据，是limit(0,n)的缩写

常见错误写法：认为最晚入职员工只有一位

select  * from employees order by hire_date desc limit 0,1;

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

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

还有另外一种方法，该方法与上述方法相比，运行时间长且占用内存大，一般不采用

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

3. 查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

知识点：
1、内连接：相当于两表的交集
2、外连接：相当于两表的并集
（1）左连接（left join）: 以左边的表为基准匹配右边的表
（2）右连接（right join）：以右边的表为基准匹配左边的表

牛客网上的输出例子，是薪水情况在前，dept_no在后，需要注意

方法一：隐式内连接

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

方法二：显示外连接/左外连接

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

4. 查找所有已经分配部门的员工的last_name和first_name以及dept_no

select last_name, first_name, dept_no
from employees inner join dept_emp
on employees.emp_no = dept_emp.emp_no;

5. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

知识点：
on与where在使用left join连接时的区别
1、on条件是在生成临时表时使用的条件，不管on后面的条件是否为真，都会返回左边表中的记录；
2、where条件是在临时表生成好后，再对临时表进行过滤的条件，条件不为真的全部过滤，失去了left join的含义（必须返回左边表的记录）

select s.last_name, s.first_name, d.dept_no
from employees e
left join dept_emp d
on employees.emp_no = dept_emp.emp_no;

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

方法一、使用内连接 21ms

select e.emp_no, s.salary
from employees as e
inner join salaries as s
on e.emp_no = s.emp_no and  e.hire_date = s.from_date
order by e.emp_no desc;

方法二、使用并列查询，相当于使用笛卡尔积 15ms

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

方法三、使用group by having 16ms

select emp_no, salary from salaries
group by emp_no having min(from_date)
order by emp_no desc;

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

下述答案的涨幅情况应该是涨为正，降为负，不涨不降为零，即薪资只要出现变化就记录一次

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

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

知识点：distinct和group by的使用
1、多表使用group by；
2、单表使用distinct和group by都可以；
3、尽量使用group by。

select salary from salaries
where to_date = '9999-01-01'
group by salary order by salary desc;

select distinct 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’

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

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

10. 获取所有非manager的员工emp_no

select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where d.emp_no is null;

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

11. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

1、用部门号相同连接两表；
2、当前时间说明：de.to_date = ‘9999-01-01’ dm.to_date = ‘9999-01-01’ ;
3、当前经理是自己不显示，即de.emp_no <> dm.emp_no 或者de.emp_no ！= dm.emp_no

select de.emp_no, dm.emp_no as manager_no
from dept_emp as de, dept_manager as dm
where de.dept_no = dm.dept_no
and de.to_date = '9999-01-01'
and dm.to_date = '9999-01-01'
and de.emp_no <> dm.emp_no;

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

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

13. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
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 title, count(title) as t
from titles
group by title having t >= 2;

14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。

select titles, count(distinct emp_no) as t
from titles
group by title having t > 1;

15. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees
where emp_no%2 == 1
and last_name <> 'Mary'
order by hire_date desc;

16. 统计出当前各个title类型对应的员工当前（to_date=‘9999-01-01’）薪水对应的平均工资。结果给出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 title, avg(salary) as avg
from titles as t inner join salaries as s
on t.emp_no = s.emp_no
where t.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by t.title;

17. 获取当前（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;

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 e.emp_no,max(s.salary),e.last_name,e.first_name
from employees e
inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and salary not in (select max(salary) from salaries where to_date = '9999-01-01')

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, dm.dept_name
from employees e
left join dept_emp de on e.emp_no = de.emp_no
left join department dm on de.dept_no = dm.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 (
(select salary from salaries where emp_no = '10001' 
	order by to_date desc limit 1)
 (select salary from salaries where emp_no = '10001'
	order by to_date limit 1)
) as growth;