【面试官:我看你SQL语句掌握的怎么样？面试SQL语句专题1】

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

实现SQL

select
  *
from
  employees
where
  hire_date = (
    select
      max(hire_date)
    from
      employees
  )

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

实现SQL

select
  *
from
  employees
where
  hire_date = (
    select
      distinct hire_date
    from
      employees
    order by
      hire_date DESC
    limit
      2, 1
  )

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

实现SQL

select
  salaries.emp_no,
  salaries.salary,
  salaries.from_date,
  salaries.to_date,
  dept_manager.dept_no
from
  salaries,
  dept_manager
where
  salaries.emp_no = dept_manager.emp_no
order by
  salaries.emp_no;

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

实现SQL

select
  e.last_name,
  first_name,
  d.dept_no
from
  employees e,
  dept_emp d
where
  e.emp_no = d.emp_no;

五.查找所有员工的last_name和first_name以及对应部门编号dept_no

实现SQL

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

六.查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

实现SQL

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

七.找出所有员工当前薪水salary情况

实现SQL

select
  distinct salary
from
  salaries
order by
  salary desc

八.获取所有非manager的员工emp_no

实现SQL

select
  emp_no
from
  employees e
where
  e.emp_no not in (
    select
      emp_no
    from
      dept_manager
  )

九.获取所有员工当前的manager

实现SQL

select
  e.emp_no,
  d.emp_no as manager
from
  dept_emp e,
  dept_manager d
where
  e.dept_no = d.dept_no
  and e.emp_no ! = d.emp_no

十.获取每个部门中当前员工薪水最高的相关信息

实现SQL

select
  temp.dept_no,
  s.emp_no,
  temp.salary as maxSalary
from
  salaries s
  inner join (
    select
      d.dept_no,
      max(salary) as salary
    from
      dept_emp d
      inner join salaries s on d.emp_no = s.emp_no
    group by
      d.dept_no
  ) temp inner join dept_emp d on temp.salary = s.salary and d.dept_no=temp.dept_no and d.emp_no=s.emp_no
order by
  temp.dept_no asc