第3章作业解答参考.docx

第3章作业解答参考.docx

《第3章作业解答参考.docx》由会员分享，可在线阅读，更多相关《第3章作业解答参考.docx（18页珍藏版）》请在冰豆网上搜索。

第3章作业解答参考

10.设有一数据库GradeManager（成绩管理），包括四个表：

学生表（Student）、课程表（Course）、班级表（Class）以及成绩表（Grade）。

四个表的结构如图2-4所示，数据如图2-5所示。

用SQL语句创建四个表。

Student

属性名

数据类型

可否为空

含义

Sno

Char（7）

否

学号（唯一）

Sname

VarChar（20）

否

学生姓名

Ssex

Char

（2）

否

性别

Sage

Smallint

可

年龄

Clno

Char（5）

否

学生所在班级

Course

属性名

数据类型

可否为空

含义

Cno

Char

（1）

否

课程号（唯一）

Cname

VarChar（20）

否

课程名称

Credit

Smallint

可

学分

Class

属性名

数据类型

可否为空

含义

Clno

Char（5）

否

班级号（唯一）

Speciality

VarChar（20）

否

班级所在专业

Inyear

Char（4）

否

入校年份

Number

Integer

可

班级人数

Monitor

Char（7）

可

班长学号

Grade

属性名

数据类型

可否为空

含义

Sno

Char（7）

否

学号

Cno

Char

（1）

否

课程号

Gmark

Decimal（4,1）

可

成绩

图1成绩管理数据库中的表结构

Student

Sno

Sname

Ssex

Sage

Clno

2000101

李勇

男

20

00311

2000102

刘诗晨

女

19

00311

2000103

王一鸣

男

20

00312

2000104

张婷婷

女

21

00312

2001101

李勇敏

女

19

01311

2001102

贾向东

男

22

01311

2001103

陈宝玉

男

20

01311

2001104

张逸凡

男

21

01311

Course

Cno

Cname

Credit

1

数据库

4

2

离散数学

3

3

管理信息系统

2

4

操作系统

4

5

数据结构

4

6

数据处理

2

7

C语言

4

Class

Clno

Speciality

Inyear

Number

Monitor

00311

计算机软件

2000

120

2000101

00312

计算机应用

2000

140

2000103

01311

计算机软件

2001

220

2001103

Grade

Sno

Cno

Gmark

2000101

1

92

2000101

3

88

2000101

5

86

2000102

1

78

2000102

6

55

2000103

3

65

2000103

6

78

2000103

5

66

2000104

1

54

2000104

6

83

2001101

2

70

2001101

4

65

2001102

2

80

2001102

4

90

2001102

6

83

2001103

4

76

2001103

6

56

图2成绩管理数据库中的数据

设习题二中第10题成绩管理数据库的表结构说明中加入了完整性约束说明，如图3-6所示，请用SQL语句完成图中约束的说明。

Student

属性名

数据类型

可否为空

含义

完整性约束

Sno

Char（7）

否

学号

主码

Sname

VarChar（20）

否

学生姓名

Ssex

Char

（2）

否

性别

男或女，默认为男

Sage

Smallint

可

年龄

大于14，小于65

Clno

Char（5）

否

学生所在班级号

外部码，级联

Course

属性名

数据类型

可否为空

含义

完整性约束

Cno

Char

（1）

否

课程号

主码

Cname

VarChar（20）

否

课程名称

Credit

Smallint

可

学分

1、2、3、4、5、6之一

Class

属性名

数据类型

可否为空

含义

完整性约束

Clno

Char（5）

否

班级号

主码

Speciality

VarChar（20）

否

班级所在专业

Inyear

Char（4）

否

入校年份

Number

Integer

可

班级人数

大于1，小于100

Monitor

Char（7）

可

班长学号

外部码

Grade

属性名

数据类型

可否为空

含义

完整性约束

Sno

Char（7）

否

学号

主属性，外部码，级联

Cno

Char

（1）

否

课程号

主属性，外部码，级联

Gmark

Decimal（4,1）

可

成绩

