之前的综合题有点难度。

https://www.nowcoder.com/ta/sql

 

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

使用子查询

# 日期相同也无碍
SELECT * FROM employees
        WHERE hire_date = (SELECT max(hire_date)
FROM employees)

# 只能输出一条
SELECT * FROM employees 
ORDER BY hire_date DESC LIMIT 1;

 

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

先完成子查询，然后根据找到的时间排名，确定该员工所有的信息

# 若存在同一天多人入职，不好使
SELECT * FROM employees 
ORDER BY hire_date DESC LIMIT 1 OFFSET 2;

SELECT * FROM employees 
WHERE hire_date=(
SELECT DISTINCT hire_date 
FROM employees
ORDER BY hire_date DESC LIMIT 1 OFFSET 2
);

 

3.查找当前薪水详情以及部门编号dept_no

只使用where

使用内连接

使用右连接

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

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

select s.*,d.dept_no
from salaries as s right join dept_manager as d
on s.emp_no = d.emp_no

 

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

右连接

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

 

5.查找所有员工的last_name和first_name以及对应部门编号dept_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;