/*显示所有员工的姓名，部门号和部门名称*/
SELECT first_name,e.department_id,department_name 
FROM employees e,departments d 
WHERE e.department_id=d.department_id;
/*查询90号部门员工的job_id和90号部门的location_id*/
SELECT e.job_id,d.location_id 
FROM employees e,departments d 
WHERE  e.department_id=d.department_id 
and e.department_id=90;
/*选择所有有奖金(commission_pct)的工的last_name,department_name,location_id,city*/
SELECT e.last_name,d.department_name,l.location_id,l.city 
FROM employees e,departments d,locations l 
WHERE l.location_id=d.location_id  
and e.department_id=d.department_id 
and commission_pct is NOT null;

/*选择city在Toronto工作的员工的ast_name,department_name,location_id,job_id,department_id*/
SELECT last_name,department_name,d.location_id,job_id,e.department_id 
FROM employees e,departments d,locations l 
WHERE l.location_id=d.location_id  
and e.department_id=d.department_id  and city='Toronto';

/*:查询每个工种、每个部门的部门名字、工种号和最低工资(要求分组)*/
SELECT  j.job_title ,j.job_id ,e.department_id ,department_name,min_salary
FROM  employees e,jobs j,departments d
WHERE e.department_id=d.department_id AND e.job_id=j.job_id
GROUP BY e.department_id,e.job_id;

/*查询每个国家下的部门个数大于2的国家编号*/
SELECT country_id,COUNT(*) FROM
locations l,departments d WHERE l.location_id=d.location_id HAVING COUNT(*)>2;

/*选择指定的员工姓名，员工号，以及它的管理这的姓名和员工号，结果类似与下面的格式
employees    Emp#    manager  Mgr#
kochhar 101  king     100 */
SELECT e.last_name employees,e.employee_id Emp,e1.last_name manager,e1.employee_id Mgr
FROM  employees e,employees e1 WHERE e.department_id=e1.department_id
and e.manager_id=e1.manager_id;