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

1、.一、實驗?zāi)康模?、 了解 SQL 語言的特點和基本概念。2、 能夠針對某種具體的DBMS （本實驗采用Access2003），熟練地運用單表查詢、連接查詢、嵌套查詢、集合查詢等各種SQL 查詢語句對數(shù)據(jù)庫中的表進行操作。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 個人的系工作的教師名字。查詢教授

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

3、名字。最低的成績點可以在student 表中通過函數(shù)min(gpa)獲得，而所查詢學生的名字的限制為成績點等于min(gpa), 因此可用如下嵌套子查詢實現(xiàn)。（ 2）語句實現(xiàn)：SELECT Student. 字段 2.FROM StudentWHEREStudent.字段 6 IN(SELECT min(Student. 字段 6)FROM Student);*;（ 3）查詢結(jié)果：3、 For each Computer Sciences class, print the cno, sectno, and the average gpa of the student enrolled in t

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

5、avStudent 字段 6 FROM Section,Enroll,StudentWHERE(Section.字段1='ComputerSciences')and(Section.字段2=Enroll.字段4)and(Enroll.ID=Student.ID)GROUP BY Section. 字段 2,Section.字段 3;*;（ 3）查詢結(jié)果：.4、 Print the course names, course numbers and section numbers of all classes with less than six students enrolled

6、 inthem.解：（ 1）分析：本題是查詢所有班級人數(shù)少于6 的課程名，課程號，分班號。通過section 表可以查詢出所有的班，其中的課程名可由查詢所得的dname, cno 在 course 表中確定，因為與班級人數(shù)有關(guān)，還需將 section 表和 enroll 表做連接，并按 o, section.dname, section.sectno 分組以獲取所有班的人數(shù)。所以可用連接查詢、嵌套查詢，并適當分組來實現(xiàn)查詢要求。（ 2）語句實現(xiàn)：SELECT (SELECT Course. 字段 2 from Coursewhere Course.字段 1=Section. 字段 2 and

7、Course.字段 3=Section.字段 1)AS Course 字段 2,Section.字段 2,Section.字段 3FROM Section,EnrollWHERE (Section. 字段 2=Enroll. 字段 4AND section. 字段 1=enroll. 字段 3 AND section. 字段 3=enroll. 字段 5)GROUP BY Section. 字段 2,Section.字段 3,Section.字段 1HAVING count(*)<6;*;（ 3）查詢結(jié)果：5、 Print the name(s) and sid(s) of studen

8、t(s) enrolled in the most classes.解：（1）分析：本題是查詢加入班級數(shù)最多的學生名字、學號。可采用臨時表的方法解決。建立一個臨時表包括字段學生名字、學號和所選課程的數(shù)目（num）。再對這個臨時表進行查詢（名字、學號），限制條件是所選課程數(shù)目等于max(num)。（ 2）語句實現(xiàn)： SELECT Temp.sid, Temp.snameFROM (SELECT enroll.sid,student.sname,count(enroll.sid) AS num FROM enroll,studentWHERE enroll.sid=student.sidGROUP

9、 BY enroll.sid,student.sname) AS TempWHERE Temp.num = (SELECT max(num) From (SELECT sid,(SELECT sname FROM Student where sid=Enroll.sid)AS sname,count(*)As num FROM Enroll group by Enroll.sid);.*(3) 查詢結(jié)果：6、 Print the names of departments that have one or more majors who are under 18 years old.解：（ 1）

10、分析：本題是查詢所含學生至少有一個年齡小于18 歲的系的名稱。在major表中可以得到每個學生所屬專業(yè)情況，該系存在學生年齡小于18 歲的學生 （通過 student 表）則該系滿足要求。因此可用帶有exists 的嵌套子查詢實現(xiàn)要求。（ 2）語句實現(xiàn)： SELECT dname FROM majorWHERE exists(SELECT*FROM Student where Student.age<18 and Student.sid=major.sid); *;（ 3）查詢結(jié)果：7、 Print the names and majors of students who are tak

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

12、se,Enroll where Enroll.dname='Mathematics' and Eo in (461,462) and Enroll.sid=Major.sid);*;（ 3）查詢結(jié)果：.8、 For those departments that have no majors taking a College Geometry course, print the department name and the number of PhD students in the department.解：（ 1）分析：本題是查詢所含學生都沒有選 College Geomet

13、ry course 的系的名稱和該系的博士生人數(shù)。本題思路和方法與上題基本一致。（ 2）語句實現(xiàn)： SELECT dname, numphdsFROM DeptWHERE not exists(SELECT*from Course,Enroll where Enroll.dname='Mathematics' and Eo in (461,462) and Enroll.sid=Major.sid);*;（ 3）查詢結(jié)果：.9、 Print the names of students who are taking both a Computer Sciences course

14、and a Mathematics course.解：（ 1）分析： 本題是查詢既選了計算機科學系課程又選了數(shù)學系課程的學生姓名是求兩個集合交的問題。因為一般的DBMS 沒有實現(xiàn)交操作， 可以先根據(jù)一個條件獲取一個集合然后根據(jù)第二個條件篩選，即第一個集合要選取也在第二個條件產(chǎn)生的集合元素中。因此可以用帶有in 的嵌套子查詢完成要求。（ 2）語句實現(xiàn)： SELECT Student.snameFROM Student,EnrollWHERE Student.sid=Enroll.sid and Enroll.dname='Mathematics' and student.snam

15、e in (SELECT Student.sname FROM Student,EnrollWHERE Student.sid=Enroll.sid and Enroll.dname='Computer Sciences');*;（ 3）查詢結(jié)果：10、 Print the age difference between the oldest and youngest Computer Sciences major(s).解：（1）分析：本題是查詢計算機科學性年齡最大的學生和年齡最小的學生之間的年齡差。思想還是通過先建立一張臨時表， 包括字段系名、 學號、年齡。再在這張臨時表里利

16、用max(age)， min(age)獲取最大年齡和最小年齡，進行相減運算。（ 2）語句實現(xiàn)： SELECT max(age)-min(age) AS difference FROM (SELECT dname,Mahor.sid, age FROM Major,StudentWHERE Major.dname='Computer Sciences' and Major.sid=Student.sid);*;（ 3）查詢結(jié)果：.11、 For each department that has one or more majors with a GPA under 1.0, pr

17、int the name of the department and theaverage GPA of its majors.解：（ 1）分析：本題是查詢系里有學生績點小于1.0 的系的系名和該系學生的平均績點。本題主要是對major ，student 兩張表按sid 做連接，連接后再按dname 分組，但要注意將min(gpa)<1 的分組剔除。（ 2）語句實現(xiàn)： SELECT dname,avg(gpa) AS avgpa FROM Major,StudentWHERE dname in (SELECT dname from Major,Student WHERE Major.si

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