数据库实习答案
1、E-R图向关系模式的转化。
最终答案：
职工(职工号，姓名，性别，工资，职称，简历,部门号)；
部门(部门号，名称，地址,领导人职工号)；
工程(工程号，工程名，参加人数，预算,部门号,负责人职工号)；
办公室(编号，地点，电话,部门号)；
参加(职工号，工程号，日期，具体职务)；
(红色部分为主键,绿色部分为外键)。
2、SQL查询语句练习。
/******************************************************
* 实验3 数据库的简单查询实验答案
******************************************************/
【例】查询全体学生的学号与姓名
SELECT Sno,Sname FROM Students
【例】查询全体学生的学号、姓名、性别、年龄、所在系。
SELECT Sno,Sname,Sname Ssex,Sage,Sdept FROM Students
【例】查询全体学生的选课情况，即学号、课程号、成绩，成绩值都加5
SELECT Sno,Cno,Grade+5 FROM Enrollment
【例】查询全体学生的学号、姓名，并为原来的英文列名设置中文列别名。以下三种方法等价。
SELECT Sno '学号' , Sname '姓名' FROM Students
SELECT Sno AS '学号' ,Sname AS '姓名' FROM Students
SELECT '学号'=Sno , '姓名'=Sname FROM Students
【例】查询全体学生的选课情况，其成绩列值都加5，并为各列设置中文的别名
SELECT Sno '学号',Cno '课程号',Grade+5 '成绩' FROM Enrollment
【例】显示所有选课学生的学号。
SELECT Sno '学号' FROM Enrollment
等价于：
SELECT ALL Sno '学号' FROM Enrollment
【例】显示所有选课学生的学号，并去掉重复行。
SELECT DISTINCT Sno '学号' FROM Enrollment
【例】查询学生选课成绩大于80分的学生学号、课程号、成绩。
SELECT * FROM Enrollment WHERE Grade>80
【例】查询数学系全体学生的学号、姓名。
SELECT Sno ,Sname FROM Students WHERE Sdept='Math'
【例】查询学生选课成绩在80～90分之间的学生学号、课程号、成绩。
SELECT * FROM Enrollment WHERE Grade BETWEEN 80 AND 90
等价于：
SELECT * FROM Enrollment WHERE Grade >= 80 AND Grade <=90
【例】查询学生年龄不在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
【例】查询数学系、计算机系、艺术系学生的学号、姓名。
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'
【例】查询既不是数学系、计算机系，也不是艺术系学生的学号、姓名。
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'
【例】查找姓名的第二个字符是u并且只有三个字符的学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '_u_'
【例】查找姓名以S开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE 'S%'
【例】查找姓名以S、D或J开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '[SDJ]%'
【例】查找姓名不是以S、D或J开头的所有学生的学号、姓名。
SELECT Sno,Sname FROM Students WHERE Sname LIKE '[^SDJ]%'
等价于：
SELECT Sno,Sname FROM Students WHERE Sname NOT LIKE '[SDJ]%'
【例】查询无考试成绩的学生的学号和相应的课程号。
SELECT Sno, Cno FROM Enrollment WHERE Grade IS NULL
不等价于：SELECT Sno, Cno FROM Enrollment WHERE Grade =0
【例】查询有考试成绩(即成绩不为空值)的学生的学号、课程号。
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
【例】求选修了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')
【例】求学生的总人数
SELECT COUNT(*) FROM Students
【例】求选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM Enrollment
【例】求选修了C1课程的学生的平均成绩。
SELECT AVG(Grade) FROM Enrollment WHERE Cno='C1'
【例】求号学生的考试总成绩之和。
SELECT SUM(Grade) FROM Enrollment WHERE Sno = ''
【例】选修了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
【例】求每个学生的学号和各门课程的总成绩。
SELECT Sno '学号', Sum(grade) '总成绩' FROM Enrollment GROUP BY Sno
【例】求选修课程超过2门课的学生的学号、平均成绩，选修的门数。
SELECT Sno, AVG(Grade) AS '平均成绩', COUNT(*) AS ' 选修门数'
FROM Enrollment
GROUP BY Sno
HAVING COUNT(*) >= 2
【例】查询所有学生的行，并按学生的年龄值从小到大排序。
SELECT * FROM Students ORDER BY Sage
【例】查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排列。
SELECT Sno, Grade FROM Enrollment WHERE Cno='C1' ORDER BY Grade DESC
【例】查询全体学生信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
SELECT * FROM Students ORDER BY Sdept, Sage DESC
【例】求选修课程超过2门课的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。
SELECT Sno AS '学号', AVG(Grade) AS '平均成绩', COUNT(*)AS '修课门数'
FROM Enrollment
GROUP BY Sno
HAVING COUNT(*) >= 2
ORDER BY AVG (Grade) DESC
/******************************************************
* 实验4 数据库的多表连接查询实验答案
******************************************************/
【例】查询每个学生的基本信息以及他/她选课的情况。
SELECT Students.*,Enrollment.*
FROM Students,Enrollment
WHERE =
上述是等值连接,改为自然连接，表示如下：
SELECT ,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Students,Enrollment
WHERE =
【例】查询每个学生的学号、姓名、选修的课程名、成绩。
SELECT ,Sname, Cname,Grade
FROM Students,Courses,Enrollment
WHERE = AND =
【例】查询选修了C2且成绩大于90分的学生的学号、姓名、成绩。
SELECT ,Sname, Grade
FROM Students, Enrollment
WHERE = AND Cno='C2 ' AND Grade>90
【例】求计算机系选修课程超过2门课的学生的学号、姓名、平均成绩, 并按平均成绩从高到低排序。
SELECT , Sname, AVG(Grade) 'Average'
FROM Students, Enrollment
WHERE = AND Sdept='Computer'
GROUP BY ,Sname
HAVING COUNT(*) >= 2
ORDER BY SUM(Grade) DESC
【例】查询与Sue在同一个系学习的所有学生的学号和姓名。
SELECT ,
FROM Students S1,Students S2
where = AND = 'Sue'
【例】查询所有学生的选修情况，要求包括选修了课程的学生和没有修课的学生，显示他们的学号、姓名、课程号、成绩。
SELECT , Sname,Cno,Grade
FROM Students,Enrollment
WHERE *=
上述左外连接可可以用右外连接等价表示：
SELECT , Sname,Cno,Grade
FROM Students,Enrollment
WHERE =*
在SQLSERVER2000中，以上左外连接还可等价表示为：
SELECT , Sname,Cno,Grade
FROM Students LEFT JOIN Enrollment ON =
-或者表示为：
SELECT , Sname,Cno,Grade
FROM Enrollment RIGHT JOIN Students ON =
/******************************************************
* 实验5 数据库的子查询、组合查询实验答案
******************************************************/
【例】查询与Sue在同一个系学习的学生学号、姓名。
SELECT Sno, Sname FROM Students
WHERE Sdept= (SELECT Sdept FROM Students WHERE Sname = 'Sue')
【例】查询其他系中比计算机系任一学生年龄都小的学生基本情况。
SELECT * FROM Students
WHERE Sdept!='Computer' AND Sage WHERE Sdept='Computer') 【例】查询其他系中比计算机系某一学生年龄小的学生的基本情况。 SELECT * FROM Students WHERE Sdept!='Computer' AND Sage WHERE Sdept='Computer') 【例】查询成绩大于80分的学生的学号、姓名。 SELECT Sno, Sname FROM Students WHERE Sno=ANY ( SELECT Sno FROM Enrollment WHERE Grade >80 ) 【例】查询选修了课程名为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') ) 【例】查询选修了C2课程的学生的学号和姓名。 SELECT Sno, Sname FROM Students WHERE EXISTS (SELECT * FROM Enrollment WHERE Sno = AND Cno ='C2') 【例】查询没有选修C2课程的学生的学号、姓名。 SELECT Sno, Sname FROM Students WHERE NOT EXISTS (SELECT * FROM Enrollment WHERE Sno = AND Cno ='C2') 【例】查询选修了C1课程或选修了C2课程的学生学号。 SELECT Sno FROM Enrollment WHERE Cno ='C1' UNION SELECT Sno FROM Enrollment WHERE Cno ='C2' 【例】查询选修了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 = AND ='C2') 【例】查询选修了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 = AND ='C2') /****************************************************** * 实验6 数据库的更新实验答案 ******************************************************/ 【例】向Students表中添加一个学生记录，学生学号为,姓名为Stefen,性别为男，年龄25岁，所在系为艺术系Art。 INSERT INTO Students VALUES ('', 'Stefen', 'F', 25, 'Art') 【例】向Enrollment表中添加一个学生的选课记录，学生学号为,所选的课程号为C2。 INSERT INTO Enrollment (Sno,Cno) VALUES ('', 'C2') 【例】假定当前数据库中有一个临时表Temp，见表。把它的所有行一次性地加到Students表中。 要求先定义Temp表，输入若干行数据。具体内容表3-7 INSERT INTO Students SELECT * FROM Temp 【例】将所有学生选课的成绩加5。 UPDATE Enrollment SET Grade = Grade + 5 【例将姓名为Sue的学生所在系改为计算机系。 UPDATE Students SET Sdept = 'Computer' WHERE Sname = 'Sue' 【例】将选课了课程名为Database课程的学生成绩加10。 UPDATE Enrollment SET Grade = Grade + 10 WHERE Cno = (SELECT Cno FROM Courses WHERE Cname = 'Database') 【例】删除所有成绩为空值的选修记录。 DELETE FROM Enrollment WHERE Grade IS NULL 【例】删除学生姓名为Deepa的学生记录。 DELETE FROM Students WHERE Sname = 'Deepa' 【例】删除计算机系选修成绩不及格的学生选修记录。 DELETE FROM Enrollment WHERE Grade < 60 AND Sno IN (SELECT Sno FROM Students WHERE Sdept = 'Computer' ) GO /****************************************************** * 实验7 数据库的视图定义及使用实验答案 ******************************************************/ 【例】建立数学系学生的视图。 CREATE VIEW MathStudentView AS SELECT Sno, Sname, Sage FROM Students WHERE Sdept = 'Math' GO 【例】建立计算机系选修了课程名为Database的学生的视图，视图名为：CompStudentView，该视图的列名为学号、姓名、成绩。 CREATE VIEW CompStudentView (学号,姓名,成绩) AS SELECT , Sname, Grade FROM Students,Courses,Enrollment WHERE = AND = AND Sdept= 'Computer' AND Cname = 'Database' GO 【例】创建一个名为StudentsSumView，包含所有学生学号和总成绩的视图。 CREATE VIEW StudentsSumView(学号,总成绩) AS SELECT Sno,SUM (Grade) FROM Enrollment GROUP BY Sno GO 【例】建立计算机系学生选修了课程名为Database并且成绩大于80分的学生视图，视图名为：CompStudentView1。该视图的列名为学号、姓名、成绩。 CREATE VIEW CompStudentView1 AS SELECT * FROM CompStudentView WHERE 成绩>80 GO 【例】查询计算机系选修了课程名为Database并且成绩大于90分的学生的姓名、成绩。 SELECT 姓名,成绩 FROM CompStudentView1 WHERE 成绩>90 【例】删除CompStudentView1视图。 DROP VIEW CompStudentView1 3、数据库安全与保护内容 因篇幅问题不能全部显示，请点此查看更多更全内容