SQL练习7-牛客网11-20题-选择top2的两种方法

链接：www.nowcoder.com/ta/sql?page…

注意
$\color{red}{第7、8题}$ 选择top2的两种方法

表结构：
employees (emp_no,birth_date,first_name,last_name,gender,hire_date)
dept_manager (dept_no,emp_no,from_date,to_date)
dept_emp (emp_no,dept_no,from_date,to_date)
departments (dept_no,dept_name)
salaries (emp_no,salary,from_date,to_date)
titles (emp_no,title,from_date,to_date)

1. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'
   dept_emp 表, dept_manager 表

select e.emp_no, m.emp_no
from dept_emp e
inner join dept_manager m on e.dept_no=m.dept_no
where e.emp_no<>m.emp_no and e.to_date='9999-01-01' and m.to_date='9999-01-01'

2. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
   dept_emp 表, salaries 表

select de.dept_no, de.emp_no, max(s.salary)
from dept_emp de
inner join salaries s on de.emp_no=s.emp_no
where de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
group by de.dept_no 
order by de.dept_no

3. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
   titles 表

select title, count(title)
from titles
group by title
having count(title)>=2

4. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t. 注意对于重复的emp_no进行忽略
   titles 表

select title, count(distinct emp_no)
from titles
group by title
having count(distinct emp_no)>=2

5. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
   employees 表

select emp_no, birth_date, first_name, last_name, gender, hire_date
from employees
where emp_no%2=1 and last_name<>'Mary'
order by hire_date desc

6. 统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资. 结果给出title以及平均工资avg
   salaries 表, titles 表

select t.title, avg(s.salary)
from titles t
left join salaries s on t.emp_no = s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by t.title

$\color{red}{注意选第二多、第三多的方法}$

7. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
   salaries 表

select emp_no, salary
from salaries
where to_date='9999-01-01' 
group by salary
order by salary desc
limit 1,1

$\color{red}{第二大：小于最大，在剩下的里面最大的}$
8. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
employees 表, salaries 表

select e.emp_no, max(salary), last_name, first_name
from employees e 
left join salaries s on e.emp_no=s.emp_no
where to_date='9999-01-01' and salary < (
    select max(salary) from salaries)

9. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
   departments 表, dept_emp 表, employees 表

select last_name, first_name, dept_name
from employees e 
left join dept_emp de on de.emp_no=e.emp_no
left join departments ds on ds.dept_no=de.dept_no

10. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
    salaries 表

select max(salary)-min(salary) growth
from salaries
where emp_no=10001