《数据库系统》实验报告三学号姓名实验时间2014-11-26实验名称数据查询实验学时4准备材料1.SQLPlus命令手册2.Oracle数据字典扩展实验1.利用企业管理器的图形界面构造查询语句，并察看查询结果2.利用企业管理器完成视图、索引的创建与使用。3.利用DBMS进行对第三章习题所设计SQL语句的检查(此部分内容不要求在实验室完成，不用写入实验报告。)实验环境Oracle9i(及以上版本)服务器SQLPlus/SQLPlusworksheet客户端实验目的1．掌握使用SQL语句进行数据查询的方法2.掌握视图的创建与使用方法3.观察索引的使用效果实验内容及步骤1.使用University数据库的数据库结构和数据（smallRelations即可），完成下列查询：(1)FindthenamesofcoursesinComputersciencedepartmentwhichhave3creditsSELECTtitleFROMcourseWHEREdept_name='Comp.Sci.'ANDcredits=3(2)ForthestudentwithID12345(oranyothervalue),showallcourse_idandtitleofallcoursesregisteredforbythestudent.SELECTcourse_id,titleFROMtakesNATURALJOINcourseWHEREid=123454.Asabove,butshowthetotalnumberofcreditsforsuchcourses(takenbythatstudent).Don'tdisplaythetot_credsvaluefromthestudenttable,youshoulduseSQLaggregationoncoursestakenbythestudent.SELECTid,SUM(credits)FROMtakesNATURALJOINstudentNATURALJOINcourseWHEREid=12345GROUPBYid;(3)Asabove,butdisplaythetotalcreditsforeachofthestudents,alongwiththeIDofthestudent;don'tbotheraboutthenameofthestudent.(Don'tbotheraboutstudentswhohavenotregisteredforanycourse,theycanbeomitted)SELECTid,SUM(credits)FROMtakesNATURALJOINstudentNATURALJOINcourseGROUPBYid(4)FindthenamesofallstudentswhohavetakenanyComp.Sci.courseever(thereshouldbenoduplicatenames)SELECTDISTINCTid,NAMEFROMtakesNATURALJOINstudentWHEREcourse_idIN(SELECTcourse_idFROMcourseWHEREdept_name='Comp.Sci.')(5)DisplaytheIDsofallinstructorswhohavenevertaughtacourse(Notesad1)Oracleusesthekeywordminusinplaceofexcept;(2)interpret"taught"as"taughtorisscheduledtoteach")SELECTidFROMinstructorWHEREidNOTIN(SELECTDISTINCTidFROMteaches)(6)Asabove,butdisplaythenamesoftheinstructorsalso,notjusttheIDs.SELECTid,NAMEFROMinstructorWHEREidNOTIN(SELECTDISTINCTidFROMteaches)(7)Findthemaximumandminimumenrollmentacrossallsections,consideringonlysectionsthathadsomeenrollment,don'tworryaboutthosethathadnostudentstakingthatsectionSELECTmax(enrollment),min(enrollment)from(SELECTsec_id,semester,year,COUNT(DISTINCTid)asenrollmentFROMtakesGROUPBYsec_id,semester,YEAR);(8)AsininQ1,butnowalsoincludesectionswithnostudentstakingthem;theenrollmentforsuchsectionsshouldbetreatedas0.Dothisintwodifferentways(andcreaterequiredatafortesting)1).Usingascalarsubquery2).Usingaggregationonaleftouterjoin(usetheSQLnaturalleftouterjoinsyntax)SELECTDISTINCTsec_id,semester,YEAR,IFNULL(`count`,0)FROMsectionLEFTOUTERJOIN(SELECTsec_id,semester,YEAR,COUNT(DISTINCTid,sec_id,semester,YEAR)AS'count'FROMtakesGROUPBYsec_id,semester,YEAR)ASTUSING(sec_id,semester,YEAR)(9)Findallcourseswhoseidentifierstartswiththestring"CS-1"SELECT*FROMcourseWHEREcourse_idLIKE'CS-1%'(10)Findinstructorswhohavetaughtalltheabovecourses1).Usingthe"notexists...except..."structure2).Usingmatchingofcountswhichwecoveredinclass(don'tforgetthedistinctclause!)selectdistinctID,namefromteachesnaturaljoininstructorwherenotexists((selectcourse_idfromcourse)except(selectcourse_idfromcoursewherecourse_idlike'CS-1%'));2.TheuniversityrulesallowanFgradetobeoverriddenbyanypassgrade(A,B,C,D).Now,createaviewthatlistsinformationaboutallfailgradesthathavenotbeenoverridden(theviewshouldcontainallattributesfromthetakesrelation).CREATEVIEWFASSELECT*FROMtakesWHEREgrade='F'3.Findallstudentswhohave2ormorenon-overriddenFgradesasperthetakesrelation,andlistthemalongwiththeFselectname,'F'asfinal_gradefromFnaturaljoinstudentgroupbynamehavingcount(grade)>=2;选择数量〉=1时有一个结果出现问题解决方案（列出遇到的问题及其解决方法）
