牛客SQL1~23

牛客SQL1~23

--SQL1
select * from employees order by hire_date desc limit 1;
--SQL2
select * from employees order by hire_date desc limit 2,1;
--SQL3
select s.*, d.dept_no
from dept_manager d, salaries s
where s.emp_no = d.emp_no 
order by s.emp_no asc;
--SQL4
SELECT e.last_name,e.first_name,d.dept_no
FROM employees AS e 
INNER JOIN dept_emp AS d 
ON e.emp_no = d.emp_no;
--SQL5
select e.last_name ,e.first_name,d.dept_no
from employees as e
left outer join dept_emp as d
on e.emp_no = d.emp_no;

--SQL7
select emp_no,count(1)
from salaries
group by emp_no
having count(1) > 15;

--SQL8
select distinct(salary)
from salaries
order by salary desc;

--SQL10
select employees.emp_no from employees left JOIN dept_manager
on employees.emp_no=dept_manager.emp_no
where dept_no is null

--SQL11
select de.emp_no,dm.emp_no as manager
from dept_emp de,dept_manager dm
where de.dept_no = dm.dept_no and de.emp_no != dm.emp_no;

--SQL12
select
    t1.dept_no,t1.emp_no,t1.salary
from
    (select d.emp_no,d.dept_no,s.salary from dept_emp d join salaries s on d.emp_no=s.emp_no ) t1
    join 
    (select d.dept_no,max(salary)as salary from dept_emp d join salaries s on d.emp_no=s.emp_no  group by d.dept_no) t2
on
    t1.dept_no=t2.dept_no and t1.salary= t2.salary
order by 
    t1.dept_no asc;
--SQL15
select *
from employees
where emp_no%2 = 1 and last_name != "Mary"
order by hire_date desc;

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

--SQL17
select emp_no,salary
from salaries
order by salary desc
limit 1,1;

--SQL18
SELECT employees.emp_no,salaries.salary,employees.last_name,employees.first_name
FROM employees INNER JOIN salaries
ON employees.emp_no=salaries.emp_no
WHERE salaries.salary =(SELECT MAX(salaries.salary) 
                        FROM salaries
                       where salary not in (SELECT MAX(salaries.salary) FROM salaries)
                       );

--SQL19
select e.last_name,e.first_name,t.dept_name
from employees as e left join dept_emp as d
on e.emp_no = d.emp_no
left join departments as t on d.dept_no = t.dept_no

--SQL21
select s1.emp_no,(salary - old_salary) as growth              #这里是为了拿到emp_no和薪水涨幅
from (
        select emp_no ,salary 
        from salaries
        where to_date='9999-01-01'
    ) as s1    #这里是为了获取salaries表中的当前薪水情况和emp_no
    inner join (
    select e.emp_no , salary as old_salary
    from employees as e
            left join 
         salaries as s
            on e.emp_no = s.emp_no
               and e.hire_date = s.from_date      #这里是为了查到一开始的薪水
      )as f       #这里他内连接了employees表，在这里通过employees表关联salaries查到了开始的薪水
    on s1.emp_no = f.emp_no   #关联的条件，即同一个人
order by growth    #最后按照涨幅排序了

--SQL22
select d.dept_no as dept_no,de.dept_name as dept_name, count(*) as sum
from
    salaries s,dept_emp d,departments de
where s.emp_no = d.emp_no and d.dept_no = de.dept_no
group by d.dept_no
order by d.dept_no;

--SQL23
select s1.emp_no,s1.salary,count(distinct s2.salary) as rank 
from salaries s1,salaries s2 
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary <= s2.salary 
group by s1.emp_no order by s1.salary desc,s1.emp_no asc;