数据库系统教程(第二版何玉洁)课后数据库上机实验答案

淡淡的烟草味﹌ 2022-05-22 07:24 150阅读 0赞

## 附录C 上机实验 ##

## C.1 第4章上机实验 ##

下列实验均使用SQL Server 的SSMS工具实现。

1．用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：

l  数据库名为：学生数据库

l  主要数据文件的逻辑文件名为：Students\_data，存放在D:\\Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。

l  日志文件的逻辑文件名字为：Students\_log，也存放在D:\\Data文件夹下，初始大小为：2MB，增长方式为自动增长，每次增加10%。

2．选用已建立的“学生数据库”，写出创建满足表C-1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义内容）

表C-1 Student表结构

<table> 
 <tbody> 
 <tr> 
 <td> 列名 </td> 
 <td> 说明 </td> 
 <td> 数据类型 </td> 
 <td> 约束 </td> 
 </tr> 
 <tr> 
 <td> Sno </td> 
 <td> 学号 </td> 
 <td> 普通编码定长字符串，长度为7 </td> 
 <td> 主键 </td> 
 </tr> 
 <tr> 
 <td> Sname </td> 
 <td> 姓名 </td> 
 <td> 普通编码定长字符串，长度为10 </td> 
 <td> 非空 </td> 
 </tr> 
 <tr> 
 <td> Ssex </td> 
 <td> 性别 </td> 
 <td> 普通编码定长字符串，长度为2 </td> 
 <td> 取值范围：{男，女} </td> 
 </tr> 
 <tr> 
 <td> Sage </td> 
 <td> 年龄 </td> 
 <td> 微整型（tinyint） </td> 
 <td> 取值范围：15-45 </td> 
 </tr> 
 <tr> 
 <td> Sdept </td> 
 <td> 所在系 </td> 
 <td> 普通编码不定长字符串，长度为20 </td> 
 <td> 默认值为“计算机系” </td> 
 </tr> 
 <tr> 
 <td> Sid </td> 
 <td> 身份证号 </td> 
 <td> 普通编码定长字符串，长度为10 </td> 
 <td> 取值不重 </td> 
 </tr> 
 <tr> 
 <td> Sdate </td> 
 <td> 入学日期 </td> 
 <td> 日期 </td> 
 <td> 默认为系统当前日期 </td> 
 </tr> 
 </tbody> 
</table>

表C-2 Course表结构

<table> 
 <tbody> 
 <tr> 
 <td> 列名 </td> 
 <td> 说明 </td> 
 <td> 数据类型 </td> 
 <td> 约束 </td> 
 </tr> 
 <tr> 
 <td> Cno </td> 
 <td> 课程号 </td> 
 <td> 普通编码定长字符串，长度为10 </td> 
 <td> 主键 </td> 
 </tr> 
 <tr> 
 <td> Cname </td> 
 <td> 课程名 </td> 
 <td> 普通编码不定长字符串，长度为20 </td> 
 <td> 非空 </td> 
 </tr> 
 <tr> 
 <td> Credit </td> 
 <td> 学时数 </td> 
 <td> 整型 </td> 
 <td> 取值大于0 </td> 
 </tr> 
 <tr> 
 <td> Semester </td> 
 <td> 学分 </td> 
 <td> 小整型 </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

表C-3 SC表结构

表C-4 Teacher表结构

<table> 
 <tbody> 
 <tr> 
 <td> 列名 </td> 
 <td> 说明 </td> 
 <td> 数据类型 </td> 
 <td> 约束 </td> 
 </tr> 
 <tr> 
 <td> Tno </td> 
 <td> 教师号 </td> 
 <td> 普通编码定长字符串，长度为8 </td> 
 <td> 非空 </td> 
 </tr> 
 <tr> 
 <td> Tname </td> 
 <td> 教师名 </td> 
 <td> 普通编码定长字符串，长度为10 </td> 
 <td> 非空 </td> 
 </tr> 
 <tr> 
 <td> Salary </td> 
 <td> 工资 </td> 
 <td> 定点小数，小数点前4位，小数点后2位 </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

3. 写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。

（1） 在Teacher表中添加一个职称列，列名为：Title，类型为nchar(4)。

ALTER  TABLE Teacher ADD Title VARCHAR(4)

（2） 为Teacher表中的Title列增加取值范围约束，取值范围为：\{教授，副教授，讲师\}。

ALTER   TABLE Teacher ADD CONSTRAINT TitleCHECK(TitleIN(’教授’,’副教授’,’讲师’))

（3） 将Course表中Credit列的类型改为：tinyint。

注意：这里首先需要先删除Credit的约束,修改完数据类型后再重新添加约束项

