数据库实习答案

1、.数据库实习答案1、E-R图向关系模式的转化。最终答案： 职工(职工号，姓名，性别，工资，职称，简历,部门号)； 部门(部门号，名称，地址,领导人职工号)； 工程(工程号，工程名，参加人数，预算,部门号,负责人职工号)； 办公室(编号，地点，电话,部门号)； 参加(职工号，工程号，日期，具体职务)； (红色部分为主键,绿色部分为外键)。2、SQL查询语句练习。/* * 实验3 数据库的简单查询实验答案*/【例3.1】查询全体学生的学号与姓名 SELECT Sno,Sname FROM Students 【例3.2】查询全体学生的学号、姓名、性别、年龄、所在系。 SELECT Sno,Sname

2、,Sname Ssex,Sage,Sdept FROM Students【例3.3】查询全体学生的选课情况，即学号、课程号、成绩，成绩值都加5SELECT Sno,Cno,Grade+5 FROM Enrollment【例3.4】查询全体学生的学号、姓名，并为原来的英文列名设置中文列别名。以下三种方法等价。SELECT Sno 学号 , Sname 姓名 FROM StudentsSELECT Sno AS 学号 ,Sname AS 姓名 FROM StudentsSELECT 学号=Sno , 姓名=Sname FROM Students【例3.5】查询全体学生的选课情况，其成绩列值都加5，

3、并为各列设置中文的别名 SELECT Sno 学号,Cno 课程号,Grade+5 成绩 FROM Enrollment【例3.6】显示所有选课学生的学号。SELECT Sno 学号 FROM Enrollment等价于：SELECT ALL Sno 学号 FROM Enrollment【例3.7】显示所有选课学生的学号，并去掉重复行。SELECT DISTINCT Sno 学号 FROM Enrollment【例3.8】查询学生选课成绩大于80分的学生学号、课程号、成绩。SELECT * FROM Enrollment WHERE Grade80【例3.9】查询数学系全体学生的学号、姓名。S

4、ELECT Sno ,Sname FROM Students WHERE Sdept=Math【例3.10】查询学生选课成绩在8090分之间的学生学号、课程号、成绩。SELECT * FROM Enrollment WHERE Grade BETWEEN 80 AND 90等价于：SELECT * FROM Enrollment WHERE Grade = 80 AND Grade =90【例3.11】查询学生年龄不在2030之间的学生学号、姓名、所在系。SELECT Sno ,Sname,Sdept FROM Students WHERE Sage NOT BETWEEN 20 AND 30

5、等价于：SELECT Sno ,Sname,Sdept FROM Students WHERE Sage30【例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.13】查询既不是数学系、计算机系，也不是艺术系学生的学号、姓名。SELECT Sno,Sname FROM Stud

6、ents WHERE Sdept NOT IN (Math, Computer, Art)等价于：SELECT Sno,Sname FROM Students WHERE Sdept! =Math AND Sdept! = Computer AND Sdept! = Art【例3.14】查找姓名的第二个字符是u并且只有三个字符的学生的学号、姓名。SELECT Sno,Sname FROM Students WHERE Sname LIKE _u_【例3.15】查找姓名以S开头的所有学生的学号、姓名。SELECT Sno,Sname FROM Students WHERE Sname LIKE

7、S%【例3.16】查找姓名以S、D或J开头的所有学生的学号、姓名。SELECT Sno,Sname FROM Students WHERE Sname LIKE SDJ%【例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.18】查询无考试成绩的学生的学号和相应的课程号。SELECT Sno, Cno FROM Enrollment WHERE Grad

8、e IS NULL不等价于：SELECT Sno, Cno FROM Enrollment WHERE Grade =0 【例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 Sage18【例3.21】求选修了C1课程或C2课程的学生学号、成绩。SELECT Sno,Grade FROM Enrol

