題目地址
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.題目描述
查找入職員工時間排名倒數(shù)第三的員工所有信息
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.題目描述
查找各個部門當(dāng)前(to_date='9999-01-01')領(lǐng)導(dǎo)當(dāng)前薪水詳情以及其對應(yīng)部門編號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));

兩張表內(nèi)連接后把領(lǐng)導(dǎo)查詢出來

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.題目描述
查找所有已經(jīng)分配部門的員工的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));

兩張表只有一個重復(fù)屬性，可以使用自然連接來操作

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以及對應(yīng)部門編號dept_no，也包括展示沒有分配具體部門的員工(這題似乎存在問題，dept_no設(shè)置為了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));

假設(shè)dept_no存在null，我們可以使用左連接來操作
內(nèi)左右連接的不同在我博客里有解釋
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;