《数据库原理及应用案例教程》实验3到实验7答案
/************************数据库原理及应用案例教程****************
* 实验3 数据库的简单查询实验答案
******************************************************/ 【例3.3.1】查询全体学生的学号与姓名
SELECT Sno,Sname FROM Students
【例3.3.2】查询全体学生的学号、姓名、性别、年龄、所在系。
SELECT Sno,Sname,Sname Ssex,Sage,Sdept FROM Students
【例3.3.3】查询全体学生的选课情况，即学号、课程号、成绩，成绩值都加5
SELECT Sno,Cno,Grade+5 FROM Enrollment
【例3.3.4】查询全体学生的学号、姓名，并为原来的英文列名设置中文列别名。以下三种
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
等价。
SELECT Sno '学号' , Sname '姓名' FROM Students
SELECT Sno AS '学号' ,Sname AS '姓名' FROM Students
SELECT '学号'=Sno , '姓名'=Sname FROM Students
【例3.3.5】查询全体学生的选课情况，其成绩列值都加5，并为各列设置中文的别名
SELECT Sno '学号',Cno '课程号',Grade+5 '成绩' FROM Enrollment
【例3.3.6】显示所有选课学生的学号。
SELECT Sno '学号' FROM Enrollment
等价于:
SELECT ALL Sno '学号' FROM Enrollment
【例3.3.7】显示所有选课学生的学号，并去掉重复行。
SELECT DISTINCT Sno '学号' FROM Enrollment
【例3.3.8】查询学生选课成绩大于80分的学生学号、课程号、成绩。
SELECT * FROM Enrollment WHERE Grade>80
【例3.3.9】查询数学系全体学生的学号、姓名。
SELECT Sno ,Sname FROM Students WHERE Sdept='Math'
【例3.3.10】查询学生选课成绩在80,90分之间的学生学号、课程号、成绩。
SELECT * FROM Enrollment WHERE Grade BETWEEN 80 AND 90 等价于:
SELECT * FROM Enrollment WHERE Grade >= 80 AND Grade <=90
【例3.3.11】查询学生年龄不在20,30之间的学生学号、姓名、所在系。
SELECT Sno ,Sname,Sdept FROM Students WHERE Sage NOT BETWEEN 20 AND 30
等价于:
SELECT Sno ,Sname,Sdept FROM Students WHERE Sage<20 OR Sage>30 【例3.3.12】查询数学系、计算机系、艺术系学生的学号、姓名。
SELECT Sno,Sname FROM Students
WHERE Sdept IN ('Math', 'Computer', 'Art')
等价于:
SELECT Sno,Sname FROM Students
WHERE Sdept ='Math' OR Sdept = 'Computer' OR Sdept = 'Art'
【例3.3.13】查询既不是数学系、计算机系，也不是艺术系学生的学号、姓名。
SELECT Sno,Sname FROM Students
WHERE Sdept NOT IN ('Math', 'Computer', 'Art')
等价于:
SELECT Sno,Sname FROM Students
WHERE Sdept! ='Math' AND Sdept! = 'Computer' AND Sdept! = 'Art'
【例3.3.14】查找姓名的第二个字符是u并且只有三个字符的学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '_u_'
【例3.3.15】查找姓名以S开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE 'S%'
【例3.3.16】查找姓名以S、D或J开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '[SDJ]%'
【例3.3.17】查找姓名不是以S、D或J开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '[^SDJ]%' 等价于:SELECT Sno,Sname FROM Students WHERE Sname NOT LIKE '[SDJ]%'
【例3.3.18】查询无考试成绩的学生的学号和相应的课程号。
SELECT Sno, Cno FROM Enrollment WHERE Grade IS NULL 不等价于:SELECT Sno, Cno FROM Enrollment WHERE Grade =0
【例3.3.19】查询有考试成绩(即成绩不为空值)的学生的学号、课程号。
SELECT Sno, Cno FROM Enrollment WHERE Grade IS NOT NULL
【例3.3.20】查询计算机系年龄在18岁以上的学生学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sdept='Computer' AND Sage>18
【例3.3.21】求选修了C1课程或C2课程的学生学号、成绩。
SELECT Sno,Grade FROM Enrollment WHERE Cno='C1' OR Cno='C2' 为了使查询语句的可读性更好， 可以将WHERE后面的整个条件用括号括起来。
等价于: SELECT Sno,Grade FROM Enrollment WHERE (Cno='C1' OR Cno='C2')
【例3.3.22】求学生的总人数
SELECT COUNT(*) FROM Students
【例3.3.23】求选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM Enrollment
【例3.3.24】求选修了C1课程的学生的平均成绩。
SELECT AVG(Grade) FROM Enrollment WHERE Cno='C1'
【例3.3.25】求20010102号学生的考试总成绩之和。
SELECT SUM(Grade) FROM Enrollment WHERE Sno = '20010102'
【例3.3.26】选修了C1课程的学生的最高分和最低分
SELECT MAX(Grade) , MIN(Grade) FROM Enrollment WHERE Cno='C1'
【例3.3.27】求选修每门课程的学生人数。
SELECT Cno AS '课程号', COUNT(Sno) AS '选修人数' FROM Enrollment GROUP BY Cno
【例3.3.28】求每个学生的学号和各门课程的总成绩。
SELECT Sno '学号', Sum(grade) '总成绩' FROM Enrollment GROUP BY Sno
【例3.3.29】求选修课程超过2门课的学生的学号、平均成绩，选修的门数。
SELECT Sno, AVG(Grade) AS '平均成绩', COUNT(*) AS ' 选修门数'
FROM Enrollment GROUP BY Sno HAVING COUNT(*) >= 2
【例3.3.30】查询所有学生的行，并按学生的年龄值从小到大排序。
SELECT * FROM Students ORDER BY Sage
【例3.3.31】查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排列。
SELECT Sno, Grade FROM Enrollment WHERE Cno='C1' ORDER BY Grade DESC
【例3.3.32】查询全体学生信息，查询结果按所在系的系名升序排列，同一系的学生按年龄
降序排列。
SELECT * FROM Students ORDER BY Sdept, Sage DESC
【例3.3.33】求选修课程超过2门课的学生的学号、平均成绩和选课门数，并按平均成绩降
序排列。
SELECT Sno AS '学号', AVG(Grade) AS '平均成绩', COUNT(*)AS '修课门数'
FROM Enrollment
GROUP BY Sno
HAVING COUNT(*) >= 2
ORDER BY AVG (Grade) DESC
/******************************************************
* 实验4 数据库的多表连接查询实验答案
******************************************************/ 【例3.3.34】查询每个学生的基本信息以及他/她选课的情况。
SELECT Students.*,Enrollment.* FROM Students,Enrollment WHERE Students.Sno = Enrollment.Sno
上述是等值连接,改为自然连接，表示如下:
SELECT Students.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Students,Enrollment WHERE Students.Sno = Enrollment.Sno
【例3.3.35】查询每个学生的学号、姓名、选修的课程名、成绩。
SELECT Students.Sno,Sname, Cname,Grade
FROM Students,Courses,Enrollment
WHERE Students.Sno = Enrollment.Sno AND Courses.Cno= Enrollment.Cno
【例3.3.36】查询选修了C2且成绩大于90分的学生的学号、姓名、成绩。
SELECT Students.Sno,Sname, Grade FROM Students, Enrollment WHERE Students.Sno = Enrollment.Sno AND Cno='C2 ' AND Grade>90
【例3.3.37】求计算机系选修课程超过2门课的学生的学号、姓名、平均成绩, 并按平均成
绩从高到低排序。
SELECT Students.Sno, Sname, AVG(Grade) 'Average' FROM Students, Enrollment
WHERE Students.Sno= Enrollment.Sno AND Sdept='Computer' GROUP BY Students.Sno,Sname HAVING COUNT(*) >= 2 ORDER BY SUM(Grade) DESC
【例3.3.38】查询与Sue在同一个系学习的所有学生的学号和姓名。
SELECT S2.Sno, S2.Sname
FROM Students S1,Students S2
where S1.Sdept = S2.Sdept AND S1.Sname = 'Sue'
【例3.3.39】查询所有学生的选修情况，要求包括选修了课程的学生和没有修课的学生，显
示他们的学号、姓名、课程号、成绩。
SELECT Students.Sno, Sname,Cno,Grade FROM Students,Enrollment WHERE Students.Sno*=Enrollment.Sno
上述左外连接可可以用右外连接等价表示:
SELECT Students.Sno, Sname,Cno,Grade FROM Students,Enrollment WHERE Enrollment.Sno =* Students.Sno
在SQLSERVER2000中，以上左外连接还可等价表示为:
SELECT Students.Sno, Sname,Cno,Grade
FROM Students LEFT JOIN Enrollment ON Students.Sno=Enrollment.Sno -或者表示为:
SELECT Students.Sno, Sname,Cno,Grade
FROM Enrollment RIGHT JOIN Students ON Enrollment.Sno=Students.Sno
/******************************************************
* 实验5 数据库的子查询、组合查询实验答案
******************************************************/
【例3.3.40】查询与Sue在同一个系学习的学生学号、姓名。
SELECT Sno, Sname FROM Students
WHERE Sdept= (SELECT Sdept FROM Students WHERE Sname = 'Sue')
【例3.3.41】查询其他系中比计算机系任一学生年龄都小的学生基本情况。
SELECT * FROM Students
WHERE Sdept!='Computer' AND Sage
80 )
【例3.3.44】查询选修了课程名为English的课程并且成绩大于80 分的学生学号、姓名。 SELECT Sno, Sname FROM Students
WHERE Sno IN
(SELECT Sno FROM Enrollment
WHERE Grade > 80 AND Cno=(SELECT Cno FROM Courses
WHERE Cname= 'English') )
【例3.3.45】查询选修了C2课程的学生的学号和姓名。
SELECT Sno, Sname FROM Students
WHERE EXISTS (SELECT * FROM Enrollment WHERE Sno =Students.Sno AND Cno ='C2')
【例3.3.46】查询没有选修C2课程的学生的学号、姓名。
SELECT Sno, Sname FROM Students
WHERE NOT EXISTS (SELECT * FROM Enrollment WHERE Sno =Students.Sno AND Cno ='C2')
【例3.3.47】查询选修了C1课程或选修了C2课程的学生学号。
SELECT Sno FROM Enrollment WHERE Cno ='C1' UNION
SELECT Sno FROM Enrollment WHERE Cno ='C2'
【例3.3.48】查询选修了C1课程并且也选修了C2课程的学生学号。
SELECT Sno FROM Enrollment WHERE Cno ='C1' SQLSERVER2000中不能运行 INTERSECT
SELECT Sno FROM Enrollment WHERE Cno ='C2' 上述查询在SQL SERVER 2000中应表示为:
SELECT Sno FROM Enrollment E1
WHERE Cno ='C1' AND
EXISTS(SELECT Sno FROM Enrollment E2 WHERE E1.Sno=E2.Sno AND E2.Cno ='C2')
【例3.3.49】查询选修了C1课程但没有选修了C2课程的学生学号。
SELECT Sno FROM Enrollment WHERE Cno ='C1' SQLSERVER2000中不能运行 MINUS
SELECT Sno FROM Enrollment WHERE Cno ='C2' 上述查询在SQLSERVER2000中应表示为:
SELECT Sno FROM Enrollment E1
WHERE Cno ='C1' AND
NOT EXISTS (SELECT Sno FROM Enrollment E2 WHERE E1.Sno=E2.Sno AND E2.Cno ='C2')
/******************************************************
* 实验6 数据库的更新实验答案
******************************************************/ 【例3.4.1】向Students表中添加一个学生记录，学生学号为20010105,姓名为Stefen,性
别为男，年龄25岁，所在系为艺术系Art。
INSERT INTO Students VALUES ('20010105', 'Stefen', 'F', 25, 'Art')
【例3.4.2】向Enrollment表中添加一个学生的选课记录，学生学号为20010105,所选的课
程号为C2。
INSERT INTO Enrollment (Sno,Cno) VALUES ('20010105', 'C2')
【例3.4.3】假定当前数据库中有一个临时表Temp，见表。把它的所有行一次性地加到
Students表中。
要求先定义Temp表，输入若干行数据。具体内容表3-7
INSERT INTO Students SELECT * FROM Temp
【例3.4.4】将所有学生选课的成绩加5。
UPDATE Enrollment SET Grade = Grade + 5
【例3.4.5将姓名为Sue的学生所在系改为计算机系。
UPDATE Students SET Sdept = 'Computer' WHERE Sname = 'Sue' 【例3.4.6】将选课了课程名为Database课程的学生成绩加10。
UPDATE Enrollment SET Grade = Grade + 10
WHERE Cno = (SELECT Cno FROM Courses WHERE Cname = 'Database')
【例3.4.7】删除所有成绩为空值的选修记录。
DELETE FROM Enrollment WHERE Grade IS NULL
【例3.4.8】删除学生姓名为Deepa的学生记录。
DELETE FROM Students WHERE Sname = 'Deepa'
【例3.4.9】删除计算机系选修成绩不及格的学生选修记录。
DELETE FROM Enrollment
WHERE Grade < 60 AND Sno IN (SELECT Sno FROM Students WHERE Sdept = 'Computer' )
GO
/******************************************************
* 实验7 数据库的视图定义及使用实验答案
******************************************************/
【例3.5.1】建立数学系学生的视图。
CREATE VIEW MathStudentView
AS
SELECT Sno, Sname, Sage
FROM Students WHERE Sdept = 'Math' GO
【例3.5.2】建立计算机系选修了课程名为Database的学生的视图，视图名为:CompStudentView，该视图的列名为学号、姓名、成绩。
CREATE VIEW CompStudentView (学号,姓名,成绩)
AS
SELECT Students.Sno, Sname, Grade FROM Students,Courses,Enrollment WHERE Students.Sno = Enrollment.Sno AND Courses.Cno=Enrollment.Cno
AND Sdept= 'Computer' AND Cname = 'Database' GO
【例3.5.3】创建一个名为StudentsSumView，包含所有学生学号和总成绩的视图。 CREATE VIEW StudentsSumView(学号,总成绩)
AS
SELECT Sno,SUM (Grade)
FROM Enrollment
GROUP BY Sno
GO
【例3.5.4】建立计算机系学生选修了课程名为Database并且成绩大于80分的学生视图，视图名为:CompStudentView1。该视图的列名为学号、姓名、成绩。
CREATE VIEW CompStudentView1
AS
SELECT * FROM CompStudentView
WHERE 成绩>80
GO
【例3.5.5】查询计算机系选修了课程名为Database并且成绩大于90分的学生的姓名、成绩。
SELECT 姓名,成绩 FROM CompStudentView1 WHERE 成绩>90
【例3.5.6】删除CompStudentView1视图。
DROP VIEW CompStudentView1
浙公网安备 33021202002483