24

题目：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary，当前表示to_date=‘9999-01-01'

方法1：多表联查+NOT IN
SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp AS de, employees AS e, salaries AS s
WHERE de.emp_no=e.emp_no
AND de.emp_no=s.emp_no
AND s.to_date='9999-01-01'
AND e.emp_no NOT IN(SELECT emp_no
FROM dept_manager
WHERE to_date='9999-01-01’)
补充：employees表可以省略：
SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp AS de, salaries AS s
WHERE de.emp_no=s.emp_no
AND s.to_date='9999-01-01'
AND de.emp_no NOT IN(SELECT emp_no
FROM dept_manager
WHERE to_date='9999-01-01’)
补充：当然，多表联查也可以用内连接，这里省略了