文章目录
一、牛客SQL题
简单
1. 查找最晚入职员工的所有信息
#入职最晚的不止一个人
select *
from employees
where hire_date = (
select max(hire_date)
from employees
)
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
select s.*,d.dept_no
from salaries s
join dept_manager d
on s.emp_no = d.emp_no
and s.to_date = '9999-01-01'
and d.to_date = '9999-01-01'
4. 查找所有已经分配部门的员工
select e.last_name,e.first_name,d.dept_no
from employees e
join dept_emp d
on e.emp_no=d.emp_no
#未分配部门的员工不显示
where d.dept_no is not null;
5. 查找薪水记录超过15次的员工
select s.emp_no,count(s.salary) c
from salaries s
group by s.emp_no
having c>15
6. 所有非部门领导的员工emp_no
select emp_no
from employees
where emp_no not in (
select emp_no
from dept_manager
)
7. 获取所有的员工和员工对应的经理
如果员工本身是经理的话则不显示
select e.emp_no,d.emp_no manager
from dept_emp e,dept_manager d
where e.dept_no = d.dept_no
and e.emp_no <> d.emp_no
8. emp_no为奇数,且last_name不为Mary
select *
from employees e
where e.emp_no%2 = 1 (或者e.emp_no&1=1 )
and e.last_name!='Mary'
order by e.hire_date desc
9. 各个title类型的平均工资avg
select t.title,avg(s.salary) as average
from titles as t
join salaries as s
on t.emp_no = s.emp_no
group by t.title
order by average
10. 三表连接
select e.last_name,e.first_name,d.dept_name
from employees e
left join dept_emp de on e.emp_no=de.emp_no
left join departments d on de.dept_no=d.dept_no
11.更新操作:薪水增加10%
update salaries as s join emp_bonus as e on s.emp_no=e.emp_no
set salary=salary*1.1
where to_date='9999-01-01'
中等
1. 获取薪水第二多的员工(多个,不用order by)
-- 方法一
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary = -- 第三步: 将第二高工资作为查询条件
(
select max(salary) -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
from salaries
where salary <
(
select max(salary) -- 第一步: 查出原表最高工资
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'
2. 入职以来的薪水涨幅情况
select b.emp_no,(b.salary-a.salary) as growth
from
(select e.emp_no,s.salary
from employees e
left join salaries s
on e.emp_no=s.emp_no
and e.hire_date=s.from_date)a -- 入职工资表
inner join
(select e.emp_no,s.salary
from employees e
left join s