ALTER  TABLE  Course DROP CONSTRAINT  CK\_\_Course\_\_Credit\_\_2B3F6F97

ALTER  TABLE  Course ALTER COLUMN  CreditTINYINT

ALTER  TABLE  Course ADD CONSTRAINT CreditCHECK(Credit>0)

（4） 删除Student表中的Sid和Sdate列。

注意：这里首先需要先删除约束

ALTER  TABLE  Student DROP CONSTRAINT  UQ\_\_Student\_\_CA1E5D79B3204FCF

ALTER  TABLE  Student DROP CONSTRAINT  DF\_\_Student\_\_Sdate\_\_3B75D760

ALTER  TABLE  Student DROP COLUMN Sdate,Sid

（5） 为Teacher表添加主键约束，其主键为：Tno。

ALTER  TABLE   Teacher ADD PRIMARY KEY(Tno)

※建立数据库主要文件和日志文件

https://img-blog.csdn.net/20161108131919403?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center

※我们可以看到D盘增加了主要数据文件.mdf和日志文件.ldf

https://img-blog.csdn.net/20161108132029576?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center

※给students\_data建立表格

<table> 
 <tbody> 
 <tr> 
 <td> createtableStudent( Sno char(7), Sname &nbsp;char(10)notnull, Ssex &nbsp;char(2) &nbsp;check(Ssex=’男’orSsex=’女’), Sage &nbsp;tinyint &nbsp;check(Sage&gt;14AND Sage&lt;46), Sdept &nbsp;varchar(20) &nbsp;default’计算机系’, Sid char(10) unique,–身份证号 Sdate &nbsp;date&nbsp;default getdate() primary &nbsp;key(Sno) ) </td> 
 </tr> 
 <tr> 
 <td> create &nbsp;table &nbsp;Course( Cno char(10) primary key, Cname &nbsp;varchar(20) &nbsp;notnull, Credit &nbsp;intcheck (Credit&gt;0), Semester &nbsp;tinyint, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> create &nbsp;tableSC( Sno char(7), Cno char(10), Grade &nbsp;tinyint &nbsp;check(Grade&gt;=0AND Grade&lt;=100) foreign &nbsp;key(Sno) &nbsp;references &nbsp;Student(Sno), foreign &nbsp;key(Cno) &nbsp;references &nbsp;Course(Cno) ) &nbsp; </td> 
 </tr> 
 <tr> 
 <td> create &nbsp;&nbsp;table &nbsp;Teacher( Tno char(8) not null, Tname &nbsp;char(10)notnull, Salary &nbsp;decimal(6,2) ) </td> 
 </tr> 
 </tbody> 
</table>

## C.2 第5章上机实验 ##

本实验均在SQL Server 的SSMS工具中实现。首先在已创建的“学生数据库”中创建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示数据，然后编写实现如下操作的SQL语句，执行所写的语句，并查看执行结果。

**有关建表及所需要的数据，见本章末尾**

1.  查询SC表中的全部数据。

SELECT \* FROM SC

2.  查询计算机系学生的姓名和年龄。

SELECT  Sname,Sage  FROM Student  WHERE Sdept=’计算机系’

3.  查询成绩在70～80分的学生的学号、课程号和成绩。

SELECT  Sno,Cno,Grade  FROM  SC WHERE Grade between 70 AND 80

4.  查询计算机系年龄在18～20岁的男生姓名和年龄。

SELECT  Sname,Sage FROM  Student  WHERE Sdept=’计算机系’AND  Sage BETWEEN 18 AND 20 AND Ssex=’男’

5.  查询C001课程的最高分。

SELECT  MAX(grade)  AS  最高成绩 FROMSC  WHERE  Cno=‘C001’

6.  查询计算机系学生的最大年龄和最小年龄。

SELECT  MAX(Sage)  AS  年长,MIN(Sage)    AS  年幼  FROM Student WHERE  Sdept=’计算机系’

7.  统计每个系的学生人数。

SELECT   Sdept,COUNT(Sno)   AS  人数 FROM Student   GROUP  BY  Sdept

8.  统计每门课程的选课人数和最高成绩。

SELECT  Cno,COUNT(Sno)  AS  人数,MAX(Grade)  AS  最高成绩  FROM SC GROUP  BY  Cno

9.  统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。

SELECT  Sno,COUNT(Cno)  AS  选课数量,SUM(Grade)  AS  考试总成绩  FROM SC GROUP  BY  Sno

10.  列出总成绩超过200的学生的学号和总成绩。

SELECT  Sno,SUM(Grade)  AS  考试总成绩 FROM SC  GROUPBY Sno HAVING SUM(Grade)>200

11.  查询选了C002课程的学生姓名和所在系。

SELECT  Sname,Sdept  FROM  Student st,SC sc WHERE st.Sno=sc.Sno  AND  Cno=‘C002’

** **或者

SELECT  Sname,Sdept  FROM  Student  WHERE Sno IN(SELECTSno  FROM SC  WHERE  Cno=‘C002’)

或者

SELECT  Sname,Sdept  FROM  Student WHERE EXISTS (SELECT  \*  FROM SC  WHERE  Sno= Student.Sno  AND  Cno = ‘C002’)

12.  查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。

SELECT  Sname,Cno,Grade  FROM  Student,SC  WHERE  Student.Sno=SC.Sno  AND Grade>80  ORDERBY Grade DESC

13.  查询与VB在同一学期开设的课程的课程名和开课学期。

SELECT  c2.Cname,c2.Semester  FROM  Course c1,Coursec2  WHERE  c1.Cname=c2.Cname  AND  c1.Cname=‘VB’  AND  c2.Cname!=‘VB’

14.  查询与李勇年龄相同的学生的姓名、所在系和年龄。

SELECT   s2.Sname,s2.Sdept,s2.Sage  FROM  Student s1,Students2  WHERE  s1.Sage=s2.Sage  AND  s1.Sname=’李勇’  AND  s2.Sname!=’李勇’

或者

SELECT  Sname,Sdept,Sage  FROM  Student WHERE Sage=(SELECT  Sage  FROM Student WHERE Sname=’李勇’) AND  Sname!=’李勇’

15.  查询哪些课程没有学生选修，列出课程号和课程名。

SELECT  Course.Cno,Course.Cname  FROM  Course LEFT JOIN SC  on SC.Cno=Course.Cno  WHERE  SC.Cno  IS  NULL

16.  查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。

SELECT  Student.Sno,Student.Sname,Cno  FROM  Student LEFT JOIN SC  ON SC.Sno=Student.Sno

17.  查询计算机系哪些学生没有选课，列出学生姓名。

SELECT  Sname  FROM Student WHERE Sdept=’计算机系’  AND  Sno NOT IN(SELECT  Sno  FROM SC)

或者

SELECT  Sname  FROM Student S LEFTJOIN  SC  ON S.Sno=SC.Sno

WHERE  Sdept= ‘计算机系’  AND SC.Cno   IS NULL

18.  查询计算机系年龄最大的三个学生的姓名和年龄。

SELECT  Top 3  Sname, Sage  FROM  Student  WHERE Sdept=’计算机系’  ORDER  BY Sage DESC

19.  列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。

SELECT  TOP 3  WITH TIES  Sname,Sdept,Grade   FROM StudentS JOIN  SC  on S.Sno= SC.Sno  JOIN  Course C ON C.Cno=SC.Cno  WHERE  Cname= ‘VB’  ORDER  BY Grade  DESC

20.  查询选课门数最多的前2位学生，列出学号和选课门数。√

SELECT  top 2Sno,COUNT(Cno)  AS  选课数 FROM SC  GROUPBY Sno ORDER BY  COUNT(Cno)  DESC

21.  查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。 √

SELECT  Sno,Sage,

CASE

WHEN  Sage<18  THEN  ’偏小’

WHEN  Sage>=18  AND Sage<=22  THEN  ’适合’

WHEN  Sage>22  THEN  ’偏大’

END

AS 年龄情况 FROM Student

22.  统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。 √

SELECT  Course.Cno,COUNT(Sno)  AS 选课人数,

CASE

WHEN  COUNT(Sno)>100   THEN  ’人多’

WHEN  COUNT(Sno)<40  AND  COUNT(Sno)>0  THEN  ’人少’

WHEN  COUNT(Sno)<=100  AND  COUNT(Sno)>=40  THEN  ’一般’

WHEN  COUNT(Sno)=0  THEN  ’无人选’

end

AS  人数情况  FROM  SC  RIGHT  JOIN  Course ON Course.Cno=SC.Cno  GROUP  BY Course.Cno

23.  查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB\_Grade中。√

v  局部临时表：\#新表名。局部于当前连接，生命期同连接期。

v  全局临时表： \#\#新表名。可在所有连接中使用，生命期同用户连接期

v  永久表： 新表名，存储在磁盘上

v  格式：SELECT选择列表   INTO新表名     FROM 子句 …

SELECT  Sname,Sdept,Grade  INTO  VB\_Grade FROM Student,SC,Course

WHERE  Student.Sno=SC.Sno  AND   Course.Cno=SC.Cno  AND   Cname=‘VB’

24.  统计每个系的女生人数，并将结果保存到新表Girls中。√

SELECT   Sdept,Ssex,COUNT(Ssex)  AS  人数 Into Girls  FROM   Student  WHERE Ssex=’女’  GROUP  BY   Sdept,Ssex

25.  用子查询实现如下查询：

（1）查询选了“C001”课程的学生姓名和所在系。√

SELECT   Sname,Sdept   FROM  Student   WHERE Sno IN  (SELECT  Sno   FROM SC WHERE Cno=‘C001’)

（2）查询通信工程系成绩80分以上的学生的学号和姓名。√

SELECT   Sno,Sname  FROM  Student   WHERE Sdept=’通信工程系’   AND  Sno IN(SELECT Sno FROM SC   WHERE   Grade>80)

（3）查询计算机系考试成绩最高的学生的姓名。√

SELECT   Sname FROM Student,SC  WHERE   SC.Sno=Student.Sno  AND Sdept=’计算机系’   AND   Gradein

(SELECTMAX(Grade)   FROM  SC,Student   WHERE   SC.Sno=Student.Sno  AND   Sdept=’计算机系’)

（4）查询年龄最大的男生的姓名、所在系和年龄。√

SELECT   Sname,Sdept,Sage  FROM  Student WHERE Ssex=’男’  AND  Sage in

(SELECT  MAX(Sage)  FROM  Student WHERE  Ssex=’男’)

26.  查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。

SELECT  Sno,Grade  FROM SC WHERE Cno= ‘C001’  AND Grade>( SELECT  AVG(Grade)  FROM  SC WHERE Cno =‘C001’)

27.  查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。

SELECT  Sname,Sdept,Cname,Grade  FROM   Student,SC,Course  WHERE   Student.Sno=SC.Sno   AND   Course.Cno=SC.Cno   AND

Sdept=’计算机系’  AND Grade>(SELECT  AVG(Grade)  FROM  SC,Course,Student  WHERE  SC.Cno=Course.Cno  AND  Student.Sno=SC.Sno

AND   Sdept=’计算机系’)

28.  查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。

SELECT    Sname,Grade  FROM   Student,SC,Course  WHERE  Student.Sno

=SC.Sno   AND   Course.Cno=SC.Cno   AND  Cname=‘VB’  AND  Grade>

(SELECT   AVG(Grade)   FROM  SC,Course   WHERE   Course.Cno=SC.Cno   AND   Cname=‘VB’)

29.  查询没选VB的学生姓名和所在系。

SELECT  Sname, Sdept  FROM  Student   WHERE Sno NOT IN(SELECT  Sno  FROM SC,Course  WHERE SC.Cno=Course.Cno  AND  Cname=‘VB’)

或者

SELECT  Sname,Sdept  FROM  Student WHERE NOT EXISTS(SELECT\* FROM SC,Course  WHERE   Course.Cno=SC.Cno  AND   Sno=Student.Sno  AND  Cname=‘VB’)

30.  查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。

SELECT  Cname,Semester,Credit  FROM  Course c1 WHERE NOT  EXISTS

(SELECT \*  FROM Course c2 WHERE  c1.Semester=c2.Semester  AND   c1.Credit<c2.Credit)

31.  查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。

SELECT  Cname,sname,SC.Cno,Grade

FROM   student  join sc on  student. sno=sc. sno  join   course on course.cno=sc.cno

WHERE  grade=(SELECT  max (grade)

FROM  sc

WHERE  cno=course.cno) order BY SC.Cno  ASC

或者

SELECT  sc1.sno,Sname,Cno, Grade  FROM SC sc1,student  WHERE  not exists

(SELECT \* FROM SC sc2 WHERE  sc1.Cno=sc2.Cno  AND  sc1.Grade<sc2.Grade)  AND  Grade IS NOT NULL  AND  Student.Sno=sc1.Sno

order  BY Cno ASC

32.  查询选了全部课程的学生姓名

SELECT  Sname  FROM Student WHERE  Sno  in

(SELECT  Sno FROM SC GROUP BY  Sno  having  COUNT(\*)=

(SELECT  COUNT(\*)  FROM Course))

或者

SELECT  Sname  FROM student

WHERE  NOTexists(SELECT\*FROM course

WHERE  NOT exists(SELECT\*FROM SC

WHERE   Sno=student.sno  AND  cno=Course.Cno))

33.  创建一个新表，表名为test，其结构为（COL1, COL 2, COL 3），其中，

COL1：整型，允许空值。

COL2：普通编码定长字符型，长度为10，不允许空值。

COL3：普通编码定长字符型，长度为10，允许空值。

试写出按行插入如下数据的语句（空白处表示是空值）。

<table> 
 <tbody> 
 <tr> 
 <td> create&nbsp;table &nbsp;test( C0L1&nbsp;int, C0L2 &nbsp;char(10)&nbsp;not &nbsp;null, C0L3 &nbsp;char(10) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) insert &nbsp;into &nbsp;test(C0L2) &nbsp;values(‘B1’) insert &nbsp;into &nbsp;test values(1,‘B2’,‘C2’) insert &nbsp;into &nbsp;test(C0L1,C0L2) &nbsp;values(2,‘B3’) </td> 
 </tr> 
 </tbody> 
</table>

34.  利用23题建立的VB\_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB\_Grade表中。

INSERT  INTO VB\_Grade SELECT Sname,Sdept ,Grade  FROM  Student,SC,Course  WHERE  Student.Sno=SC.Sno  AND

SC.Cno=Course.Cno  AND  Sdept=’信息管理系’  AND  Cname=‘VB’

35.  将所有选修C001课程的学生的成绩加10分。

UPDATE  SC  SET Grade=Grade+10  WHERE  Cno=‘C001’

36.  将计算机系所有学生的“计算机文化学”的考试成绩加10分。

UPDATE  SC  SET Grade=Grade+10  WHERE

Sno in( SELECT SC.Sno FROM Student,SC,Course

WHERE  Student.Sno=SC.Sno  AND  SC.Cno=Course.Cno

AND Sdept=’计算机系’  AND  Cname=’计算机文化学’)

37.  修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。

UPDATE  SC  SET Grade=Grade+

CASE  Sdept

WHEN ’通信工程系’  THEN 10

WHEN  ’信息管理系’  THEN 5

else 0

end

FROM  Student,SC,Course

WHERE  SC.Sno=Student.Sno  AND  Course.Cno=SC.Cno  AND  Cname=‘VB’

38.  删除成绩小于50分的学生的选课记录。

DELETE  FROMSC WHERE  Grade<50

39.  删除计算机系VB考试成绩不及格学生的VB选课记录。

DELETE  FROM  SC

FROM  SC,Student,Course  WHERE  SC.Sno=Student.Sno

AND  SC.Cno=Course.Cno  ANDSdept=’计算机系’  AND

Cname=‘VB’  AND  Grade<60

40.  删除“VB”考试成绩最低的学生的VB修课记录。

DELETE  FROM  SC FROM   SC,Course

WHERE  SC.Cno=Course.Cno  AND   Cname=‘VB’

AND Grade=

(SELECT  MIN(Grade)  FROM  SC,Course  WHERE  SC.Cno=Course.Cno  AND  Cname=‘VB’)

41.  删除没人选的课程的基本信息。

DELETE  FROM  Course FROM Course left  joinSC on Course.Cno=SC.Cno

WHERE  SC.Cno  IS  NULL

**本章有关建表和插入表格中的数据**

<table> 
 <tbody> 
 <tr> 
 <td> CREATE &nbsp;TABLE&nbsp;Student( &nbsp; Sno&nbsp;&nbsp;&nbsp;&nbsp;CHAR(7)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PRIMARYKEY, &nbsp; Sname&nbsp; &nbsp;NCHAR(5)&nbsp;&nbsp;&nbsp;&nbsp;NOTNULL, &nbsp; Ssex&nbsp; &nbsp;&nbsp;NCHAR(1), &nbsp; Sage&nbsp; &nbsp;&nbsp;TINYINT, &nbsp; Sdept&nbsp; &nbsp;NVARCHAR(20) ) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </td> 
 </tr> 
 <tr> 
 <td> CREATE &nbsp;TABLE &nbsp;Course( &nbsp; Cno&nbsp; &nbsp; &nbsp; &nbsp;CHAR(6), &nbsp; Cname&nbsp;&nbsp;&nbsp;&nbsp;NVARCHAR(20)&nbsp;NOTNULL, &nbsp; Credit&nbsp; &nbsp;&nbsp;TINYINT, &nbsp; Semester &nbsp;TINYINT, &nbsp; PRIMARYKEY(Cno) ) </td> 
 </tr> 
 <tr> 
 <td> CREATE &nbsp;TABLE &nbsp;SC( &nbsp; Sno&nbsp;&nbsp;&nbsp;CHAR(7)&nbsp;NOTNULL, &nbsp; Cno&nbsp;&nbsp;&nbsp;CHAR(6)&nbsp;NOTNULL, &nbsp; Grade&nbsp;&nbsp;TINYINT, &nbsp; PRIMARYKEY(Sno,Cno), &nbsp; FOREIGNKEY(Sno)&nbsp;&nbsp;REFERENCES&nbsp;&nbsp;Student(Sno), &nbsp; FOREIGNKEY(Cno)&nbsp;&nbsp;REFERENCES&nbsp;&nbsp;Course(Cno)&nbsp; )&nbsp; </td> 
 </tr> 
 <tr> 
 <td> insert &nbsp;into &nbsp;student values(‘0811101’,’李勇’,’男’,21,’计算机系’) insert &nbsp;into &nbsp;student values(‘0811102’,’刘晨’,’男’,20,’计算机系’) insert &nbsp;into &nbsp;student values(‘0811103’,’王敏’,’女’,20,’计算机系’) insert &nbsp;into &nbsp;student values(‘0811104’,’张小红’,’女’,19,’计算机系’) insert &nbsp;into &nbsp;student values(‘0821101’,’张立’,’男’,20,’信息管理系’) insert &nbsp;into &nbsp;student values(‘0821102’,’吴宾’,’女’,19,’信息管理系’) insert &nbsp;intos &nbsp;tudent values(‘0821103’,’张海’,’男’,20,’信息管理系’) insert &nbsp;into &nbsp;student values(‘0831101’,’钱小平’,’女’,21,’通信工程系’) insert &nbsp;into &nbsp;student values(‘0831102’,’王大力’,’男’,20,’通信工程系’) insert &nbsp;into &nbsp;student values(‘0831103’,’张姗姗’,’女’,19,’通信工程系’) &nbsp; insert &nbsp;into &nbsp;course values(‘C001’,’高等数学’,4,1) insert &nbsp;into &nbsp;course values(‘C002’,’大学英语’,3,1) insert &nbsp;into &nbsp;course values(‘C003’,’大学英语’,3,2) insert &nbsp;into &nbsp;course values(‘C004’,’计算机文化学’,2,2) insert &nbsp;into &nbsp;course values(‘C005’,‘VB’,2,3) insert &nbsp;into &nbsp;course values(‘C006’,’数据库基础’,4,5) insert &nbsp;into &nbsp;course values(‘C007’,’数据结构’,4,4) insert &nbsp;intoc &nbsp;ourse values(‘C008’,’计算机网络’,4,4) &nbsp; insert &nbsp;into&nbsp;sc values(‘0811101’,‘C001’,96) insert &nbsp;into&nbsp;sc values(‘0811101’,‘C002’,80) insert&nbsp;into&nbsp;sc values(‘0811101’,‘C003’,84) insert &nbsp;into&nbsp;sc values(‘0811101’,‘C005’,62) insert&nbsp;into&nbsp;sc values(‘0811102’,‘C001’,92) insert &nbsp;into&nbsp;sc values(‘0811102’,‘C002’,90) insert&nbsp;into&nbsp;sc values(‘0811102’,‘C004’,84) insert&nbsp;into&nbsp;sc values(‘0821102’,‘C001’,76) insert &nbsp;into&nbsp;sc values(‘0821102’,‘C004’,85) insert &nbsp;into &nbsp;sc values(‘0821102’,‘C005’,73) insert &nbsp;into&nbsp;sc values(‘0821102’,‘C007’,NULL) insert&nbsp;into &nbsp;sc values(‘0821103’,‘C001’,50) insert &nbsp;into &nbsp;sc values(‘0821103’,‘C004’,80) insert&nbsp;into &nbsp;sc values(‘0831101’,‘C001’,50) insert &nbsp;into &nbsp;sc values(‘0831101’,‘C004’,80) insert &nbsp;into &nbsp;sc values(‘0831102’,‘C007’,NULL) insert&nbsp;into &nbsp;sc values(‘0831103’,‘C004’,78) insert &nbsp;into&nbsp;sc values(‘0831103’,‘C005’,65) insert &nbsp;into&nbsp;sc values(‘0831103’,‘C007’,NULL) </td> 
 </tr> 
 </tbody> 
</table>

** **

## C.3 第6章上机实验 ##

下列实验均使用SQL Server 的SSMS工具实现。利用第4章上机实验创建的“学生数据库”中Student、Course和SC表，完成下列实验。

1.  写出实现下列操作的SQL语句，并执行所写代码。

（1）在Student表上为Sname列建立一个聚集索引，索引名为：IdxSno。（提示：若执行创建索引的代码，请先删除该表的主键约束）

<table> 
 <tbody> 
 <tr> 
 <td> 注意: 一个表只能有一个聚集索引，可以有多个非聚集索引 (聚集索引对数据按索引关键字值进行物理排序，数据行本身只能按一个顺序存储) Student本身创建主键(Sno)的同时会默认创建唯一聚集索引，且Sno为SC表的外键，所以，首先要删除SC表的外键约束，再删除Student表的主键约束 —-chenyuchao </td> 
 </tr> 
 </tbody> 
</table>

ALTER TABLE SC DROP CONSTRAINT   FK\_\_SC\_\_Sno\_\_31EC6D26

ALTER TABLE STUDENT DROP CONSTRAINT  PK\_\_Student\_\_CA1FE464CD8CBE4F

CREATE CLUSTERED INDEX  idxSno ON  Student(Sname)

（2）在Course表上为Cname列建立一个唯一的非聚集索引，索引名为：IdxCN

CREATE   unique  NONCLUSTERED   INDEX  idxCN ON  Course(Cname)

(如果是在表中数据都插入完成后建立索引，此处会报错，因为Cname中有两个不同学期的大学英语课程，就不唯一了，请注意)

（3）在SC表上为Sno和Cno建立一个组合的聚集索引，索引名为：IdxSnoCno。（提示：若执行创建索引的代码，请先删除该表的主键约束）

同样需要删除主键约束

ALTER  TABLE  SC DROP CONSTRAINT  PK\_\_SC\_\_E6000253A86AC951

CREATE   CLUSTERED   INDEX    idxSnoCno   ON   SC(Sno,Cno)

（4）删除Sname列上建立的IdxSno索引。

DROP  INDEX  Student.idxSno

2.  写出创建满足下述要求的视图的SQL语句，并执行所写代码。

（1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。

CREATE  VIEW  v1 AS

SELECT  Student.Sno,Sname,Sdept,SC.Cno,Cname,Credit

FROM  student,SC,Course  WHERE  Student.Sno=SC.Sno  AND  Course.Cno=SC.Cno

（2）查询学生的学号、姓名、选修的课程名和考试成绩。

CREATE  VIEW  v2 AS

SELECT   Student.Sno,Sname,Cname,Grade  FROM  Student,SC,Course

WHERE    Student.Sno=SC.Sno   AND   Course.Cno=SC.Cno

（3）统计每个学生的选课门数，要求列出学生学号和选课门数。

CREATE  VIEW  v3

SELECT  Sno,COUNT(Cno) Coursenum  FROM SC GROUP BY  Sno

（4）统计每个学生的修课总学分，要求列出学生学号和总学分（说明：考试成绩大于等于60才可获得此门课程的学分）。

CREATE  VIEW  v4

SELECT Sno,SUM(Credit)  Creditsum

FROM  SC,Course   WHERE  SC.Cno=Course.Cno  AND  Grade>=60  GROUP  BY Sno

3.  利用第2题建立的视图，完成如下查询。

（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。

SELECT   Sname,Cname,Grade  FROM  v2 WHERE Grade>=90

（2）查询选课门数超过3门的学生的学号和选课门数。

SELECT  Sname,Cname,Grade  FROM  v2 WHERE Grade>=90

（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。

SELECT   Sname,Cnum  FROM  Student,v3   WHERE   v3.Sno=Student.SnoANDSdept=’计算机系’  AND   Coursenum>3

（4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分。

SELECT   Student.Sno,Sname,Sdept,Creditsum FROM  Student,v4

WHERE   v4.Sno=Student.SnoAND   Creditsum>10

（5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。

SELECT  Sname,Sage,Sdept,Creditsum FROM  Student,v4

WHERE  v4.Sno=Student.Sno  AND  Creditsum>10  AND  Sage>=20

4.  修改第3题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。

ALTER  VIEW   v4  AS

SELECT   Sno,SUM(Credit)   Creditsum,COUNT(SC.Cno)   Coursenum

FROM   SC,Course   WHERE   SC.Cno=Course.Cno   GROUPBY Sno

## C.4 第7章上机实验 ##

利用第5章建立的学生数据库以及Student、Coures和SC表，完成下列操作。

1.  创建满足如下要求的后触发型触发器

。

<table> 
 <tbody> 
 <tr> 
 <td> 说明:对于insert into表名 values() 单行插入语句，触发器每次都会被触发 &nbsp;&nbsp;&nbsp;&nbsp; 对于 insert into表名 selec FROM表名 或者是 insert into 表名 values (),(),()….多行插入语句，触发器只会触发一次，所有的插入数据都会一并备份到inserted表中，然后执行触发筛选—-chenyuchao </td> 
 </tr> 
 </tbody> 
</table>

（1）限制学生的考试成绩必须在0-100之间。

CREATE   TRIGGER   tri1 ON SC  after  insert

if exists( SELECT GradeFROM  inserted WHERE Grade not  between 0  AND 100)

rollback

（2）限制不能删除成绩不及格的考试记录。

CREATE  TRIGGER  tri2 ON SC AFTER   DELETE

if exists(SELECT \* FROM deleted WHERE Grade<60)

rollback

（3）限制每个学期开设的课程总学分不能超过20。

CREATE  TRIGGER  tri3 ON Course AFTER  INSERT

if exists( SELECT SUM(Credit)  FROM  Course

GROUP BY   Semester  having  SUM(Credit)>20)

rollback

（4）限制每个学生每学期选的课程不能超过5门。

CREATE  TRIGGER  tri4 ON SC AFTER  INSERT

if exists( SELECT COUNT(SC.Cno)  FROM  SC,Course

WHERE  SC.Cno=Course.Cno  GROUPBY  Semester,Sno having  COUNT(SC.Cno)>5)

ROLLBACK

2.  创建满足如下要求的存储过程。

（1）查询每个学生的修课总学分，要求列出学生学号及总学分。

CREATE  PROC  p1

SELECT  Student.Sno,SUM(Credit)  CreditSum  FROM Student,SC,Course

WHERE  Student.Sno=SC.Sno  AND  SC.Cno=Course.Cno  GROUP BY Student.Sno

执行：execp1

（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值。

CREATE  PROC  p2 @dept  char(20)=’信息管理系’

SELECT  Student.Sno,Sname,SC.Cno,Course.Cname,Credit  FROM  Student, SC, Course

WHERE   Student.Sno=SC.Sno  AND  SC.Cno=Course.Cno  AND  Sdept=@dept

执行:  execp2

执行:  exec p2@dept=’计算机系’

（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。

CREATE  PROC p3 @dept char(20),@number int  output

SELECT@number=COUNT(Sno)  FROM  Student WHERE Sdept=@dept  AND  Ssex=’男’

执行: declare @numint

exec p3 计算机系,@num  output

print @num

（4）删除指定学生的修课记录，其中学号为输入参数。

CREATE  PROC  p4 @sno  char(20)

delete  FROM  SC WHERE  Sno=@sno

执行:exec  p4’ 0811101’

（5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。

Create  Proc  p5 @cno  char(20), @semester  Int

update  Course  set Semester=@semester  WHERE  Cno=@cno

执行:execp5‘C001’,4

## C.5第11章上机实验 ##

利用第4、5章建立的学生数据库和其中的Student、Course、SC表，并利用SSMS工具完成下列操作。

1.  用SSMS工具建立SQL Server身份验证模式的登录名：log1、log2和log3。

2.      用log1建立一个新的数据库引擎查询，在“可用数据库”下列列表框中是否能选中学生数据库？为什么？

3.      用系统管理员身份建立一个新的数据库引擎查询，将log1、log2和log3映射为学生数据库中的用户，用户名同登录名。

4.      在log1建立的数据库引擎查询中，现在在“可用数据库”下列列表框中是否能选中学生数据库？为什么？

5.      在log1建立的数据库引擎查询中，选中学生数据库，执行下述语句，能否成功？为什么？

SELECT \* FROMCourse

6.      在系统管理员的数据库引擎查询中，执行合适的授权语句，授予log1具有对Course表的查询权限，授予log2具有对Course表的插入权限。

7.      用log2建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？

INSERT INTO Course VALUES(‘C1001’，’数据库基础’，4，5)

再执行下述语句，能否成功？为什么？

SELECT \* FROMCourse

8.      在log1建立的数据库引擎查询中，再次执行下述语句：

SELECT\* FROM Course

这次能否成功？但如果执行下述语句：

INSERTINTO Course VALUES(‘C103’,’软件工程’,4, 5)

能否成功？为什么？

9.      log3建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？

CREATE TABLE NewTable(

C1 int,

C2 char(4))

10.   授予log3在学生数据库中具有创建表的权限。

11.   在系统管理员的数据库引擎查询中，执行下述语句：

GRANT CREATE TABLETO log3

CREATE SCHEMA log3AUTHORIZATION log3

ALTER USER log3WITH  DEFAULT\_SCHEMA = log3

12.   在log3建立一个新的数据库引擎查询中，再次执行第9题的语句，能否成功？为什么？

如果执行下述语句：

SELECT \* NewTable

能否成功？为什么？

</div>
 </div>

数据库系统教程(第二版何玉洁)课后数据库上机实验答案

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关计算机组成原理课后答案(唐第二版)

相关金属学与热处理(第二版)课后习题答案

相关《Python程序设计与算法基础教程（第二版）》江红余青松全部章节的课后习题，上机实践，课后答案，案例研究

相关计算机操作系统（慕课版）第二章课后题答案

相关《计算机网络教程》(微课版第五版) 第二章物理层课后习题及答案

相关 python语言程序设计2019版第二章课后答案-python语言程序设计基础课后答案第二章...

相关实验七数据查询【数据库系统实验】【含答案】

相关第二章课后题答案

相关数据库系统教程(第二版何玉洁)课后数据库上机实验答案

相关数据库笔记整理--基于《数据库系统概论》第五版王珊一书|第二章--关系数据库知识整理和课后习题答案