空间数据库期末复习.doc

解锁： conn sys/orcl as sysdbaalter user scott account unlock;passw scottconn scott/123赋权限 grant select / insert / update on emp to scott; grant connect / create table to xiaohong;emp表中有几个岗位类别 select distinct job from emp;emp表中各岗位职工数 select job,count(*)from emp group by job;emp表中各岗位职工数从高到低排序select job,count(*) from scott.emp group by job order by count(*) desc;emp表中各岗位中最高工资select job,max(sal) from scott.emp group by job;在emp表中添加age 列，兵根据hiredate列日期求出雇佣时间，存入age列中alter table scott.emp add ( age number (2);update scott.emp set age=(sysdate-hirdate)/365;emp表中每个岗位工资最高的职员信息select ename,job,sal from scott.empwhere sal in(select max(sal) from scott.emp gourp by job);在emp表中添加年终奖，年终奖为：（sal+comm.）*i，其中i随部门的不同而变化，部门10的员工为2，部门20的员工为3，部门30的员工为4，部门40的员工为5，更新该列alter table scott.emp add 年终奖number(8);update scott.emp set 年终奖=case DEPTNOwhen 10 then (sal+nvl(comm,0)*2when 20 then (sal+nvl(comm,0)*3when 30 then (sal+nvl(comm,0)*4when 40 then (sal+nvl(comm,0)*5查询哪一个职员其下属最多；Select mgr from (select mgr , count(*) numb from scott.emp group by mgr ) t1 where numb=(select max(numb) from(select mgr ,count(*) numb from scott.emp group by mgr ) t2 )查询各部门比所在部门平均工资高的员工姓名，部门，工资Select * from scott.emp t1, (select deptno,avg(sal) avgsal from scott.emp group by deptno ) t2 where(t1.deptno=t2.deptno) and (t1.salt2.avgsal)为了激励每个员工，将在公司内每个员工从雇佣时间开始到2010年，按每天补偿2元计算，输出每个员工的姓名，补偿天数，补偿金额Select ename, cast(01-1月-10 as date)-hiredate as 补偿天数,（cast(01-1月-10 as date)-hiredate）*2 as 补偿金额from scott.emp多表查询据表emp和salgrade 输出每个员工的姓名，工资，工资等级Select ename, sal, gradefrom scott.emp, scott.salgradewhere scott.smp.sal = scott.salgrade.losal and scott.emp.sal10000 ) then rise_application_error();update emp set comm=oldn where empno=empnox;end fi;end;在emp表中添加“年收入”列，年收入为(sal+comm.)*12，创建触发器实现当调整sal或comm列的值后，自动更新“年收入”列的值。alter table scott.emp add ysal number (7,2);update emp set ysal=(sal+nvl(comm,0)*12创建一个触发器实现：公司根据公司总的盈利情况将利润分配给各部门，规定：员工工资调整后，10部门所有员工的工资和不超过15000,20部门所有员工的工资和不超过18000，30部门所有员工的工资和不超过20000，40部门所有员工的工资和不超过15000；create or replace tr_te after update on scott.empdeclare sum_comm number(8); oldn number(8); newn number(8); empnox number(4); deptnox number(2);begun select deptno into deptnox from oldt; Case When (deptnox=10) then select sum(comm) into sum_comm from emp where deptno=10; select empno,comm intoempnox,oldn from oldt; select comm into newn from newt; if(sum_comm)15000) then raise_application_error(-20001,部门10); update emp set comm=oldn where empno=empnox; end if; when(deptnox=20) then select sum(comm) into sum_comm from emp where deptno=20; select empno,comm into empnox,oldn from oldt; select comm into newn from newt; if(sum_comm)18000) then raise_application_error(-20002,部门20); update emp set comm=oldn where empno=empnox; end if; when(deptnox=30) then select empno,comm into empnox,oldn from oldt; end case; end;创建工资差额表，表名为diffsal，表格如下IdTypeAvgsal1工资排名前三名平均工资2所有员工平均工资3工资排名后三名平均工资 创建触发器实现：如果调整emp表中的工资，便级联更新diffsal表中Avgsal列中的值。（1）create or replace trigger tr_diffsalafter update of salon empdeclare fi_avg number(8,2);tw_avg number(8,2);th_avg number(8,2);begin select avg(sal) into fi_avg from (select a1.*,rownum rn from (select * from emp order by sal desc) a1 where rownum=3) t1; update diffsal set avgsal=fi_avg where id=1;end;（2）create or replace trigger tr_diffsalafter update of salon empdeclare fi_avg number(8,2);tw_avg number(8,2);th_avg number(8,2);begin select avg(sal) into tw_avg from (select avg(sal) from emp)a2 update diffsal set avgsal=tw_avg where id=2;end;（3）create or replace trigger tr_diffsalafter update of salon empdeclare fi_avg number(8,2);tw_avg number(8,2);th_avg number(8,2);begin select avg(sal) into th_avg from (select a3.*,rownum rn from (select * from emp order by sal) a3 where rownum=3) t3; update diffsal set avgsal=th_avg where id=3;end;创建表空间 创建xiaoxiao用户，默认表空data_empcreate tablespace data_emp create user xiaoxiao identified by m123datafile E:data_emp.dbf default tablespace data_emp; size 50m autoextend on 为xiaoxiao用户赋connect , resource权限next 50m maxsize 20480m conn system/orcl as sysdba;extent management local; grant connect,resource to xiaoxiao; 在scott用户下创建视图v_sal，该视图具有的功能为：显示每个城市下每个工作类型中最高工资平均值，包含城市，平均工资两列内容。create view v_sal as select *from (select loc,job,avg(sal) avgsalfrom(select loc,job,sal from scott.emp,scott.deptwhere dept.deptno=emp.deptno order by loc,job) t1) t3Where(loc,avgsal) in (select loc, max(avgsal) maxsal from(select loc,job,avg(sal) avgsalfrom(select loc, job, sal from scott.dept,scott.empwhere scott.dept.deptno=scott.emp.deptnoorder by loc,job) t1group by loc, job) t2group by loc)创建一个包package_emp.其中有两个存储过程和两个函数。存储过程sp_prosal用来实现输入job类型，和增加工资额度，来实现员工加薪。一个函数用来返回排名前n个人的平均工资，为fun_d;一个函数用来返回排名后n个人的平均工资，为fun_i;存储过程sp_prodiffsal用来实现输入排名前n名的数字和排名后n名的数字，输出两者的插值。create or replace package package_emp as procedure sp_prosal(sp_sal emp.job %type);-用来实现输入job类型，和增加工资额度，来实现员工加薪procedure sp_prodiffsal(n number,m number, dis number);-用来实现输入排名前n名的数字和排名后n名的数字，输出两者的插值function fun_d(avg_sal1 emp.sal %type) return fun_dsal;-用来返回排名前n个人的平均工资，为fun_dfunction fun_i(avg_sal2 emp.sal %type) return fun_isal;-用来返回排名后n个人的平均工资，为fun_i end package_emp;create or replace package body package_emp as procedure sp_prosal(sp_sal emp.job%type , add_sal emp.sal %type) is begin update scott.emp set sal=sal+add_sal where scott.emp.job=sp_sal; end;create or replace function fun_d(avg_sal1 emp.sal%type) return fun_dsal as n number(8,2);begin select avg(emp.sal) fun_dsal ,rownum rn from (select * from scott.emp) a1 where rownum = n ) return fun_dsal; end; create or replace function fun_i(avg_sal2 emp.sal%type) return fun_isal asm number(8,2)begin select avg(emp.sal) fun_