大于0，小于100`

图3加了约束说明后的表结构

--创建course表

createtablecourse

（

cnochar

（1）primarykey,

cnamevarchar（20）notnull,

creditsmallint

）

--创建class表

createtableclass

（

clnochar（5）primarykey,

specialityvarchar（20）notnull,

inyearchar（4）notnull,

numberinteger,

monitorchar（7）

）

--创建student表

createtablestudent

（

snochar（7）primarykey,

snamevarchar（20）notnull,

ssexchar

（2）notnull,

sagesmallint,

clnochar（5）notnullforeignkey（clno）referencesclass（clno）ondeletecascade

）

--为class表添加参照完整性

altertableclass

addconstraintmonitorforeignkey（monitor）referencesstudent（sno）ondeletenoaction

--创建grade表

createtablegrade

（

snochar（7）notnullforeignkey（sno）referencesstudent（sno）ondeletecascade,

cnochar

（1）notnullforeignkey（cno）referencescourse（cno）ondeletecascade,

gmarkdecimal（4,1）

）

--插入数据class

insertintoclass（clno,speciality,inyear,number）

values（'00311','计算机软件','2000',120）

--插入数据course

insertintocourse

values（'1','数据库',4）

--插入数据student

insertintostudent

values（'2000101','李勇','男',20,'00311'）

--修改数据class

updateclass

setmonitor='2000101'

whereclno='00311'

--插入数据grade

insertintograde

values（'2000101','1',92）

11.针对以上四个表，用SQL语言完成以下各项操作：

1给学生表增加一属性Nation（民族），数据类型为Varchar（20）；

altertablestudent

addnationvarchar（20）

②删除学生表中新增的属性Nation；

altertablestudent

dropcolumnnation

③向成绩表中插入记录（”2001110”，”3”，80）；

insertintograde

values（'2001101','3',80）

④修改学号为”2001110”的学生的成绩为70分；

updategrade

setgmark=70

wheresno='2001101'

⑤删除学号为”2001110”的学生的成绩记录；

deletegrade

fromgrade

wheresno='2001101'

⑥为学生表创建一个名为IX_Class的索引，以班级号的升序排序；

createindexIX_Class

onstudent（clnoasc）

⑦删除IX_Class索引。

dropindexstudent.IX_Class

12.针对以上四个表，用SQL语言完成以下各项查询：

①找出所有被学生选修了的课程号；

selectdistinctcno

fromgrade

②找出01311班女学生的个人信息；

select*

fromstudent

whereclno='01311'andssex='女'

3出01311班、01312班的学生姓名、性别、出生年份；

selectsname,ssex,2010-sageasbirthyear

fromstudent

whereclno='01311'orclno='01312'

4找出所有姓李的学生的个人信息；

select*

fromstudent

wheresnamelike'李%'

⑤找出学生李勇所在班级的学生人数；

--使用连接查询

selectnumber

fromclass,student

whereclass.clno=student.clnoandsname='李勇'

--或使用嵌套子查询

selectnumber

fromclass

whereclno=

（selectclno

fromstudent

wheresname='李勇'）

②找出课程名为操作系统的平均成绩、最高分、最低分；

selectavg=avg（gmark）,max=max（gmark）,min=min（gmark）

fromgrade,course

whereo=oandcname='操作系统'

⑦找出选修了课程的学生人数；

selectcount（distinctsno）as选课人数

fromgrade

⑧找出选修了课程操作系统的学生人数；

--使用连接查询

selectcount（sno）as选操作系统人数

fromgrade,course

whereo=oandcname='操作系统'

--或使用嵌套子查询

selectcount（*）as选操作系统人数

fromgrade

wherecno=

（selectcno

fromcourse

wherecname='操作系统'）

⑨找出2000级计算机软件班的成绩为空的学生姓名。

--使用连接查询

Selectsname

Fromstudent,grade,class

Wherestudent.sno=grade.snoandstudent.clno=class.clno

andspeciality='计算机软件'andinyear='2000'andgmarkisnull

--或使用嵌套子查询

Selectsname

Fromstudent

Wheresnolike'2000%'

andclnoin

（selectclno

fromclass

wherespeciality='计算机软件'

andsno=any

（selectsnofromgrade

wheregmarkisnull））

4.针对以上四个表，用SELECT的嵌套查询完成以下各项查询：

1找出与李勇在同一个班级的学生信息；

select*

fromstudent

whereclno=（

selectclno

fromstudent

wheresname=’李勇’）

2找出所有与学生李勇有相同选修课程的学生信息；

select*

fromstudent

wheresnoin

（selectdistinctsno

fromgrade

wherecnoin

（selectcno

fromgrade

wheresno=

（selectsno

fromstudent

wheresname=’李勇’）））

3找出年龄介于学生李勇和25岁之间的学生信息；

select*

fromstudent

wheresagebetween

（selectsage

fromstudent

wheresname=’李勇’）and25

--使用自身连接查询

selects1.*

fromstudents1,students2

wheres1.sagebetweens2.sageand25ands2.sname='李勇'ands2.sno

4找出选修了课程操作系统的学生学号和姓名；

selectsno,sname

fromstudent

wheresnoin

（selectsno

fromgrade

wherecno=

（selectcno

fromcourse

wherecname=’操作系统’））

--或使用相关子查询

selectsname,sno

fromstudent

whereexists

（select*

fromgrade

wheresno=student.snoandcno=

（selectcno

fromcourse

wherecname=’操作系统’））

--或使用连接查询

selectstudent.sno,sname

fromstudent,grade,course

wherestudent.sno=grade.snoando=o

andame=’操作系统’

5找出所有没有选修1号课程的学生姓名；

selectsname

fromstudent

wheresnonotin

（selectsno

fromgrade

wherecno=1）

`

