牛客网之SQL---持续更新

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

select * from employees
order by hire_date desc
limit 1;

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

select *from employees
order by hire_date desc
limit 2,1;

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

select s.*,d.dept_no 
from salaries s, dept_manager d
where s.emp_no = d.emp_no
order by s.emp_no

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

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

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

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

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

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

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

select distinct salary
from salaries
order by  salary desc

SQL10 获取所有非manager的员工emp_no

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

SQL11 获取所有员工当前的manager

select e.emp_no, d.emp_no asmanager
from dept_emp e, dept_manager d
where (e.dept_no = d.dept_no and
       e.emp_no <> d.emp_no)

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

# SELECT e.dept_no, e.emp_no, max(s.salary) as maxSalary
# from dept_emp e, salaries s
# where e.emp_no = s.emp_no
# group by e.dept_no
# order by e.dept_no
-- 以上是错误示例，正解如下

/*
* 这里做了一个新的表，是部门编号及最高工资（还是有点迷糊）
* 然后用新表把另外两个表联结起来
*/
select r.dept_no,ss.emp_no,r.maxSalary 
from (
    select d.dept_no,max(s.salary)as maxSalary 
    from dept_emp d,salaries s
    where d.emp_no=s.emp_no
    group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
    and r.dept_no=dd.dept_no
    and dd.emp_no=ss.emp_no
order by r.dept_no

SQL15 查找employees表emp_no与last_name的员工信息

select * 
from employees
where (emp_no%2<>0) and last_name!='Mary'
order by hire_date desc

SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

select t.title, avg(s.salary)
from titles t, salaries s
where t.emp_no = s.emp_no
group by t.title

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary
from salaries
ORDER by salary desc
LIMIT 1,1

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

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
    )
)

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

select e.last_name, e.first_name, d.dept_name
from (employees as e left join dept_emp as dd
                     on e.emp_no = dd.emp_no)
     left join departments as d
     on dd.dept_no = d.dept_no