9、lment WHERE Cno=C1 OR Cno=C2为了使查询语句的可读性更好， 可以将WHERE后面的整个条件用括号括起来。等价于:SELECT Sno,Grade FROM Enrollment WHERE (Cno=C1 OR Cno=C2)【例3.22】求学生的总人数SELECT COUNT(*) FROM Students 【例3.23】求选修了课程的学生人数SELECT COUNT(DISTINCT Sno) FROM Enrollment【例3.24】求选修了C1课程的学生的平均成绩。SELECT AVG(Grade) FROM Enrollment WHERE Cno=C1

10、【例3.25】求20010102号学生的考试总成绩之和。SELECT SUM(Grade) FROM Enrollment WHERE Sno = 20010102【例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.28】求每个学生的学号和各门课程的总成绩。SELECT Sno 学号, Sum(gr

11、ade) 总成绩 FROM Enrollment GROUP BY Sno【例3.29】求选修课程超过2门课的学生的学号、平均成绩，选修的门数。SELECT Sno, AVG(Grade) AS 平均成绩, COUNT(*) AS 选修门数FROM EnrollmentGROUP BY Sno HAVING COUNT(*) = 2【例3.30】查询所有学生的行，并按学生的年龄值从小到大排序。SELECT * FROM Students ORDER BY Sage【例3.31】查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排列。 SELECT Sno, Grade FROM Enro

12、llment WHERE Cno=C1 ORDER BY Grade DESC【例3.32】查询全体学生信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。 SELECT * FROM Students ORDER BY Sdept, Sage DESC【例3.33】求选修课程超过2门课的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。SELECT Sno AS 学号, AVG(Grade) AS 平均成绩, COUNT(*)AS 修课门数FROM EnrollmentGROUP BY Sno HAVING COUNT(*) = 2 ORDER BY AVG (Grade)

13、 DESC/* * 实验4 数据库的多表连接查询实验答案*/【例4.1】查询每个学生的基本信息以及他/她选课的情况。SELECT Students.*,Enrollment.* FROM Students,EnrollmentWHERE Students.Sno = Enrollment.Sno 上述是等值连接,改为自然连接，表示如下：SELECT Students.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Students,EnrollmentWHERE Students.Sno = Enrollment.Sno【例4.2】查询每个学生的学号、姓名、选修

14、的课程名、成绩。SELECT Students.Sno,Sname, Cname,GradeFROM Students,Courses,EnrollmentWHERE Students.Sno = Enrollment.Sno AND Courses.Cno= Enrollment.Cno【例4.3】查询选修了C2且成绩大于90分的学生的学号、姓名、成绩。SELECT Students.Sno,Sname, GradeFROM Students, EnrollmentWHERE Students.Sno = Enrollment.Sno AND Cno=C2 AND Grade90【例4.4】

15、求计算机系选修课程超过2门课的学生的学号、姓名、平均成绩, 并按平均成绩从高到低排序。SELECT Students.Sno, Sname, AVG(Grade) AverageFROM Students, EnrollmentWHERE Students.Sno= Enrollment.Sno AND Sdept=ComputerGROUP BY Students.Sno,Sname HAVING COUNT(*) = 2ORDER BY SUM(Grade) DESC【例4.5】查询与Sue在同一个系学习的所有学生的学号和姓名。SELECT S2.Sno, S2.SnameFROM Stu

16、dents S1,Students S2 where S1.Sdept = S2.Sdept AND S1.Sname = Sue【例4.6】查询所有学生的选修情况，要求包括选修了课程的学生和没有修课的学生，显示他们的学号、姓名、课程号、成绩。SELECT Students.Sno, Sname,Cno,Grade FROM Students,EnrollmentWHERE Students.Sno*=Enrollment.Sno上述左外连接可可以用右外连接等价表示：SELECT Students.Sno, Sname,Cno,Grade FROM Students,EnrollmentWHE

17、RE 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 数据库的子查询、组合查询实验答案

