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

1、1、了解SQL語言的特點和基本概念。2、 能夠針對某種具體的DBMS(本實驗采用Access2003),熟練地運用單表查詢、連接查詢、嵌套查詢、集合查詢等各 種SQL查詢語句對數(shù)據(jù)庫中的表進(jìn)行操作。3、 對相同的查詢要求，能夠采用多種查詢方法實現(xiàn)，并能分析各種方法的優(yōu)劣，從中選擇合適的方法。、實驗過程：1、Print the names of professors who work in departments that have fewer than 50 PhD students.解：(1)分析：本題是查詢在博士生人數(shù)少于50個人的系工作的教師名字。查詢教授名字可以通過prof表，而所查

2、詢的教授名字是有限制條件的，他所工作的系名要屬于特定的集合(所有系名的一個子集)，顯然這 個子集可以通過查詢dept表獲得，所以帶有謂詞in的嵌套子查詢可以實現(xiàn)題目要求。(2) 語句實現(xiàn)：SELECT Prof.字段 1FROM ProfWHERE Prof.字段 2 IN (SELECT Dept.字段 1 FROM Dept WHERE Dept.字段 2 < 50);.J(3) 查詢結(jié)果：2、Print the name(s) of student(s) with the lowest gpa解：(1)分析：本題是查詢成績點最低的學(xué)生的名字。最低的成績點可以在student表中通過

3、函數(shù)min(gpa)獲得， 而所查詢學(xué)生的名字的限制為成績點等于min(gpa),因此可用如下嵌套子查詢實現(xiàn)。(2) 語句實現(xiàn)：SELECT Student.字段 2FROM StudentWHERE Student.字段 6 IN(SELECT min(Student.字段 6)FROM Student);/jS/jS.(3) 查詢結(jié)果：3、For each Computer Sciences class, print the eno, sect no, and the average gpa of the stude nt en rolled in the class. 解：(1)分析：本

4、題是查詢計算機科學(xué)系的所有班的課程號、分班號、班上學(xué)生的平均績點。計算機科學(xué)系的所有班可以通過section表獲得，而通過enroll表可以由section表中的dname, eno, sectno獲得班上所 有學(xué)生的sid，而通過sid可以在student表中查得學(xué)生成績點，最后由eno, sectno進(jìn)行分組， 并用函數(shù)avg(gpa)，獲得每組的平均成績。所以可用三個表的連接查詢，并適當(dāng)分組實現(xiàn)查詢要 求。(2)語句實現(xiàn)：SELECT Section.字段 2,Section.字段 3,avg(Student.字段 6) AS avStudent 字段 6FROM Section,Enr

