–1.使用等值连接,显示员工的编号,姓名,部门名,使用表别名简化
select e.employee_id,e.first_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id ;c
–2.使用非等值连接,显示员工的编号,姓名,月薪,工资级别
select e.employee_id , e.first_name , salary , m.e_groun
from employees e , money m
where e.salary between m.hire_money and m.end_money;
–3.使用外连接,按部门10,20,30,40号,统计各部门员工人数,显示部门号,部门名,人数
select d.department_id ,d.department_name,count(e.employee_id)
from departments d ,employees e
where d.department_id(+)=e.department_id and d.department_id in (10,20,30,40)
group by d.department_id,d.department_name;
–4.使用自连接,显示”SMITH的老板是FORD”这种格式,表示层次关联的数据,用自连接
select e1.first_name ||’的老板是’||e2.first_name as word
from employees e1,employees e2
where e1.manager_id=e2.employee_id;