习题1

使用scott/tiger用户下的emp表和dept表完成下列练习1．列出至少有一个员工的所有部门。2．列出薪金比“SMITH”多的所有员工。3．列出所有员工的姓名及其直接上级的姓名。1.selectdistinctdnamefromdeptwheredeptnoin(selectdistinctdeptnofromemp);2.selectename,job,mgr,hiredate,salfromempwheresal(selectsalfromempwhereename='SMITH');3.selecte.ename,e.mgr,w.enamefromempe,empwwheree.mgr=w.empno;4．列出受雇日期早于其直接上级的所有员工。5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。6．列出所有“CLERK”（办事员）的姓名及其部门名称。4.selecte.ename,e.mgr,e.hiredate,w.ename,w.hiredatefromempe,empwwheree.mgr=w.empnoande.hiredatew.hiredate;5.selecte.ename,e.hiredate,d.dname,d.deptnofromempe,deptdwheree.deptno(+)=d.deptno;本例子使用的是外连接，也可以用右连接。selecte.ename,e.hiredate,d.dname,d.deptnofromemperightjoindeptdone.deptno=d.deptno;6.selecte.ename,e.hiredate,e.job,d.dname,d.deptnofromempe,deptdwheree.deptno=d.deptnoande.job=upper('clerk');7．列出最低薪金大于1500的各种工作。8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。9．列出薪金高于公司平均薪金的所有员工。7.selectjob,min(sal)fromempgroupbyjobhavingmin(sal)1500;8.selecte.ename,d.dnamefromempe,deptdwheree.deptno=d.deptnoandd.dname=upper('sales');或者：selectenamefromempwheredeptno=(selectdeptnofromdeptwheredname=upper('SALES'));9.selectename,job,salfromempwheresal(selectavg(sal)fromemp);10．列出与“SCOTT”从事相同工作的所有员工。11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。10.selectename,jobfromempwherejob=(selectjobfromempwhereename=upper('scott'));11.selectename,sal,jobfromempwheresalin(selectsalfromempwheredeptno=30);12.selectename,sal,jobfromempwheresalall(selectsalfromempwheredeptno=30);或者selectename,salfromempwheresal(selectmax(sal)fromempwheredeptno=30);13．列出在每个部门工作的员工数量、平均工资和平均服务期限。14．列出所有员工的姓名、部门名称和工资。15．列出从事同一种工作但属于不同部门的员工的一种组合。13.selectd.deptno,d.dname,count(e.ename),avg(e.sal),avg(months_between(sysdate,e.hiredate))fromempe,deptdwheree.deptno(+)=d.deptnogroupbyd.deptno,dname;或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。selecte.*,d.dnamefrom(selectdeptno,count(ename),avg(sal),avg(months_between(sysdate,hiredate)/12)fromempgroupbydeptno)e,deptdwheree.deptno=d.deptno;14.selectd.dname,e.ename,sal+nvl(comm,0)fromempe,deptdwhered.deptno=e.deptno;15.selecte.ename,e.job,e.deptno,d.job,d.deptnofromempe,empdwheree.job=d.jobande.deptnod.deptno;16．列出所有部门的详细信息和部门人数。17．列出各种工作的最低工资。18．列出各个部门的MANAGER（经理）的最低薪金。19．列出所有员工的年工资,按年薪从低到高排序。16.selectcount(e.ename),d.dnamefromempe,deptdwheree.deptno(+)=d.deptnogroupbye.deptno,d.dname;或者写成selectd.deptno,e.*,d.dnamefrom(selectdeptno,count(ename)fromempgroupbydeptno)e,deptdwheree.deptno(+)=d.deptno;17.selectjob,min(sal+nvl(comm,0))fromempgroupbyjob;18.selectdeptno,min(sal)fromempwherejob=upper('manager')groupbydeptno;19.selectename,(sal+nvl(comm,0))*12yearsalfromemporderbyyearsal;20.查找EMP表中前5条记录21查找EMP表中10条以后的记录22查找EMP表中薪水第5高的员工20SELECT*FROMempWHEREROWNUM=5;21SELECT*FROM(SELECTa.*,ROWNUMASrnFROMempa)WHERErn10;22SELECT*FROM(SELECTa.*,rownumASrnFROMempaORDERBYsalDESC)WHERErn=5;1、创建表myemp和emp表具有相同的结构和记录。（若只是结构而没有数据呢？只复制部分数据呢？）2、给myemp的empno列添加主建约束。1.createtablemyempasselect*fromemp;表的约束是复制不过来的，需要自己去添加createtablemydept1asselect*fromdeptwhere1=2insertintomydept1select*fromdeptwheredeptno20;或者createtablemydept1asselect*fromdeptwheredeptno20;2.altertablemyempaddconstraintmyemp_empno_pkprimarykey(empno);创建以下表teachercreatetableteacher(teacherxhvarchar2(10)primarykey,teachernamevarchar2(20));插入记录，要求:教师的编号的格式是TH00001，TH00002…….建立序列createsequenceteacher_seqmaxvalue99999;insertintoteachervalues('TH'||ltrim(to_char(teacher_seq.nextval,'00000')),'张三');insertintoteachervalues('TH'||ltrim(to_char(teacher_seq.nextval,'00000')),'李');使用PL/SQL块编程实现，注意必要的异常处理1.输入一个员工号，输出该员工的姓名、薪金和大概的服务年限（按年月日显示）2.接收一个员工号，输出该员工所在部门的名称3.接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEWYORK工作那么就给他薪金扣除5％;其他情况不作处理。4.接收一个员工号，输出这个员工所在部门的平均工资5.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样•declare•CURSORc_emp(p_empnoemp.empno%TYPE)IS•SELECTemp.job,emp.empno,dept.loc,emp.deptnoFROMemp,deptWHEREemp.deptno=dept.deptnoandemp.empno=p_empno;•enoemp.empno%type;•begin•eno:=&no;•forv_empinc_emp(eno)loop•ifv_emp.job='MANAGER'andv_emp.loc='DALLAS'then•updateempsetsal=sal*1.15whereempno=eno;•endif;•ifv_emp.job='CLERK'andv_emp.loc='NEWYORK'then•updateempsetsal=sal*0.95whereempno=eno;•endif;•endloop;•end;3•declare•cursormycurisselectemp.empno,emp.job,dept.locfromemp,deptwhereemp.deptno=dept.deptno;•begin•forv_mycurinmycurloop•ifv_mycur.job='MANAGER'andv_mycur.loc='DALLAS'then•updateempsetsal=sal*1.15whereempno=v_mycur.empno;•endif;•ifv_mycur.job='CLERK'andv_mycur.loc='NEWYORK'then•updateempsetsal=sal*0.95whereempno=v_mycur.empno;•endif;•endloop;•end;•declare•cursormycurisselect*fromempforupdate;•dno1dept.deptno%type;•dno2dept.deptno%type;••begin•selectdeptnointodno1fromdeptwhereloc='DALLAS';•selectdeptnointodno2fromdeptwhereloc='NEWYORK';•forv_mycurinmycurloop•ifv_mycur.deptno=dno1then•updateempsetsal=sal*1.15wherecurrentofmycur;•endif;•ifv_mycur.deptno=dno2then•updateempsetsal=sal*0.95wherecurrentofmycur;•endif;•endloop;••end;——创建员工表createtableemp(emp_idnumber(5),emp_namevarchar2(20),emp_salarynumber(4),jobvarchar2(20),dept_idnumber(3));——部门表createtabledept(dept_id