数据库实验4实验报告

实验报告书课程名： 《数据库原理及应用 目： T-SQL高级应 级： 软件 号： 名： 孙莹 成绩 指导教师： 批阅时间：年月日SELECT14-16编写程序，查询“1033factoryfactory，55/03/081，75/10/104)workerworkerfactorySQLServerLCB-PCdbo.Diagram_15.1⑨的操作workerdepartworkersalary1-141-151-162345-25-35-45-55-65-75-85-9

5-10FROMWHEREEXISTSEXISTS（*）构成。由于只是测试与1useselectfromwherein(103,105)orderby学号computeavg(分数)by2useschoolselectstudent.班号,course.课程名,avg(score.分数)as'平均分'fromstudent,course,scorewherestudent.学号=score.andcourse.课程号=score.课程号groupbystudent.班号,course.课程名withcube3useschoolbegintransactionMytraninsertintoteachersavetransactionMytraninsertintorollbacktransactionMytrancommittransactionselect*fromteacherdeleteteacherwhere='999'4useselectstudent.学号,student.姓名,student.班号,score.课程号,score.分数fromstudent,scorewhere分数in(selectmax(分数)fromscorewhere班号='1033'andstudent.学号=score.5USEschoolSELECTs.姓名,c.课程名,sc.INTOFROMstudents,coursec,scoreWHEREs.学号=sc.andc.课程号=sc.andsc.isnotnullORDERBYs.姓名SELECT*FROMstud6USEschoolSELECT班号,课程名,MAX(FROM(SELECTs.学号,s.姓名,s.班号,c.课程名,sc.分数FROMstudents,coursec,scorescWHEREs.学号=sc.ANDc.课程号=sc.ANDISNOTNULL)TGROUPBY班号,课程名ORDERBY7USEfactorySELECTworker.职工号,worker.姓名,salary.工资FROMworker,salaryWHEREworker.职工号=salary.职工号ORDERBYworker.职工号,worker.COMPUTESUM(salary.工资)BY 8USEfactorySELECTworker.性别,depart.部门名,AVG(salary.ASFROMworker,depart,salaryWHEREworker.职工号=salary.ANDworker.部门号=depart.部门号GROUPBYworker.性别,depart.部门名WITHCUBE9USEfactoryINSERTINTOworkerVALUES('20','陈立','女','55/03/08',1,'75/10/10',4)INSERTINTOdepartVALUES(5,'设备处')SELECTworker.职工号,worker.姓名,depart.部门名FROMworkerFULLJOINdepartORDERBYworker.职工号DELETEFROMworkerWHERE职工号DELETEFROMdepartWHERE5USEfactorySELECTworker.职工号,worker.姓名,depart.部门名,salary.日期,salary.工资FROMworker,depart,salaryWHEREworker.部门号=depart.ANDworker.职工号=salary.职工号ANDsalary.工资=(SELECTMAX(FROMUSEfactorySELECTFROMdepartWHERE(SELECTFROMworkerWHERE(SELECTFROMsalaryWHERE工资=(SELECTMAX(FROM)USEfactorySELECTFROMworkerWHEREIN(SELECT职工号FROMsalaryGROUPBYHAVINGAVG(工资)<(SELECTAVG(工资)FROM)USEfactorySETNOCOUNTDECLARE@dnameDECLAREd_cursorCURSORFORSELECT部门名FROMdepartWHERE部门号=(SELECTFROMworkerWHERE职工号=(SELECTFROMsalaryWHERE工资=(SELECTMAX(FROM))OPENFETCHNEXTFROMd_cursorINTOPRINT'部门名'PRINT' WHILE@@FETCH_STATUS=0PRINT@dnameFETCHNEXTFROMd_cursorINTOCLOSEd_cursorDEALLOCATEd_cursorUSEfactorySETNOCOUNTDECLARE@noint,@nameDECLAREw_cursorCURSORFORSELECTFROMWHERE职工号IN(SELECT职工号FROMsalaryGROUPBY职工号HAVINGAVG(工资)<(SELECTAVG(工资)FROMOPENw_cursorFETCHNEXTFROMw_cursorINTOPRINT'职工号姓名'PRINT WHILE@@FETCH_STATUS=0PRINTCAST(@noASFETCHNEXTFROMw_cursorINTOCLOSEw_cursorDEALLOCATEw_cursorUSEfactoryDECLARE@numSELECT@num=COUNT(*)FROMPRINT:'+CAST(@numASCHAR(3))DECLARE@numBEGIN INSERTINTOworkerVALUES(20,'陈立','女PRINTS