sql 解题本 题与答案 2020

查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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 where hire_date=(select max(hire_date) from employees);

查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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  p where 
(SELECT count(hire_date) FROM employees WHERE hire_date>p.hire_date ) =2;

查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no(请注意输出结果，dept_no列是最后一列)
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL comment ‘部门编号’,
emp_no int(11) NOT NULL comment ‘员工编号’,
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 comment ‘员工编号’,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

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