數據庫原理實驗6六

GROUPBYBirthDateHAVINGORDERBYSELECTEmployeeID,HireDate,DATEDIFF(DD,HireDate,GETDATE())ASLengthOfEmploymentFROMHumanResources.Employee實驗6-2用函數實現(xiàn)分USEAdventureWorksDECLARE@PageSizeInt,@PageIndexInt,@PageStartInt,@PageEndIntSET@PageSize=10--每頁10行SET@PageIndex=2--顯示第2SET@PageStart=@PageSize*(@PageIndex-1)+1SET@PageEnd=@PageSize*@PageIndexselect*fromwhereEmployeeIDBETWEEN@PageStartAND@PageEndDECLARE@PageSizeInt,@PageIndexIntSET@PageSize=10--每頁10行SET@PageIndex=2--顯示第2selecttop(@PageSize)*(selectrow_number()over(orderbyEmployeeID)ASrownumber,*fromHumanResources.Employee)ASawhererownumber>(@PageIndex-1)*@PageSize實驗6-3用自定義函數顯示學生的所有課程平均設計一個函數USESelectCoursesCREATEFUNCTIONufnSumScore(@StudentIDchar(9))RETURNSFLOATDECLARE@AvgScoreFloatSELECT@AvgScore=AVG(Score)FROMChoicesWHEREStudentID=@StudentIDRETURN@AvgScore SELECT ')AS6-4基于選課系統(tǒng)數據庫的臨時表和CTE實2)查詢選修了所有課程的學生USESelectCoursesCREATETABLE#StuSelectAll(StudentIDchar(9),ChoiceCountint)INSERTINTOSELECTStudentID,COUNT(*)ASChoiceCountFROMChoicesGROUPBYHAVINGCOUNT(*)=(SELECTCOUNT(*)FROMCourse)SELECTFROM#StuSelectAllJOINStudentON#StuSelectAll.StudentID=Student.IDWITHStuSelectAll(SELECTStudentID,COUNT(*)ASChoiceCountFROMChoicesGROUPBYHAVINGCOUNT(*)=(SELECTCOUNT(*)FROMSELECTFROMStuSelectAllJOINStudentONStuSelectAll.StudentID=Student.IDCREATETABLE#GrandpaCourse(SonCourseIDint,FatherCourseIDint,GrandpaCourseIDINSERTINTOSELECTDISTINCTSonCourse.CourseID,FatherCourse.CourseID,GrandpaCourse.CourseIDFROMTeaching nASSonCourseJOINTeaching nASFatherCourseONJOINTeaching nASGrandpaCourseONSELECTCourse.*,#GrandpaCourse.FatherCourseIDASFatherCourseID,#GrandpaCourse.GrandpaCourseIDASGrandpaCourseIDFROMCourseJOIN#GrandpaCourseONCourse.ID=#GrandpaCourse.SonCourseIDWITHGrandpaCourse(SELECTDISTINCTSonCourse.CourseIDASSonCourseID,FatherCourse.CourseIDASFatherCourseID,GrandpaCourse.CourseIDASGrandpaCourseIDFROMTeaching nASSonCourseJOINTeaching nASFatherCourseONJOINTeaching nASGrandpaCourseONSELECTCourse.*,GrandpaCourse.FatherCourseIDASFatherCourseID,GrandpaCourse.GrandpaCourseIDASGrandpaCourseIDFROMCourseJOINGrandpaCourseONCourse.ID=GrandpaCourse.SonCourseID6-5CTE合實USEAdventureWorksCREATETABLETestData(Idint,TestDatavarchar(20));INSERTINTOTestDataVALUES(1,'Test1');INSERTINTOTestDataINSERTINTOTestDataVALUES(3,'Test3');INSERTINTOTestDataVALUES(1,'Test1');INSERTINTOTestDataVALUES(2,'Test2');INSERTINTOTestDataVALUES(4,'Test4');INSERTINTOTestDataVALUES(5,'Test5');INSERTINTOTestDataVALUES(6,'Test6');WITHDeleteDuplicate(SELECTId,TestData,ROW_NUMBER()OVER(PARTITIONBYId,TestDataORDERBYId)DuplicateCountFROMDELETEFROMDeleteDuplicateWHEREDuplicateCount>1;SELECT*FROM2）CTECREATETABLE (IDint,Namevarchar(30),Motherint,Fatherint);INSERTINTO VALUES(1,'Jack',NULL,NULL);INSERTINTO VALUES(2,'Susan',NULL,NULL);INSERTINTO VALUES(3,'Bob',1,2);INSERTINTO VALUES(4,'Jimmy',1,2);INSERTINTO VALUES(5,'Frank',3,NULL);INSERTINTO VALUES(6,'Me',NULL,3);INSERTINTO VALUES(7,'Sun',6,8);INSERT VALUESWITHGenealogy(ID)(SELECTMotherFROM WHEREName='Sun'SELECTFatherFROM WHEREName='Sun'UNIONALLSELECTP.MotherFROM P,GenealogyGWHEREP.ID=G.IDUNIONSELECTP.FatherFROM P,GenealogyGWHEREP.ID=G.ID)SELECTP.ID,P.Name,P.Mother,P.FatherFROMGenealogyG, WHEREP.ID=實驗6-6基于選課系統(tǒng)數據庫的流程控制和異常處理實在開課計劃表中查詢每個教師的、開課數量和工作量情況USESelectCoursesWITHTeacherLoadSELECTTeacherID,COUNT(Teaching n.ID)ASCourseNumberFROMTeaching GROUPBYSELECTTeacher.Name,TeacherLoad.CourseNumber,CASEWHENTeacherLoad.CourseNumber<=2THEN合適'WHENTeacherLoad.CourseNumber>=3ANDTeacherLoad.CourseNumber<=5THEN負荷WHENTeacherLoad.CourseNumber>5THEN嚴重超負荷'ELSE'不滿足'ENDASFROMTeacherLoadJOINTeacherONT-SQLDECLARE@Mul1int,@Mul2intSET@Mul1=1SET@Mul2=1WHILE@Mul1<10SET@Mul2=1WHILE@Mul2<10IF@Mul2>=@Mul1SET

SET 實驗心得CTE的生命周期是產生這個臨時表的關閉時自動刪除，CTE的生命周期是在第一次使用后就消亡。對的遞歸用法還不是很理解，課后會再找些相關題目理解一下。6-4（5）USESelectCoursesSELECTStudentID,TeachingID,ScoreINTO#HighScoreStuFROMChoicesWHEREScore=(SELECTMAX(ScoreFROMChoices