-- departments 部门表
-- dept_emp 部门员工表
-- dept_manager 部门主管表
-- employees 雇员表
-- salaries 薪水表
-- titile 职称表
-- * 查询统计公司员工数量、男员工数量、女员工数量
SELECT COUNT(employees.emp_no) FROM employees;
SELECT COUNT(employees.emp_no) FROM employees WHERE gender = 'M';
SELECT COUNT(employees.emp_no) FROM employees WHERE gender = 'F';
-- * 查询员工最新薪水 (分页，第一页，每页50行)
-- SELECT * FROM student limit ((当前页数 - 1) * 每页行数), 行数
SELECT * FROM salaries WHERE TO_Date = '9999-01-01' LIMIT 0,50;
-- * 查询部门列表需显示当前主管名字
SELECT departments.dept_no AS '部门编号',departments.dept_name AS '部门名称',employees.first_name AS '名字',employees.last_name AS '姓氏' FROM departments,employees,dept_manager WHERE departments.dept_no = dept_manager.dept_no AND employees.emp_no = dept_manager.emp_no AND dept_manager.to_date = '9999-01-01';
-- * 查询 Lillian Haddadi的详细信息，及薪水调薪记录 以及担任某部门主管的详细信息
SELECT * FROM employees WHERE employees.first_name = 'Lillian' AND employees.last_name = 'Haddadi';
-- 下面的语句为啥会卡死程序？
-- 因为有一张不必要查询的表，相当于做了交叉链接查询，会查找到巨大乘积的数据，把from后面的salaries去掉就能查到了
-- 即使修改正确以后，上下两句的效率也相差很多，下面的查询语句明显运行得慢很多
SELECT salaries.emp_no,salaries.from_date,salaries.to_date,salaries.salary FROM salaries,employees WHERE employees.first_name = 'Lillian' AND employees.last_name = 'Haddadi' AND employees.emp_no = salaries.emp_no;
SELECT salaries.emp_no,salaries.from_date,salaries.to_date,salaries.salary FROM salaries WHERE salaries.emp_no IN (SELECT employees.emp_no FROM employees WHERE employees.first_name = 'Lillian' AND employees.last_name = 'Haddadi');
-- 没有找到担任主管的信息？可能是找职称？
SELECT * FROM titles WHERE titles.emp_no = 10019;
SELECT * FROM dept_manager,employees WHERE employees.first_name = 'Lillian' AND employees.last_name = 'Haddadi' AND employees.emp_no = dept_manager.emp_no;
-- * 查询 Shahaf Famili的详细信息，及薪水调薪记录 以及担任某部门主管的详细信息
SELECT * FROM employees WHERE employees.first_name = 'Shahaf' AND employees.last_name = 'Famili';
SELECT salaries.emp_no,salaries.from_date,salaries.to_date,salaries.salary FROM salaries,employees WHERE employees.first_name = 'Shahaf' AND employees.last_name = 'Famili' AND employees.emp_no = salaries.emp_no;
SELECT * FROM dept_manager,employees WHERE employees.first_name = 'Shahaf' AND employees.last_name = 'Famili' AND employees.emp_no = dept_manager.emp_no;
