数据库系统原理第五章作业及其测验讲评教材

第五章习题-5设职工-社团数据库有3个基本表：职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）。1.定义职工表、社会团体表和参加表，并说明其主码和参照关系createtable职工(职工号char(5)primarykey,

姓名char(8)notnull,

年龄smallint,

性别char(2), constraintC1check(性别in('男','女')));createtable社会团体(编号char(6)primarykey,

名称char(20)notnull,

负责人char(5),

活动地点char(30), constraintC2foreignkey(负责人)references职工(职工号));createtable参加(职工号char(5),

编号char(6),

参加日期datetime, constraintC3primarykey(职工号,编号), constraintC4foreignkey(职工号)references职工(职工号), constraintC5foreignkey(编号)references社会团体(编号));…uniqueprimarykey,createtable职工(职工号char(5)notnullunique,

姓名char(8)notnull,

年龄smallint,

性别char(2), constraintCxprimarykey(职工号), constraintC1check(性别in('男','女')));createtable参加(职工号char(5)primarykey,

编号char(6)primarykey, constraintC4foreignkey(职工号)references职工(职工号), constraintC5foreignkey(编号)references社会团体(编号));外码的数据结构要与主码的相同！这道题出现的问题有：缺少逗号、分号、引号，混淆主码与唯一约束，忽略表之间数据结构的一致。。。2.建立视图社团负责人(编号,名称,负责人职工号,负责人姓名,负责人性别)createview社团负责人(编号,名称,负责人职工号,负责人姓名,负责人性别) asselect社会团体.编号,社会团体.名称,职工.职工号,职工.姓名, 职工.性别

from职工,社会团体

where职工.职工号=社会团体.负责人;参加人情况(职工号,姓名,社团编号,社团名称,参加日期)createview参加人情况(职工号,姓名,社团编号,社团名称,参加日期) asselect参加.职工号,姓名,参加.编号,名称,参加日期

from职工,社会团体,参加

where职工.职工号=参加.职工号and社会团体.编号=参加.编号;可写成：社会团体.负责人3.查找参加唱歌队或篮球队的职工号和姓名selectdistinct职工.职工号,姓名from职工,社会团体,参加where职工.职工号=参加.职工号and参加.编号=社会团体.编号 and(名称='篮球队'or名称='唱歌队');selectdistinct职工.职工号,姓名from职工,社会团体,参加where职工.职工号=参加.职工号and参加.编号=社会团体.编号 and名称in('篮球队','唱歌队');名称='篮球队'or名称='唱歌队';selectdistinct职工.职工号,姓名from职工,社会团体,参加where职工.职工号=参加.职工号and参加.编号=社会团体.编号 and

名称=('篮球队'or'唱歌队');这样会选择出只要满足了职工.职工号=参加.职工号and参加.编号=社会团体.编号and名称=‘篮球队’

