Oracle多表连接，自身连接（习题答案）

多表连接

1.表别名 别名越多越好，定义必须使用

2.笛卡尔积P61-62

2.1

(练习1)emp与dept笛卡尔积连接

select * from emp dept

(练习2)使用交叉连接，emp与dept笛卡尔积连接

select * from emp cross join dept

使用等值连接（where语句），实现emp与dept连接

select * from emp e,dept d where e.deptno=d.deptno

使用内连接（【inner】join on 语句），实现emp与dept连接

select * from emp inner join dept using(deptno)

使用自然连接（natural join 语句），实现emp与deptno连接

select * from emp natural join dept

–emp与salgrade连接（练习）

–where条件表达式

select * from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal

–内连接 on

select * from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal

4、外连接平p69悬浮元组

–按员工，汇总员工和部门信息

–按部门，汇总部门和员工信息

create table copy_emp as select * from emp where 1>1;--复制表结构，空表
desc copy_emp;
select count(*) from copy_emp;
drop table copy_emp purge

create table copy_emp as select * from emp;--复制表结构和表数据
select count(*) from copy_emp;
insert all
into copy_emp values(6699,'张三'，'MANAGER',NULL,NULL,2300,NULL,20)
into copy_emp values(8888,'李兴华','CLERK',7369,SYSDATE,800,100,NULL)
into copy_emp values(7777,'DAVID','CLERK',7369,to_date('1981-2-28','YYYY-MM-DD'),1600,100,NULL)
into copy_emp values(9999,'李兴华','CLERK',7369,SYSDATE,800,100,NULL)
select 1 from dual;
insert into copy_emp values(6699,'张三','MANAGER',NULL,NULL,2300,NULL,20);
insert into copy_emp values(8888,'李兴华','CLERK',7369,SYSDATE,800,100,NULL);

[习题7]显示所有员工及其所属部门信息

select * from emp e left join dept d on e.deptno=d.deptno

或

select * from emp e,dept d where e.deptno=d.deptno(+)

[习题8]显示所有员工及其所属部门人数（难）

select e.*,temp.dnum from(select deptno,count(*) dnum from copy_emp group by deptno)temp,copy_emp e
where temp.deptno(+)=e.deptno order by e.deptno

或

select e.ename,d.deptno,count(d.deptno)from copy_emp e,dept d where e.deptno=d.deptno group by d.deptno,e.ename

按部门，汇总部门和员工信息

[习题9]显示所有部门序号及各部门人数（where+group by）（难）

select d.deptno,nvl(temp.dnum,0)dnum 
from (select deptno,count(*)dnum from emp group by deptno)temp,dept d
where temp.deptno(+)=d.deptno order by d.deptno

[习题10]显示所有部门信息及各部门人数（where+group by）（难）

select d.*,nvl(temp.dnum,0)dnum
from (select deptno,count(*)dnum from emp group by deptno)temp,dept d
where temp.deptno(+)=d.deptno order by d.deptno

task10-11

5.自身连接 P77

[习题1]查询每个雇员的编号、姓名及上司的上司编号、姓名。(问题？？？)

select e.empno,e.ename,m.empno mno,m.ename mname from emp e,emp m 
where e.mgr=m.empno(+)

[习题2]查询每个雇员的编号、姓名及其下属的编号、姓名。

select e.empno,e.ename,l.empno lno,l.ename lname from emp e , emp l 
where l.mgr(+)=e.empno;

[习题3]查询出每个雇员的编号、姓名、部门名称及其上级领导的编号、姓名、部门名称。(问题？？？)

select e.empno,e.ename,p.empno mmno,p.ename mmname from emp e,emp m,emp p
where e.mgr=m.empno(+) and m.mgr=p.empno(+)

[习题4]查询1981年雇佣的全部雇员的编号、姓名、雇佣日期（按照年-月-日显示）、工作、领导姓名、雇员月工资、雇员年工资（基本工资+奖金），雇员工资等级、部门编号、部门名称、部门位置，并且要求这些雇员的月基本工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作进行排序

select e.empno ,e.ename,to_char(e.hiredate,'YYYY-MM-DD') hiredate, e.job,
m.ename mname,e.sal+nvl(e.comm,0) month_sal,(e.sal+nvl(e.comm,0))*12 year_sal,decode(s.grade,1,'E级工资',2,'D级工资',3,'C级工资',4,'B级工资',5,'A级工资') sgrade,
e.deptno,d.dname,d.loc 
from emp e,emp m,dept d,salgrade s
where e.mgr=m.empno(+)
      and to_char(e.hiredate,'YYYY')='1981'
      and e.deptno=d.deptno
      and e.sal between 1500 and 3500
      and e.sal between s.losal and s.hisal
 order by year_sal desc,e.job;
 

子查询

[习题5]查看比职位是SALESMAN和CLERK工资都高的员工信息（姓名、基本工资和职位）

select sal from emp where job in ('SALEMAN','CLERK');

[习题6]查看有员工的部门编号和部门名称

select d.deptno,dname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.deptno,dname;

[习题7]与SALESMAN职位同部门的其他职位的员工信息（分别用in和exists实现）

select deptno from emp where job='SALESMAN';