18、*/【例5.1】查询与Sue在同一个系学习的学生学号、姓名。SELECT Sno, Sname FROM StudentsWHERE Sdept= (SELECT Sdept FROM Students WHERE Sname = Sue)【例5.2】查询其他系中比计算机系任一学生年龄都小的学生基本情况。SELECT * FROM StudentsWHERE Sdept!=Computer AND Sage ALL (SELECT Sage FROM Students WHERE Sdept=Computer)【例5.3】查询其他系中比计算机系某一学生年龄小的学生的基本情况。 SELECT *

19、 FROM StudentsWHERE Sdept!=Computer AND Sage 80 )【例5.5】查询选修了课程名为English的课程并且成绩大于80 分的学生学号、姓名。SELECT Sno, Sname FROM StudentsWHERE Sno IN(SELECT Sno FROM Enrollment WHERE Grade 80 AND Cno=(SELECT Cno FROM Courses WHERE Cname= English)【例5.6】查询选修了C2课程的学生的学号和姓名。SELECT Sno, Sname FROM StudentsWHERE EXIST

20、S (SELECT * FROM EnrollmentWHERE Sno =Students.Sno AND Cno =C2)【例5.7】查询没有选修C2课程的学生的学号、姓名。SELECT Sno, Sname FROM StudentsWHERE NOT EXISTS (SELECT * FROM EnrollmentWHERE Sno =Students.Sno AND Cno =C2)【例5.8】查询选修了C1课程或选修了C2课程的学生学号。SELECT Sno FROM Enrollment WHERE Cno =C1UNIONSELECT Sno FROM Enrollment W

21、HERE Cno =C2【例5.9】查询选修了C1课程并且也选修了C2课程的学生学号。SELECT Sno FROM Enrollment WHERE Cno =C1 SQLSERVER2000中不能运行INTERSECTSELECT Sno FROM Enrollment WHERE Cno =C2上述查询在SQL SERVER 2000中应表示为：SELECT Sno FROM Enrollment E1WHERE Cno =C1 AND EXISTS(SELECT Sno FROM Enrollment E2WHERE E1.Sno=E2.Sno AND E2.Cno =C2)【例5.1

22、0】查询选修了C1课程但没有选修了C2课程的学生学号。SELECT Sno FROM Enrollment WHERE Cno =C1 SQLSERVER2000中不能运行MINUSSELECT Sno FROM Enrollment WHERE Cno =C2上述查询在SQLSERVER2000中应表示为：SELECT Sno FROM Enrollment E1WHERE Cno =C1 AND NOT EXISTS (SELECT Sno FROM Enrollment E2WHERE E1.Sno=E2.Sno AND E2.Cno =C2)/* * 实验6 数据库的更新实验答案*/【

23、例6.1】向Students表中添加一个学生记录，学生学号为20010105,姓名为Stefen,性别为男，年龄25岁，所在系为艺术系Art。INSERT INTO Students VALUES (20010105, Stefen, F, 25, Art)【例6.2】向Enrollment表中添加一个学生的选课记录，学生学号为20010105,所选的课程号为C2。 INSERT INTO Enrollment (Sno,Cno) VALUES (20010105, C2)【例6.3】假定当前数据库中有一个临时表Temp，见表。把它的所有行一次性地加到Students表中。要求先定义Temp表

24、，输入若干行数据。具体内容表3-7INSERT INTO Students SELECT * FROM Temp【例6.4】将所有学生选课的成绩加5。UPDATE Enrollment SET Grade = Grade + 5【例6.5将姓名为Sue的学生所在系改为计算机系。UPDATE Students SET Sdept = Computer WHERE Sname = Sue【例6.6】将选课了课程名为Database课程的学生成绩加10。UPDATE Enrollment SET Grade = Grade + 10WHERE Cno = (SELECT Cno FROM Courses WHERE Cname = Database)【例6.7】删除所有成绩为空值的选修记录。DELETE FROM Enrollment WHERE Grade IS NULL【例6.8】删除学生姓名为Deepa的学生记录。 DEL