测试题2

1、显示所有员工的姓名、部门号和部门名称

select last_name,d.department_id,department_name
from employees e,departments d
where e.department_id=d.department_id;

2、查询90号部门员工的job_id和90号部门的location_id

select job_id,location_id
from employees e,departments d
where e.department_id=d.department_id
and e.department_id=90;

3、选择所有有奖金的员工的last_name，department_name，location_id，city

select last_name,department_name,lication_id,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工作的员工的last_name，department_name，department_id

select last_name,department_name,department_id
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city ='Toronto';

5、查询每个工种，每个部门的部门名、工种名和最低工资

select j.job_id,department_name,min(salary)
from jobs j,departments d,employees e
where e.department_id=d.department_id
and e.job_id=j.job_id
group by department_name,job_id;

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

SELECT country_id,count(*) 部门个数
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
having count(*)>2;

7、选择指定员工的姓名、员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#

select e.last_name "employees",e.employee_id "Emp#",m.last_name "manager",m.employee_id "Mgr#"
from employees e,employees m
where e.manager_id=m.employee_id