牛客数据库SQL习题实战（一）

面试过程经常遇到被要求写算法题的，下面就更新做牛客上关于数据库的习题

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

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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 * from employees order by hire_date desc limit 1
或者
select * from employees order by hire_date desc limit 0，1
或者
SELECT * 
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees)
或者
SELECT s1.emp_no , s1.birth_date, s1.first_name,s1.last_name,s1.gender,s1.hire_date
FROM employees AS s1 WHERE s1.hire_date = (SELECT MAX(hire_date) FROM employees);

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

表的信息和第一题一样

答案：select * from employees order by hire_date desc limit 2,1

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

(注:请以salaries表为主表进行查询，输出结果以salaries.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`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, -- '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

答案

select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
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'
order by salaries.emp_no

### 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,dept_emp as b 
where a.emp_no=b.emp_no
或者
select employees.last_name,employees.first_name,dept_emp.dept_no 
from employees inner join dept_emp
on employees.emp_no=dept_emp.emp_no

on和where在inner上没有区别

在left join 或者right join 有区别。因为on 是无论条件是否是真，都会实现join。如果有on也有where，先执行on然后再执行where

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

答案

select e.last_name,e.first_name,d.dept_no
from employees as e left join dept_emp as d 
on e.emp_no=d.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 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
或者
select e.emp_no, d.salary
from employees e
inner join salaries d
on e.emp_no = d.emp_no
where e.hire_date = d.from_date
order by e.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`));

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

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

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' ORDER BY salary DESC

### 9.获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

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 d.dept_no, s.emp_no, s.salary
from dept_manager as d,salaries as s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
或者
select d.dept_no,d.emp_no,s.salary
from dept_manager as d
inner join salaries as s
on d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
and s.emp_no=d.emp_no

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

select emp_no
from employees
where emp_no not in (select emp_no from dept_manager)
或者
select e.emp_no
from employees e 
left join dept_manager d 
on e.emp_no=d.emp_no
where d.dept_no isnull

未完待续