/*1.显示所有员工的姓名，部门号和部门名称*/
SELECT first_name , last_name, e.department_id,department_name 
from employees e ,departments d 
where e.department_id=d.department_id;

/*2.查询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 ;

/*3：选择所有有奖金(commission_pct)的工的last_name,department_name,location_id,city*/
SELECT last_name,d.department_name,d.location_id,l.city
from employees e , departments d ,locations l 
where e.department_id=d.department_id AND d.location_id=l.location_id and e.commission_pct is not null;

/*4:选择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 locations l ,employees e, departments d
WHERE l.city='Toronto' and l.location_id=d.location_id and d.department_id=e.department_id;

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

/*6:查询每个国家下的部门个数大于2的国家编号*/

SELECT l.city , d.department_name 
from locations l,  departments d 
WHERE d.location_id=l.location_id 
HAVING COUNT(d.department_name)>2;

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