南京曉莊學(xué)院《數(shù)據(jù)庫(kù)原理與應(yīng)用》課程實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)六分組統(tǒng)計(jì)查詢和集合查詢?cè)O(shè)計(jì)所在院(系):數(shù)學(xué)與信息技術(shù)學(xué)院班級(jí)：學(xué)號(hào)：姓名：1.實(shí)驗(yàn)?zāi)康氖炀氄莆諗?shù)據(jù)查詢中分組條件表達(dá)、選擇組條件的表達(dá)方法。熟練使用統(tǒng)計(jì)函數(shù)和分組函數(shù)。熟練各類計(jì)算和分組計(jì)算的查詢操作方法。掌握集合查詢的實(shí)現(xiàn)方法。2.實(shí)驗(yàn)要求針對(duì)“TM〞數(shù)據(jù)庫(kù)，在SQLServer查詢分析器中，用T-SQL語(yǔ)句實(shí)現(xiàn)以下查詢操作：查詢各個(gè)院系學(xué)生的總?cè)藬?shù)，并按人數(shù)進(jìn)行降序排列。查詢各系各門課程的平均成績(jī)。查詢每個(gè)院系各種職稱的教師人數(shù)，輸出院系、職稱、人數(shù)。查詢數(shù)學(xué)與信息技術(shù)學(xué)院學(xué)生的平均年齡。查詢07294003課程的最高分和最低分。查詢選修人數(shù)超過(guò)30人，且課程號(hào)以07開頭的課程號(hào)、課程名稱和選修人數(shù)。按選修人數(shù)降序排列。查詢選修了5門以上課程的學(xué)生學(xué)號(hào)。查詢年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡。查詢SC表中最高分與最低分之差大于20分的課程號(hào)。查詢平均成績(jī)大于75分的課程的課程號(hào)、課程名、平均分。查詢期末考試平均分排名前10%的學(xué)生，輸出學(xué)號(hào)和平均分。查詢教師人數(shù)最多的前3個(gè)院系，輸出院系和教師人數(shù)。查詢?nèi)＠蠋熀蛯W(xué)生的姓名，輸出姓名和類別兩列〔類別中顯示教師或?qū)W生〕，結(jié)果按類別排序。用集合查詢實(shí)現(xiàn)同時(shí)講授過(guò)07294003和07295007兩門課的老師的工號(hào)。用集合查詢實(shí)現(xiàn)教師表中職稱不是教授的老師的詳情。按要求完成實(shí)驗(yàn)報(bào)告。3.實(shí)驗(yàn)步驟、結(jié)果和總結(jié)實(shí)驗(yàn)步驟/結(jié)果將調(diào)試成功的T-SQL語(yǔ)句寫在下方〔標(biāo)明題號(hào)〕。(1) 針對(duì)“TM〞數(shù)據(jù)庫(kù)，在SQLServer查詢分析器中，用T-SQL語(yǔ)句實(shí)現(xiàn)以下查詢操作：a) 查詢各個(gè)院系學(xué)生的總?cè)藬?shù)，并按人數(shù)進(jìn)行降序排列。selectDEPT_ID,COUNT(*)總?cè)藬?shù)fromStudentgroupbyDEPT_IDorderbyCOUNT(*)descb) 查詢各系各門課程的平均成績(jī)。selects.dept_id,sc.c_id,c_name,avg(exam_grade)asavg_gradefromsc,coursec,studentswheresc.c_id=c.c_idands.s_id=sc.s_idgroupbys.dept_id,sc.c_id,c_namec) 查詢每個(gè)院系各種職稱的教師人數(shù)，輸出院系、職稱、人數(shù)。selectDEPT_ID,Academic_Title,count(Academic_Title)as'人數(shù)'fromTeachergroupbyDEPT_ID,Academic_Titled) 查詢數(shù)學(xué)與信息技術(shù)學(xué)院學(xué)生的平均年齡。selectAVG(year(getdate())-year(Date_of_Birth))as平均年齡fromStudent,DepartmentwhereStudent.DEPT_ID=Department.DEPT_IDandDEPT_Name='數(shù)學(xué)與信息技術(shù)學(xué)院'e) 查詢07294003課程的最高分和最低分。selectMAX(EXAM_Grade)as最高分,MIN(EXAM_Grade)as最低分fromSCwhereC_ID='07294003'f) 查詢選修人數(shù)超過(guò)30人，且課程號(hào)以07開頭的課程號(hào)、課程名稱和選修人數(shù)。按選修人數(shù)降序排列。selectCourse.C_ID,Course.C_Name,count(*)人數(shù)fromSC,CoursewhereSC.C_ID=Course.C_IDandCourse.C_IDlike'07%'groupbyCourse.C_ID,Course.C_Namehavingcount(*)>30orderbycount(*)descg) 查詢選修了5門以上課程的學(xué)生學(xué)號(hào)。selects.s_idfromstudentsjoinscons.s_id=sc.s_idgroupbys.s_idhavingcount(*)>5;h) 查詢年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡。selectS_NAME,year(getdate())-year(Date_of_Birth)as年齡fromstudentwhereGender='男'andyear(getdate())-year(Date_of_Birth)>(selectavg(year(getdate())-year(Date_of_Birth))fromstudentwhereGender='女')i) 查詢SC表中最高分與最低分之差大于20分的課程號(hào)。selectc_idfromscgroupbyc_idhavingmax(exam_grade)-min(exam_grade)>20j) 查詢平均成績(jī)大于75分的課程的課程號(hào)、課程名、平均分。selectsc.c_id,c_name,avg(exam_grade)as平均成績(jī)fromscjoincourseconsc.c_id=c.c_idgroupbysc.c_id,c_namehavingavg(exam_grade)>75k) 查詢期末考試平均分排名前10%的學(xué)生，輸出學(xué)號(hào)和平均分。selecttop10percentS_ID,avg(avg_grade)平均成績(jī)fromSCgroupbyS_IDorderbyavg(avg_grade)descl) 查詢教師人數(shù)最多的前3個(gè)院系，輸出院系和教師人數(shù)。selecttop3Department.DEPT_ID,COUNT(*)人數(shù)fromTeacher,DepartmentwhereTeacher.DEPT_ID=Department.DEPT_IDgroupbyDepartment.DEPT_IDorderbycount(*)descm) 查詢?nèi)＠蠋熀蛯W(xué)生的姓名，輸出姓名和類別兩列〔類別中顯示教師或?qū)W生〕，結(jié)果按類別排序。selectt_nameaspname,'教師'asptypefromteacherunionselects_nameaspname,'學(xué)生'asptypefromstudentorderbyptypen) 用集合查詢實(shí)現(xiàn)同時(shí)講授過(guò)07294003和07295007兩門課的老師的工號(hào)。selectt_idfromtcwherec_id='07294003'intersectselectt_idfromtcwherec_id='07295007'o) 用集合查詢實(shí)現(xiàn)教師表中職稱不是教授的老師的詳情。select*fromteacherexcept(select*fromteacherwhereacademic_title='教授')4．實(shí)驗(yàn)思考：①SELECT命令中，HAVING子句和WHERE子句表示的篩選條件有何不同？HAVING指定組或聚合的搜索條件。通常與GROUPBY子句一起使用。指定組要滿足的搜索條件。搜索條件可以使用聚合表達(dá)式和非聚合表達(dá)式。只有在GROUPBY子句中指定為分組列的列才能用在非聚合表達(dá)式中。這是因?yàn)榉纸M依據(jù)列表示整個(gè)組的通用屬性。同樣，聚合表達(dá)式也表示整個(gè)組的通用屬性。HAVING子句搜索條件表達(dá)的是組屬性上的謂詞。如果不使用GROUPBY子句，那么HAVING的行為與WHERE子句一樣。WHERE指定查詢返回的行的搜索條件。定義要返回的行應(yīng)滿足的條件。對(duì)搜索條件中可以包含的謂詞數(shù)量沒有限制。不可以包含聚合表達(dá)式！②使用GROUPBY〔分組條件〕子句后，語(yǔ)句中的統(tǒng)計(jì)函數(shù)的運(yùn)行結(jié)果有什么不同？使用后先按照分組條件對(duì)數(shù)據(jù)進(jìn)行分組，再分別對(duì)各個(gè)組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)運(yùn)算。即統(tǒng)計(jì)函數(shù)將分別計(jì)算每組的匯總值。③組合查詢是否能用其他語(yǔ)句代替？有何不同？將兩個(gè)或更多查詢的結(jié)果合并為單個(gè)結(jié)果集，該結(jié)果集包含聯(lián)合查詢中的所有查詢的全部行。UNION運(yùn)算不同于使用聯(lián)接合并兩個(gè)表中的列的運(yùn)算。不能代替，因?yàn)槁?lián)接合并更改了表的關(guān)系模式，即改變了列數(shù)。UNION本身并不改變表結(jié)構(gòu)。④用UNION〔或UNIONALL〕進(jìn)行組合查詢時(shí)，有哪些根本規(guī)那么？1.所有查詢中的列數(shù)