ORACLE外连接实例

--查询各个部门工资范围，按照1000~2000,2000~3000.。。。这样的格式显示人数
-------------------方法一
select 
dept.dname ,nvl(ano,0) "1000以下",nvl(dno,0) "1000~2000",
nvl(bno,0) "2000~3000",nvl(cno,0) "3000以上" 
from dept,
(select ano,"1000~2000" dno,"2000~3000" bno,nvl("3000以上",0) cno,f.deptno from
(select ano,"1000~2000","2000~3000",e.deptno from
(select nvl("1000以下",0) ano,"1000~2000",b.deptno from
(select count(sal)  "1000以下" , deptno from emp where sal<1000 group by deptno) a ,
(select count(ename) "1000~2000",deptno from emp where sal>=1000 and sal<2000 group by deptno) b
where a.deptno(+) = b.deptno) e,
(select count(ename) "2000~3000",deptno from emp where sal>=2000 and sal<=3000 group by deptno) c
where e.deptno=c.deptno(+)) f,
(select count(ename) "3000以上",deptno from emp where sal>3000group by deptno) d
where f.deptno = d.deptno(+)) g
where g.deptno(+)=dept.deptno
---------------------------------------------------------------------------------------------------------------------
--方法二
select nvl(ano,0) "1000以下",nvl(bno,0) "1000~2000",nvl(cno,0) "2000~3000",nvl(dno,0) "3000以上",dept.dname from dept
full join
(select count(sal)  ano , deptno from emp where sal<1000 group by deptno) a
on a.deptno = dept.deptno
full join
(select count(ename) bno,deptno from emp where sal>=1000 and sal<2000 group by deptno) b
on dept.deptno = b.deptno
full join
(select count(ename) cno,deptno from emp where sal>=2000 and sal<=3000 group by deptno) c
on dept.deptno = c.deptno
full join
(select count(ename) dno,deptno from emp where sal>3000group by deptno) d
on dept.deptno = d.deptno

 

运行结果：

 

综合实例

create table test1 
("姓名" varchar2(20),"年龄" number(6),"籍贯" varchar2(40),"婚姻状况" varchar2(4),"性别" varchar2(2));

--修改字段类型   alter table 表名 modify（字段名 新数据类型（大小））（不能有数据）
alter table test1 modify ("年龄" number (4));

insert into test1 values ('张强',23,'宝鸡','未婚','男');
insert into test1 values ('张戈',25,'西安','未婚','男');
insert into test1 values ('张敏',35,'临潼','已婚','女');
insert into test1 values ('张超',57,'渭南','已婚','男');
insert into test1 values ('张灵',20,'武功','未婚','女');
insert into test1 values ('张林',18,'榆林','未婚','男');
insert into test1 values ('张宝',1208,'黄巾','未知','男');
insert into test1 values ('张角',1210,'黄巾','未知','男');

update test1 set "婚姻状况" = '丧偶' where "姓名"='张宝';
--添加字段 alter table 表名 add（字段名 数据类型（大小））
alter table test1 add ("结婚次数" number(3));

update test1 set "结婚次数"="年龄"/80 where "姓名"='张角';
update test1 set "结婚次数"=0 where "婚姻状况"='未婚';

update test1 set "结婚次数"=2 where "姓名" in('张敏','张超');

--删除字段  alter table 表名  drop column 字段名
alter table test1 add (canspel char(1));

update test1 set canspel = 'y' where "年龄"<30;
update test1 set canspel = 'n' where "年龄">=30;
alter table test1 drop column canspel;
rename test1 to "古今人物婚姻调查";
select *from "古今人物婚姻调查";
alter session set nls_date_format='yyyy-mm-dd';
alter table "古今人物婚姻调查" add ("调查日期" date default sysdate);
alter table "古今人物婚姻调查" drop column "调查日期";
update "古今人物婚姻调查" set "调查日期"='2013-09-11' where "年龄"<100;
update "古今人物婚姻调查" set "调查日期"='-101-12-01' where "年龄">100;