15 查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

解法一

select * from employees where emp_no%2=1 and last_name != ‘Mary’ order by hire_date desc;

解法二 （使用位运算）

select * from employees where emp_no&1=1 and last_name != ‘Mary’ order by hire_date desc; 奇数&1=1 偶数&1=0 另外,判定是否为偶数,可以通过先除以2再乘以2 emp_no >> 1 << 1 是否等于emp_no

16 统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

select t.title,avg(s.salary) from titles t inner 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;

17 获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

解法一 distinct

select emp_no , salary from salaries where to_date=‘9999-01-01’ and salary = (select distinct salary from salaries order by salary desc limit 1,1);

解法二 group by

select emp_no , salary from salaries where to_date=‘9999-01-01’ and salary = (select salary from salaries group by salary order by salary desc limit 1,1);

18 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

去掉最大值再取最大值。 select e.emp_no,s.salary,e.last_name,e.first_name from employees e inner join salaries s on e.emp_no = s.emp_no where salary = ( select max(salary) from salaries where salary<(select max(salary) from salaries where to_date=‘9999-01-01’) );