查找最晚入职员工的所有信息
select * from employees where hire_date = (select max(hire_date) from employees)
查找入职员工时间排名倒数第三的员工所有信息
select * from employees order by hire_date desc limit 2, 1;
获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
select de.dept_no, de.emp_no, s.salary from dept_emp de, salaries s where de.emp_no = s.emp_no and de.to_date = '9999-01-01' and s.to_date = '9999-01-01' group by de.dept_no having max(s.salary)
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。注意对于重复的emp_no进行忽略。
select t.title, count(distinct emp_no) t from titles t group by t.title having t>=2
查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
select * from employees e where e.emp_no%2 = 1 and e.last_name != 'Mary' order by hire_date desc
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
select s.emp_no, max(s.salary) salary, e.last_name, e.first_name from salaries s, employees e where s.to_date = '9999-01-01' and s.emp_no = e.emp_no and s.salary < (select max(salary) from salaries where s.to_date = '9999-01-01')
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
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
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth FROM (SELECT s.emp_no, s.salary FROM salaries s WHERE s.to_date = '9999-01-01') AS sCurrent, (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart WHERE sCurrent.emp_no = sStart.emp_no ORDER BY growth
对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank FROM salaries AS s1, salaries AS 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