或者名称=‘唱歌队’的结果。语法错误！！4.查找没有参加任何社会团体的职工情况select*from职工wherenotexists(select* from参加

where职工.职工号=职工号);select*from职工where职工号notin(select职工号

from参加);select职工.*from职工,参加where职工.职工号=参加.职工号and编号isnull;编号是参加表的主码，不可能为空，所以这这个查询的结果肯定为空。5.查找参加了全部社会团体的职工情况select*from职工wherenotexists(select* from社会团体

wherenotexists(select* from参加

where职工.职工号=参加.职工号and 社会团体.编号=参加.编号));6.查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号select职工号from职工wherenotexists(select* from参加参加1 where参加1.职工号=‘1001' andnotexists(select* from参加参加2 where职工.职工号=参加2.职工号 and参加2.编号=参加1.编号));select职工号from参加where编号in(select编号

from参加

where职工号='1001');这个查询所求的是：参加了职工号为“1001”的职工所参加团体中的一个的职工号。select职工号from参加where编号=(select编号

from参加

where职工号='1001');这是一个集合，所以前面不能用等号，要用IN。7.求每个社团的参加人数select编号,count(职工号)from参加groupby编号;select名称,count(职工号)from社会团体,参加where参加.编号=社会团体.编号groupby名称;社会团体.编号;服务器:消息8120，级别16，状态1，行1列‘社会团体.名称’在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUPBY子句中。编号名称…00001A…00002B…00002C…00003D…00004D………...8.求参加人数最多的社会团体的名称和参加人数select名称,count(参加.职工号)as参加人数from社会团体,参加where参加.编号=社会团体.编号groupby名称havingcount(*)>=all(selectcount(*) from参加,社会团体

where参加.编号=社会团体.编号

groupby名称);groupby名称havingcount(职工号)>=(allcount(职工号));count(职工号)返回的只是一个数，不是一个集合，所以不能对其使用all函数，即语法上会出错。9.求参加人数超过100人的社会团体的名称和负责人selectdistinct名称,负责人from社会团体where编号in(select编号

from参加

groupby编号havingcount(*)>=100);selectdistinct名称,负责人,职工号from社会团体,参加where社会团体.编号=参加.编号andexists(select编号

from参加

groupby编号havingcount(*)>=100);因为只有存在有一个社会团体的参加人数超过100，那exists(select编号

from参加

groupby编号havingcount(*)>=100的值一定是真，这样所查的结果必定是所有社会团体的名称及其负责人！！！第五章习题-6设工程-零件数据库中有4个基本表：供应商（供应商代码，姓名，所在城市，联系电话）；工程（工程代码，工程名，负责人，预算）；零件（零件代码，零件名，规格，产地，颜色）；供应零件（供应商代码，工程代码，零件代码，数量）第五章习题6：解答1）找出天津供应商的姓名和电话Select姓名,联系电话

From供应商

Where所在城市=‘天津市’;2）查找预算在50000~100000元之间的工程信息，并将结果按预算降序排列Select*

From工程

Where预算Between50000and100000

Orderby预算DESC;第五章习题6：解答3）找出使用供应商S1所供零件的工程号码Select工程代码

From供应零件

Where供应商代码=‘S1’;4）找出工程项目J2使用的各种零件名称及其数量Select零件名,数量

From供应零件,零件

Where供应零件.零件代码=零件.零件代码

and工程代码=‘J2’;第五章习题6：解答5）找出上海厂商供应的所有零件号码Select零件代码

From供应零件,供应商

Where供应零价.供应商代码=供应商.供应商代码

and供应商.所在城市=‘上海’;6）找出使用上海产的零件的工程名称Select工程名称

From供应零件,工程,零件

Where供应零件.工程代码=工程商.工程代码

and供应零件.零件代码=零件.零件代码

and产地=‘上海’;第五章习题6：解答7）找出没有使用天津产零件的工程号码Select工程代码from工程

Wherenotexists

(Select*

From供应零件,零件

Where供应零件.零件代码=零件.零件代码

and供应零件.工程代码=工程.工程代码

and产地=‘天津’);8）把全部红色零件的颜色改成蓝色Update零件Set颜色=‘蓝色’Where颜色=‘红色’;第五章习题6：解答9）将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并作其他必要的修改Update供应零件

Set供应商代码=‘S3’

Where供应商代码=‘S5’

and工程代码=‘J4’

and零件代码=‘P6’;10）从供应商关系中删除S2的纪录，并从供应零件关系中删除相应的纪录DeleteFrom供应零件Where供应商代码=‘S2’;DeleteFrom供应商Where供应商代码=‘S2’;关键1:

供应商(供应商代码，姓名，所在城市，联系电话)；工程（工程代码，工程名，负责人，预算）零件（零件代码，零件名，规格，产地，颜色）供应零件（供应商代码，工程代码，零件代码，数量）4.找出工程项目J2使用的各种零件名称及其数量Select零件名称,数量From零件,供应零件Where零件.零件代码=供应零件.零件代码and工程代码=‘J2’Select零件名称,sum(数量)From零件,供应零件Where零件.零件代码=供应零件.零件代码and工程代码=‘J2’Groupby零件名称

不同供应商提供相同的零件关键2:

供应商(供应商代码，姓名，所在城市，联系电话)；工程（工程代码，工程名，负责人，预算）零件（零件代码，零件名，规格，产地，颜色）供应零件（供应商代码，工程代码，零件代码，数量）7.找出没有使用天津产零件的工程号码Select工程号码From工程Where工程号码notexists(select工程号码from供应零件,零件Where零件.零件代码=供应零件.零件代码And供应零件.工程代码=工程.工程代码And产地=‘天津’)如果有些零件的产地名称是‘天津市’，怎么办？第五章习题8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）1.找出选修了”程军”老师教的所有课程的学生姓名SelectSNFromSWherenotexists(select*fromCwhereTEARCHER=“程军”Wherenotexists(select*fromSCwhereS.S#=SC.S#and

C.C#=SC.C#))第五章习题8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）6.检索选修了四门以上课程的学生总成绩(不统计不及格的课程)，并要求按照成绩降序排列selectS#,count(*)as课程门数,sum(Grade)as总成绩fromscwheregrade>=60groupbyS#havingcount(*)>=4orderby总成绩desc第五章习题8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）8.检索不学C6课程的男学生的学号和姓名selectS#,SN,SEXfromstudentwhereSEX='男'andS#notin(selectS#fromSCwhereC#='C6')selectstu_id,Name,SEXfromstudentwhereSEX='男'andnotexists

(select*fromSCwhereSC.S#=S.S#andC#='C6');第五章习题8:

S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）9.将SC表中每门课程的平均成绩插入SC_C(CNO,CNAME,AVG_GRADE)CreatetableSC_C(CNOchar(8),CNAMEchar(20),AVG_GRADEnumeric(8,2))InsertintoSC_CselectC.C_ID,CN,AVG(GRADE)fromSC,CwhereSC.C#=C.C#groupbyC.C#,CN第五章习题8:S(S#，SN，SEX，AGE,DEPT)；C（C#，CN，TEARCHER）SC(S#，C#，GRADE）11.从SC表中把数学课程中低于数学平均分的元组删除DeletefromSCwheregrade<(selectavg(grade)fromSCwhereC#in(selectC#fromCwhereCN='数学'))andC#in(selectC#fromCwhereCN='数学')10—22选择题答案：10—15：B;C;A;C;C;B16--22:A;C;B;C;B;D;C课堂测验题:设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)；其中学生表S由学号(Sno)、学生姓名(Sname)和学生住址(Saddress)和学生所在系(Sdept)组成；课程表C由课程编号(Cno)、课程名称(Cname)组成；教师表T由教师职工号(Tno)、教师姓名(Tname)和教师电话(Phone)组成；表示学生选课的选课表SCT由学号(Sno)、课程编号(Cno)、教师职工号(Tno)、考试成绩(Grade)、学年(Tyear)组成。用SQL语言完成下列各项操作：创建表SCT(Sno,Cno,Tno,Grade,Tyear)；查询学号(Sno)为‘03287512’的学生姓名Sname和地址Saddress；查询电话号码中含有‘3762’的教师姓名Tname；将所有电话号码中含有‘3762’的教师的电话号码前加‘8’，形成新的电话号码；查询2006学年(Tyear=2006)选修了课程号Cno为‘C012’，且考试成绩(Grade)不及格(<60)的学生人数；课堂测验题:设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)；用SQL语言完成下列各项操作：查询2006学年(Tyear=2006)选修过的全部课程的课程编号Cno、及其相应每门课的选修人数、平均成绩；查询选修了所有课程的学生学号、姓名；查询没有选修张强老师(Tname=’张强’)所授课程的学生学号和学生姓名；求2006学年(Tyear=2006)选课人数最多的课程编号、选课人数；建立视图View_SCT，它记录了教师编号为“T01”授课情况，包括：课程编号、课程名称、选修人数、平均成绩、最高分；将机电工程系的学生，选修了课程编号Cno为‘C100’的所有学生的成绩加5分。课堂测验题:

部分解答设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

4.将所有电话号码中含有‘3762’的教师的电话号码前加‘8’，形成新的电话号码；UpdateTSetPhone=‘8’+PhoneWherePhonelike‘%3762%’测验题:

设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

5.查询2006学年(Tyear=2006)选修了课程号Cno为‘C012’，且考试成绩(Grade)不及格(<60)的学生人数；Selectcount(Sno)FromSCTWhereTyear=2006andGrade<60andCno=‘C012’错例分析:GroupbySnoSelectcount(Sno)FromSCTWhereTyear=2006andCno=‘C012’andGrade<60本题是统计选修了课程号为Cno=‘C012’的所有学生的人数,直接在表SCT中查询即可,不能以学号来分组.如果以Sno分组则每元组的count(Sno)列始终为１．测验题:

设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

6.查询2006学年(Tyear=2006)选修过的全部课程的编号Cno、及其相应每门课的选修人数、平均成绩；SelectCno,count(Sno)选修人数,Avg(Grade)平均成绩FromSCTWhereTyear=2006GroupByCno如果没写,查询结果中就无列名了啊!!测验题:

设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

7.查询选修了所有课程的学生学号、姓名；selectSno,NamefromSwhereSno

in(selectSnofromSCTwherenotexists(select*fromCwherenotexists(select*fromSCTwhereSCT.Sno=S.SnoandC.Cno=SCT.Cno)))测验题:

设有下列四个表：S(Sno,Sname,Saddress,Sdept)；C(Cno,Cname)；T(Tno,Tname,Phone);SCT(Sno,Cno,Tno,Grade,Tyear)

8.查询没有选修张强老师所授课程的学生学号和学生姓名

selectSno,NamefromswhereSnonotin(selectSnofromSCTwhereTnoin(selectT.TnofromTwhereTname=‘张强’))selectSno,Namefromswherenotexists(select*fromSCTwhereexists(select*fromTwhereTname=‘张强’andT.Tno=SCT.TnoandS.Sno=SCT.