數(shù)據(jù)庫原理例題及解答

數(shù)據(jù)庫原理例題及解答數(shù)據(jù)庫原理例題及解答數(shù)據(jù)庫原理例題及解答數(shù)據(jù)庫原理例題及解答編制僅供參考審核批準(zhǔn)生效日期地址：電話：傳真:郵編:實驗?zāi)康模毫私釹QL語言的特點(diǎn)和基本概念。能夠針對某種具體的DBMS（本實驗采用Access2003），熟練地運(yùn)用單表查詢、連接查詢、嵌套查詢、集合查詢等各種SQL查詢語句對數(shù)據(jù)庫中的表進(jìn)行操作。對相同的查詢要求，能夠采用多種查詢方法實現(xiàn)，并能分析各種方法的優(yōu)劣，從中選擇合適的方法。實驗過程：Printthenamesofprofessorswhoworkindepartmentsthathavefewerthan50PhDstudents.解：（1）分析：本題是查詢在博士生人數(shù)少于50個人的系工作的教師名字。查詢教授名字可以通過prof表，而所查詢的教授名字是有限制條件的， 他所工作的系名要屬于特定的集合（所有系名的一個子集），顯然這個子集可以通過查詢dept表獲得，所以帶有謂詞in的嵌套子查詢可以實現(xiàn)題目要求。 （2）語句實現(xiàn)：SELECTProf.字段1FROMProfWHEREProf.字段2IN(SELECTDept.字段1FROMDeptWHEREDept.字段2<50);****************************************************; （3）查詢結(jié)果：Printthename(s)ofstudent(s)withthelowestgpa解：（1）分析：本題是查詢成績點(diǎn)最低的學(xué)生的名字。最低的成績點(diǎn)可以在student表中通過函數(shù)min(gpa)獲得，而所查詢學(xué)生的名字的限制為成績點(diǎn)等于min(gpa),因此可用如下嵌套子查詢實現(xiàn)。 （2）語句實現(xiàn)：SELECTStudent.字段2FROMStudentWHEREStudent.字段6IN(SELECTmin(Student.字段6)FROMStudent);****************************************************; （3）查詢結(jié)果：ForeachComputerSciencesclass,printthecno,sectno,andtheaveragegpaofthestudentenrolledintheclass.解：（1）分析：本題是查詢計算機(jī)科學(xué)系的所有班的課程號、分班號、班上學(xué)生的平均績點(diǎn)。計算機(jī)科學(xué)系的所有班可以通過section表獲得,而通過enroll表可以由section表中的dname,cno,sectno獲得班上所有學(xué)生的sid，而通過sid可以在student表中查得學(xué)生成績點(diǎn)，最后由cno,sectno進(jìn)行分組，并用函數(shù)avg(gpa)，獲得每組的平均成績。所以可用三個表的連接查詢，并適當(dāng)分組實現(xiàn)查詢要求。（2）語句實現(xiàn)：SELECTSection.字段2,Section.字段3,avg(Student.字段6)ASavStudent字段6FROM[Section],Enroll,StudentWHERE((Section.字段1='ComputerSciences')and(Section.字段2=Enroll.字段4)and=)GROUPBYSection.字段2,Section.字段3;****************************************************;（3）查詢結(jié)果：4、Printthecoursenames,coursenumbersandsectionnumbersofallclasseswithlessthansixstudentsenrolledinthem.解：（1）分析：本題是查詢所有班級人數(shù)少于6的課程名，課程號，分班號。通過section表可以查詢出所有的班，其中的課程名可由查詢所得的dname,cno在course表中確定，因為與班級人數(shù)有關(guān)，還需將section表和enroll表做連接，并按,,分組以獲取所有班的人數(shù)。所以可用連接查詢、嵌套查詢，并適當(dāng)分組來實現(xiàn)查詢要求。（2）語句實現(xiàn)：SELECT(SELECTCourse.字段2fromCoursewhereCourse.字段1=Section.字段2andCourse.字段3=Section.字段1)ASCourse字段2,Section.字段2,Section.字段3FROM[Section],EnrollWHERE(Section.字段2=Enroll.字段4ANDsection.字段1=enroll.字段3ANDsection.字段3=enroll.字段5)GROUPBYSection.字段2,Section.字段3,Section.字段1HAVINGcount(*)<6;****************************************************; （3）查詢結(jié)果：5、Printthename(s)andsid(s)ofstudent(s)enrolledinthemostclasses.解：（1）分析：本題是查詢加入班級數(shù)最多的學(xué)生名字、學(xué)號?？刹捎门R時表的方法解決。建立一個臨時表包括字段學(xué)生名字、學(xué)號和所選課程的數(shù)目（num）。再對這個臨時表進(jìn)行查詢（名字、學(xué)號），限制條件是所選課程數(shù)目等于max(num)。（2）語句實現(xiàn)：SELECT,FROM(SELECT,,countASnumFROMenroll,studentWHERE=GROUPBY,ASTempWHERE=(SELECTmax(num)From(SELECTsid,(SELECTsnameFROMStudentwheresid=ASsname,count(*)AsnumFROMEnrollgroupby);****************************************************(3)查詢結(jié)果：6、Printthenamesofdepartmentsthathaveoneormoremajorswhoareunder18yearsold. 解：（1）分析：本題是查詢所含學(xué)生至少有一個年齡小于18歲的系的名稱。在major 表中可以得到每個學(xué)生所屬專業(yè)情況，該系存在學(xué)生年齡小于18歲的學(xué)生（通過student表）則該系滿足要求。因此可用帶有exists的嵌套子查詢實現(xiàn)要求。（2）語句實現(xiàn)：SELECTdnameFROMmajorWHEREexists(SELECT*FROMStudentwhere<18and=;****************************************************;（3）查詢結(jié)果：7、PrintthenamesandmajorsofstudentswhoaretakingoneoftheCollegeGeometrycourses. 解：（1）分析：本題查詢所有選了CollegeGeometrycourses的學(xué)生的姓名和所在系名。此題對課程名需要用通配符進(jìn)行模糊查詢。學(xué)生的限制條件是存在一門CollegeGeometrycourse為他所選。因此還需用到帶有exists的嵌套子查詢。（2）語句實現(xiàn)：SELECT(SELECTsnameFROMStudentwheresid=ASsname,dnameFROMmajorWHEREexists(SELECT*fromCourse,Enrollwhere='Mathematics'andin(461,462)and=;****************************************************;（3）查詢結(jié)果：8、ForthosedepartmentsthathavenomajorstakingaCollegeGeometrycourse,printthedepartmentnameandthenumberofPhDstudentsinthedepartment.解：（1）分析：本題是查詢所含學(xué)生都沒有選CollegeGeometrycourse的系的名稱和該系的博士生人數(shù)。本題思路和方法與上題基本一致。 （2）語句實現(xiàn)：SELECTdname,numphdsFROMDeptWHEREnotexists(SELECT*fromCourse,Enrollwhere='Mathematics'andin(461,462)and=; ****************************************************; （3）查詢結(jié)果：9、PrintthenamesofstudentswhoaretakingbothaComputerSciencescourseandaMathematicscourse.解：（1）分析：本題是查詢既選了計算機(jī)科學(xué)系課程又選了數(shù)學(xué)系課程的學(xué)生姓名是求兩個集合交的問題。因為一般的DBMS沒有實現(xiàn)交操作，可以先根據(jù)一個條件獲取一個集合然后根據(jù)第二個條件篩選，即第一個集合要選取也在第二個條件產(chǎn)生的集合元素中。因此可以用帶有in的嵌套子查詢完成要求。 （2）語句實現(xiàn)：SELECTFROMStudent,EnrollWHERE=and='Mathematics'andin(SELECTFROMStudent,EnrollWHERE=and='ComputerSciences');****************************************************; （3）查詢結(jié)果：10、PrinttheagedifferencebetweentheoldestandyoungestComputerSciencesmajor(s).解：（1）分析：本題是查詢計算機(jī)科學(xué)性年齡最大的學(xué)生和年齡最小的學(xué)生之間的年齡差。思想還是通過先建立一張臨時表，包括字段系名、學(xué)號、年齡。再在這張臨時表里利用max(age)，min(age)獲取最大年齡和最小年齡，進(jìn)行相減運(yùn)算。 （2）語句實現(xiàn)：SELECTmax(age)-min(age)ASdifferenceFROM(SELECTdname,,ageFROMMajor,StudentWHERE='ComputerSciences'and=;***************************************************;（3）查詢結(jié)果：11、ForeachdepartmentthathasoneormoremajorswithaGPAunder,printthenameofthedepartmentandtheaverageGPAofitsmajors.解：（1）分析：本題是查詢系里有學(xué)生績點(diǎn)小于的系的系名和該系學(xué)生的平均績點(diǎn)。本題主要是對major，student兩張表按sid做連接，連接后再按dname分組，但要注意將min(gpa)<1的分組剔除。 （2）語句實現(xiàn)：SELECTdname,avg(gpa)ASavgpaFROMMajor,StudentWHEREdnamein(SELECTdnamefromMajor,StudentWHERE=and<GROUPBYdname)and=GROUPBYdname; ****************************************************; （3）查詢結(jié)果：12、Printtheids,names,andGPAsofthestudentswhoarecurrentlytakingalloftheCivilEngineeringcourses.解：（1）分析：本題查詢選修了所有CivilEngineering系課程的學(xué)生的學(xué)號、姓名、成績點(diǎn)。即所查詢學(xué)生的限制條件為沒有一門CivilEngineering系的課程該學(xué)生是沒有選的。將肯定形式的查詢要求轉(zhuǎn)化為等價的雙重否定形式，便于SQL語言求解。 （2）語句實現(xiàn)：SELECTsid,sname,gpaFROMStudent