Oracle作业及其答案.ppt

第3章作业,编写SQL语句完成下列功能，并要求写出第1、3、7题的查询结果。1查询10号部门员工的员工号以及其领导的员工号，并以别名“领导员工号”显示列名。selectempno,mgr领导员工号fromempwheredeptno=10;2查询emp表中所有的员工信息，并要求按照部门号升序排序，相同部门按照工资降序排序。select*fromemporderbydeptno,saldesc;,1,3查询部门内部人数多于3人的部门号。selectdeptno,count(*)fromempgroupbydeptnohavingcount(*)3;4向emp表中插入一条数据，员工号1000，员工名：Zhangsan，工作日期是1985年2月3日。insertintoemp(empno,ename,hiredate)values(1000,Zhangsan,date1985-2-3);5修改Zhangsan的工资为20部门的最高工资。updateempsetsal=(selectmax(sal)fromempwheredeptno=20)whereename=Zhangsan;,2,6删除员工名中包含一个“A”并且以“W”结尾的员工信息。deletefromempwhereenamelike%A%W;7统计emp表中每个部门的平均工资和最高工资，并要求参与统计的部门的平均工资多于1000，少于3000。selectavg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)between1000and3000;,3,第5章作业,编写sql语句完成下面功能。1、按照下列表结构创建表。,class表,createtableclass(cnonumber(2)constraintpk_clprimarykey,cnamevarchar2(20);,4,student表,createtablestudent(snonumber(4)constraintpk_stuprimarykey,snamevarchar2(20)constraintun_stuunique,sagenumber,sexchar(2),cnonumber(2);,5,2、为student表添加外键约束，其cno列参照class表cno列。altertablestudentaddconstraintfk_stuforeignkey(cno)referencesclass(cno);3、为student表sage列添加检查约束，列值在0-100。altertablestudentaddconstraintck_stucheck(sagebetween0and100);4、为student表sex列添加约束，确定其值为F或M，且默认值为M。altertablestudentaddconstraintck_stu1check(sexin(F,M);altertablestudentmodifysexdefaultM;,6,5、查询student表的约束信息，并记录查询结果。selectconstraint_name,column_namefromuser_cons_columnswheretable_name=STUDENT;selectowner,constraint_name,constraint_type,statusfromuser_constraintswheretable_name=STUDENT;,7,已知表:学生表：student(sno,sname,sbirth,sclass)班级表：class(cno,cname,cdept)系表：department(dno,dname)其中student(sclass)关联class(cno)，class(cdept)关联department(dno)。,第6章作业,8,编写sql语句完成下列功能。1.查询班级名为1班的所有学生的学号、姓名。selectsno,snamefromstudenta,classbwherea.sclass=oandcname=1班；2.查询学号为20070001的学生的姓名和所在班的班名以及所在系的系名。selectsname,cname,dnamefromstudenta,classb,departmentcwherea.sclass=oandb.cdept=c.dnoanda.sno=20070001;,9,3.查询计算机系里1988年以前出生的学生的学号和出生日期。1）selectsno,sbirthfromstudentwheresclassin(selectcnofromclassa,departmentbwherea.cdept=b.dnoandd.dname=计算机系)andsbirthdate1988-01-01;2）selectsno,sbirthfromstudenta,classb,departmentcwherea.sclass=oandb.cdept=c.dnoandd.dname=计算机系anda.sbirthdate1988-01-01;,10,4.查询比计算机系某一个班的年龄小的学生的学号和出生日期。selectsno,sbirthfromstudentwheresbirthany(selectsbirthfromstudenta,classb,departmentcwherea.sclass=oandb.cdept=c.dnoandd.dname=计算机系）;5.查询计算机系各个班的人数，显示人数和班级号。selectcount(*),sclassfromstudentwheresclassin(selectcnofromclassa,departmentbwherea.cdept=b.dnoandb.dname=计算机系);,11,1.定义记录表类型存储dept表中每个部门（10、20、30、40）的部门号和部门名，并输出。setserveroutputondeclaretypetable_deptistableofdept%rowtypeindexbybinary_integer;t_dtable_dept;beginfort_din(selectdeptno,dnamefromdept)loopdbms_output.put_line(t_d.deptno|t_d.dname);endloop;end;,第7章作业,12,2.利用显式游标修改表emp中各个雇员的工资，若雇员属于10号部门，则增加100，若雇员属于20号部门，则增加200，否则增加300。declarecursorc_empisselectempno,sal,deptnofromemp;beginforvcinc_emploopifvc.deptno=10thenupdateempsetsal=sal+100whereempno=vc.empno;elseifvc.deptno=20thenupdateempsetsal=sal+200whereempno=vc.empno;elseupdateempsetsal=sal+300whereempno=vc.empno;ednif;endloop;end;,13,3.查询并输出emp表中某一部门的员工号、员工名。如果此部门号不存在，捕获此异常。如果出现其他异常，给出异常信息。declaretyper_empisrecord(ENOemp.empno%type，ENAMEemp.ename%type);typeemp_rcisrefcursorreturnr_emp;v_rcemp_rc;emp_rowr_emp;v_deptnoemp.deptno%type;exexception;inumber:=0;jnumber:=0;begin,14,v_deptno=,15,exceptionwhenexthendbms_output.put_line(此员工不存在)；whenothersthendbms_output.put_line(异常信息:|SQLCODE|SQLERROM);end;,16,第7章练习,ex：使用游标与异常处理，完成下列功能。查询dept表中的所有部门号与部门名，并输出。将dept表中部门号为50的部门地址loc改为：上海。如果部门号不合法，则给出错误提示。如果出现其他错误，给出错误代码和错误文本。,17,declarecursore1isselect*fromdept;ex_updateexception;beginforveine1loopdbms_output.put_line(ve.deptno|ve.dname);endloop;updatedeptsetloc=上海wheredeptno=50;ifsql%notfoundthenraiseex_update;endif;exceptionwhenex_updatethendbms_output.put_line(没有50号部门);whenothersthendbms_output.put_line(sqlcode|sqlerrm);end;,18,19,第8章作业,1、编写过程，用于查询某部门的所有员工的员工号和工资。如果部门号不存在，提示出错。createorreplaceproceduresearchempno_sal(v_dnonumber)ascursorc_empisselectempno,salfromempwheredeptno=v_dno;ccc_emp%rowtype;exexception;beginopenc_emp;fetchc_empintocc;ifc_emp%notfoundthenraiseex;elsedbms_output.put_line(员工号|cc.empno);dbms_output.put_line(工资|cc.sal);,endloop;endif;closec_emp;exceptionwhenexthendbms_output.put_line(此部门号不存在);whenothersthendbms_output.put_line(捕获到其他异常);endsearchempno_sal;,20,2、编写函数，查询某个部门所有员工的工资和。createorreplacefunctionsum_sal(dnodept.deptno%type)returnnumberassum_salnumber;beginselectsum(sal)intosum_salfromempwheredeptno=dno;returnsum_sal;endsum_sal;,21,3、编写PL/SQL块调用1、2题中的过程和函数。查询并输出10号部门的员工号、工资和工资和。setserveroutputondeclarev_sum_salnumber;beginsearchempno_sal(10);v_sum_sal:=sum_sal(10）；dbms_output.put_line(10号部门的工资和为:|v_sum_sal);end;,22,