--或使用all关键字

selectsname

fromstudent

wheresno<>all

（selectsno

fromgrade

wherecno=1）

--或使用相关子查询

selectsname

fromstudent

wherenotexists

（select*

fromgrade

wheresno=student.snoandcno=1）

⑥找出选修了全部课程的学生姓名。

（提示：

可找出这样的学生，没有一门课程是他不选修的。

）

selectsname

fromstudent

wherenotexists

（select*

fromcourse

wherenotexists

（select*

fromgrade

wheregrade.sno=student.snoando=o））

14.针对以上四个表，用SQL语言完成以下各项查询：

1查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；

selectsno,gmark

fromgrade

wherecno=’3’

orderbygmarkdesc

2查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列；

select*

fromstudent

orderbyclno,sagedesc

3求每个课程号及相应的选课人数；

selectcno,count（distinctsno）as选课人数

fromgrade

groupbycno

--包括没被选修课程及其相应的选课人数，方法一，使用并集：

selectcno,count（distinctsno）as选课人数

fromgrade

groupbycno

union

selectcno,0

fromcourse

wherecno<>all

（selectcno

fromgrade）

--包括没被选修课程及其相应的选课人数，方法二，使用外连接查询：

selecto,count（distinctsno）as选课人数

fromcourse,grade

whereo*=o

groupbyo

4查询选修了3门以上课程的学生学号。

Selectsno

Fromgrade

Groupbysno

Havingcount（cno）>=3

15.针对以上四个表，用SQL语言完成以下各项操作：

1对每个班，求学生的平均年龄，并把结果存入数据库；

--方法一，使用into关键字

selectclno,avg（student.sage）asavg_age

intot_avgage

fromstudent

groupbyclno

--方法二，先新建表，后带子查询的插入数据

createtables_avg

（clnochar（6）primarykey,

avg_ageint）

go

insertintos_avg

selectclno,avg（sage）

fromstudent

groupbyclno

方法三，使用视图的方式，但注意视图只保存查询定义并不存放实际数据：

createviews_avg_age

as

selectclno,avg（sage）asavg_age

fromstudent

groupbyclno

2将01311班的全体学生的成绩置零；

updategrade

setgmark=0

wheresnoin（selectsno

fromstudent

whereclno=’01311’）

3删除2001级计算机软件的全体学生的选课记录；

错解：

delete

fromgrade,student,class

wheregrade.sno=student.snoandstudent.clno=class.clno

andspeciality=’计算机软件’andinyear=’2001’

正解：

deletefromgrade

wheresnoin

（selectsno

fromstudent

whereclnoin

（selectclno

fromclass

whereinyear=’2001’

andspeciality=’计算机软件’））

4学生李勇已退学，从数据库中删除有关他的记录。

]

--删除grade表中李勇同学选课记录

delete

fromgrade

wheresno=

（selectsno

fromstudent

wheresname=’李勇’）

go

--修改李勇所在班级人数

updateclass

setnumber=number-1

whereclno=

（selectclno

fromstudent

wheresname=’李勇’）

go

--若李勇是班长，则还应修改该班级班长的数据

updateclass

setmonitor=null

whereclno=

（selectclno

fromstudent

wheresname=’李勇’）

go

--删除student表中李勇学生记录

delete

fromstudent

wheresname=’李勇’

16.视图操作：

1建立01312班选修了1号课程的学生视图Stu_01312_1；

createviewStu_013121_1

as

selectstudent.sno,sname,ssex,sage,clno

fromstudent,grade

wherestudent.sno=grade.snoandclno=01312andcno=1

2建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；

createviewStu_013121_2

as

selectStu_013121_1.*

fromStu_013121_1,grade

whereStu_013121_1.sno=grade.snoandgmark<60

3建立视图Stu_year，由学生学号、姓名、出生年份组成。

CreateviewStu_year

As

Selectsno,sname,2002-sageasbirth_year

Fromstudent

4查询1983年以后出生的学生姓名。

Selectsname

FromStu_