空间数据库期末复习

解锁：connsys/orclassysdbaalteruserscottaccountunlock;passwscottconnscott/123赋权限grantselect/insert/updateonemptoscott;grantconnect/createtabletoxiaohong;emp表中有几个岗位类别selectdistinctjobfromemp;emp表中各岗位职工数selectjob,count(*)fromempgroupbyjob;emp表中各岗位职工数从高到低排序selectjob,count(*)fromscott.empgroupbyjoborderbycount(*)desc;emp表中各岗位中最高工资selectjob,max(sal)fromscott.empgroupbyjob;在emp表中添加age列，兵根据hiredate列日期求出雇佣时间，存入age列中altertablescott.empadd(agenumber(2));updatescott.empsetage=(sysdate-hirdate)/365;emp表中每个岗位工资最高的职员信息selectename,job,salfromscott.empwheresalin(selectmax(sal)fromscott.empgourpbyjob);在emp表中添加年终奖，年终奖为：（sal+comm.）*i，其中i随部门的不同而变化，部门10的员工为2，部门20的员工为3，部门30的员工为4，部门40的员工为5，更新该列altertablescott.empadd年终奖number(8);updatescott.empset年终奖=caseDEPTNOwhen10then(sal+nvl(comm,0))*2when20then(sal+nvl(comm,0))*3when30then(sal+nvl(comm,0))*4when40then(sal+nvl(comm,0))*5查询哪一个职员其下属最多；Selectmgrfrom(selectmgr,count(*)numbfromscott.empgroupbymgr)t1wherenumb=(selectmax(numb)from(selectmgr,count(*)numbfromscott.empgroupbymgr)t2)查询各部门比所在部门平均工资高的员工姓名，部门，工资Select*fromscott.empt1,(selectdeptno,avg(sal)avgsalfromscott.empgroupbydeptno)t2where(t1.deptno=t2.deptno)and(t1.salt2.avgsal)为了激励每个员工，将在公司内每个员工从雇佣时间开始到2010年，按每天补偿2元计算，输出每个员工的姓名，补偿天数，补偿金额Selectename,cast(’01-1月-10’asdate)-hiredateas补偿天数,（cast(’01-1月-10’asdate)-hiredate）*2as补偿金额fromscott.emp多表查询据表emp和salgrade输出每个员工的姓名，工资，工资等级Selectename,sal,gradefromscott.emp,scott.salgradewherescott.smp.sal=scott.salgrade.losalandscott.emp.sal=scott.salgrade.hisal;据表emp和dept查询在纽约市工作的ename，job，citySelectename,job,locfromscott.emp,scott.deptwherescott.emp.deptno=scott.dept.deptno根据emp表和dept表查询每个城市中的job类型数量Selectcount(distinctjob),locfromscott.emp,scott.deptwherescott.emp.deptno=scott.dept.deptnogroupbydept.loc;将sex的默认值设为‘男’，且在该列中只能输入‘男’或‘女’两个值其中一个altertableperdonmodifysexdefault‘男’addconstraintsexcheck(sexin(‘男’,’女’))；将polygonid和cardid设为主键altertablelanduseaddconstraintpk_landuseprimarykey(polygonid);altertablepersonaddconstraintpk_personprimaykey(cardid);根据emp表和dept表查询输出job类型数量最多的城市及job类型数量select*from(selectdept.locaslocation,count(job)ascount1fromscott.deptno,scott.deptwhereemp.deptno=dept.deptnogroupbydept.loc)t1wherecount1=(selectmax(count2)from(selectscott.dept.locaslocation,count(job)ascount2fromscott.emp,scott.deptwhereemp.deptno=dept.deptnogroupbydept.loc)t2)将landuse表中cardid设为外键及级联删除altertablelanduseaddconstraintfk_personforeignkey(cardid)referenceperson(cardid)ondeletecascade;设置某列值非空altertabletable_namemodifycolumn_namenotnull;设置某列值唯一altertabletable_nameaddconstraintcolumn_nameunique(column_nme)设定约束，令输入email列的值符合[e-mail]格式altertablepersonaddconstrainchk_emailcheck(emaillike‘%@%.com’);触发器实现不能对emp表中empno列的值进行编辑Createorreplacetriggertr_empnobeforeupdateofempnoonscott.empbeginraise_application_error(-20001,’不能编辑empno列’)end;实现在晚23：00至次日凌晨4:00期间不能对emp表编辑Createorreplacetriggertr_emp1beforeinsertorupdateordeleteonscott.empdeginif(to_char(sysdate,’hh24:MI’)between’23:00’and’4:00’)thenraise_application_error(-20001,’晚23：00至凌晨4：00不能对emp表操作’)；ednif;end;对奖金进行调整后，所有员工的总奖金不能超过10000元；createorreplacetriggertr_del_empbforeupdateonscott.empforeachrowbegindeletefromolst;deletefromnewt;insertintooldtvalues(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno,‘old’);Insertintonewtvalues(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno,‘new’);end;createorreplacetriggertr_teafterupdateonscott.empdeclaresum_commnumber(8);oldnnumber(8);newnnumber(8);empnoxnumber(4);beginselectsum(comm)intosum_commfromemp;selectempno,commintoempnox,oldnfromlodt;selectcommintonewfromnewt;if((sum_comm)10000)thenrise_application_error();updateempsetcomm=oldnwhereempno=empnox;endfi;end;在emp表中添加“年收入”列，年收入为(sal+comm.)*12，创建触发器实现当调整sal或comm列的值后，自动更新“年收入”列的值。altertablescott.empaddysalnumber(7,2);updateempsetysal=(sal+nvl(comm,0))*12创建一个触发器实现：公司根据公司总的盈利情况将利润分配给各部门，规定：员工工资调整后，10部门所有员工的工资和不超过15000,20部门所有员工的工资和不超过18000，30部门所有员工的工资和不超过20000，40部门所有员工的工资和不超过15000；createorreplacetr_teafterupdateonscott.empdeclaresum_commnumber(8);oldnnumber(8);newnnumber(8);empnoxnumber(4);deptnoxnumber(2);begunselectdeptnointodeptnoxfromoldt;CaseWhen(deptnox=10)thenselectsum(comm)intosum_commfromempwheredeptno=10;selectempno,commintoempnox,oldnfromoldt;selectcommintonewnfromnewt;if((sum_comm)15000)thenraise_application_error(-20001,’部门10……’);updateempsetcomm=oldnwhereempno=empnox;endif;when(deptnox=20)thenselectsum(comm)intosum_commfromempwheredeptno=20;selectempno,commintoempnox,oldnfromoldt;selectcommintonewnfromnewt;if((sum_comm)18000)thenraise_application_error(-20002,'部门20');updateempsetcomm=oldnwhereempno=empnox;endif;when(deptnox=30)thenselectempno,commintoempnox,oldnfromoldt;endcase;end;创建工资差额表，表名为diffsal，表格如下IdTypeAvgsal1工资排名前三名平均工资2所有员工平均工资3工资排名后三名平均工资创建触发器实现：如果调整emp表中的工资，便级联更新diffsal表中Avgsal列中的值。（1）createorreplacetriggertr_diffsalafterupdateofsalonempdeclarefi_avgnumber(8,2);tw_avgnumber(8,2);th_avgnumber(8,2);beginselectavg(sal)intofi_avgfrom(selecta1.*,rownumrnfrom(select*fromemporde