SQL语句训练1-5

题目地址
https://www.nowcoder.com/activity/oj

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

分两步，第一步查出最晚入职时间，再通过这个时间选取员工

select max(hire_date) from employees;

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

先查出排第三的时间再那这个时间筛选员工

select distinct hire_date from employees order by hire_date desc limit 2,1;

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

两张表内连接后把领导查询出来

select t1.*,dept_no 
from salaries as t1 
inner join dept_manager as t2
on t1.emp_no=t2.emp_no
where t1.to_date='9999-01-01' and t2.to_date='9999-01-01';

4.题目描述
查找所有已经分配部门的员工的last_name和first_name
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 t1.last_name,t1.first_name,t2.dept_no
from employees as t1
natural join dept_emp as t2;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工(这题似乎存在问题，dept_no设置为了not null却不能使用等值连接来做)
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));

假设dept_no存在null，我们可以使用左连接来操作
内左右连接的不同在我博客里有解释
https://blog.csdn.net/wzngzaixiaomantou/article/details/81807714

select t1.last_name,t1.first_name,t2.dept_no
from employees as t1
left join dept_emp as t2
on t1.emp_no=t2.emp_no;