5、oll.StudentWHERE (Section.字段 1 ='Computer Sciences,)and(Section.字段 2=Enroll.字段 4)and=)GROUP BY Section.字段 2,Section.字段 3;%?>.J(3)查詢結(jié)果：4、Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.解：(1)分析：本題是查詢所有班級人數(shù)少于6的課程名，課程號，分班號。通過s

6、ection表可以查詢出所有的班，其中的課程名可由查詢所得的dname, eno在course表中確定，因為與班級人數(shù)有 矢，還需將section表和enroll表做連接，并按，分組以獲取所有班的人數(shù)。所以可用連接查 詢、嵌套查詢，并適當(dāng)分組來實現(xiàn)查詢要求。(2) 語句實現(xiàn)：SELECT (SELECT Course.字段 2 from Coursewhere Course.字段 1=Section.字段 2 and Course.字段 3=Section.字段 1)AS Course 字段 2,Section.字段 2,Section.字段 3FROM Section,EnrollWHERE

7、 (Section.字段 2=Enroll.字段 4AND section.字段 1=enroll.字段 3 AND section.字段 3=enroll.字段 5)GROUP BY Section.字段 2,Section.字段 3,Section.字段 1 HAVING count(*)<6;%!*.J(3) 查詢結(jié)果：5、Print the name(s) and sid(s) of student(s) enrolled in the most classes.解：(1)分析:本題是查詢加入班級數(shù)最多的學(xué) 生名字、學(xué)號?？刹捎门R時表的方法解決。建立一個臨時表包括字段學(xué)生名字、學(xué)

8、號和所選課程的數(shù)目(num)。再對這個臨時表進(jìn)行查詢(名字、學(xué)號)，限制條件是所選課程數(shù)目等于max(num)。(2)語句實現(xiàn)：SELECT ,FROM (SELECT ,count AS numFROM enroll,studentWHERE=GROUP BY , AS TempWHERE = (SELECT max(num) From (SELECT sid,(SELECT sname FROM Student where sid= AS sname,count(*)As num FROM Enroll group by );(3)查詢結(jié)果：6 、Print the names of de

9、partments that have one or more majors who are under 18 years old.解：(1)分析：本題是查詢所含學(xué)生至少有一個年齡小于18歲的系的名稱。在major表中可以得 到每個學(xué)生所屬專業(yè)情況，該系存在學(xué)生年齡小于18歲的學(xué)生(通過 student表)則該系滿足要求。因此可用帶有exists的嵌套子查詢實現(xiàn)要求。(2)語句實現(xiàn)：SELECT dnameFROM majorWHERE exists (SELECT*FROM Student where <18 and =;*拳*拳*寧* 3)查詢結(jié)果：7 、 Print the nam

10、es and majors of students who are taking one of the College Geometry courses.解：(1)分析：本題查詢所有選了 College Geometry courses的學(xué)生的姓名和所在系名。此題對課程名需要用通配 符進(jìn)行模糊查詢。學(xué)生的限制條件是存在一門College Geometry course為他所 選。因此還需用到 帶有exists的嵌套子查詢。(2)語句實現(xiàn)：SELECT (SELECT sname FROM Student where sid= AS sname, dname FROM majorWHERE ex

11、ists (SELECT*from Course,Enroll where Mathematics' and in (461,462) and =;/jS.J(3 )查詢結(jié)果：8 、For those departments that have no majors taking a College Geometry course, print the department name and thenumber of PhD students in the department.解：(1 )分析：本題是查詢所含學(xué)生都沒有選College Geometry course的系的名稱和該系的博

12、士生人數(shù)。本題思路和方法與上題基本一致。(2) 語句實現(xiàn)：SELECTdname, numphdsFROM DeptWHERE not exists (SELECT*from Course,Enroll where Mathematics' and in (461,462) and =;/jS.J(3) 查詢結(jié)果：9、Print the names of students who are taking both a Computer Sciences course and a Mathematics course.解:(1)分析：本題是查詢既選了計算機科學(xué)系課程又選了數(shù)學(xué)系課程的學(xué)生姓

13、名是求兩個集合交的問題。因為一般的DBMS沒有實現(xiàn)交操作，可以先根據(jù)一個條件獲取一個集合然后根據(jù)第二個條件篩選，即第一個集合要選取也在第二個條件產(chǎn)生的集合元素中。因此可以用帶有in的嵌套子查詢完成要求。(2) 語句實現(xiàn)：SELECTFROM Student,EnrollWHERE = andMathematics' and in (SELECT FROM Student.Enroll WHERE = and ='Computer Sciences');.J(3) 查詢結(jié)果：10、Print the age d iff ere nee betwee n the oldes

14、t and youn gest Computer Scie nces major(s).解：(1)分析：本題是查詢計 算機科學(xué)性年齡最大的學(xué)生和年齡最小的學(xué)生之間的年齡差。思想還是通過先建立一張臨時表，包括字段系名、學(xué)號、 年齡。再在這張臨時表里利用max(age)，min(age)獲取最大年齡和最小年齡，進(jìn)行相減運算。(2)語句實現(xiàn)：SELECT max(age)-min(age) AS differenceFROM (SELECT dname, ageFROM Major,StudentWHERE ='Computer Sciences1 and =;*?* *?* %!* %!*

15、 *1 *1 *1jS.(3 )查詢結(jié)果:11、For each department that has one or more majors with a GPAunder, print the nameof the department and the average GPA of its majors.解：(1)分析：本題是查詢系里有學(xué)生績點小于的系的系名和該系學(xué)生的平均績點。本題主要是對major，student兩張表按sid做連接，連接后再按dname分組，但要注意將min (gpa) <1的分組剔除。(2) 語句實現(xiàn)：SELECT dname,avg (gpa) AS avg

16、paFROM Major,StudentWHERE dname in (SELECT dname from Major,Student WHERE = and < GROUP BYdname) and = GROUP BY dname;.J(3) 查詢結(jié)果：12、Print the ids, names, and GPAsof the students who are currently taking all of the Civil Engineering courses.解：(1 )分析：本題查詢選修了所有Civil Engineering系課程的學(xué)生的學(xué)號、姓名、成績點。即所查詢學(xué)生的限制條件為沒有一門Civil Engineering系的課程該學(xué)生是沒有選的。將肯定形式的查詢要求轉(zhuǎn) 化為等價的雙重否定形式，便于SQL語言求解。(2)語句實現(xiàn)：SELECT sid,sname,gpaFROM Stud