数据库第三章课后习题解答

3-3 习题33.4 在SQL Server中，创建一个名为students且包含有下列几个属性的表。SNO char(10)；NAME varchar(10)；SEX char(1)；BDATE datetime；DEPT varchar(10)；DORMITORYvarchar(10).要求：1.采用两种形式创建表，即用SQL语句和用图形界面的形式来创建。2.定义必要的约束，包括主键SNO,NAME值不允许为空，且SEX取值为0或1。【解答】进入SQL查询分析器建立查询，创建students表的SQL语句如下，操作如图3.17所示。use mydb /* 假设在mydb库中建表 */create table students(SNO char(10) not NULL primary key, NAME varchar(10) not NULL, SEX char(1) not NULL check(sex=0 or sex=1), BDATE datetime, DEPT varchar(10), DORMITORY varchar(10)图3.17 用SQL语句创建students表进入企业管理器用基本操作创建students表。用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。或者，用右键单击“mydb”数据库下一级的“表”，从弹出的菜单中选择“新建表”。然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图3.18所示。图3.18 用基本操作创建students表其中，SEX字段取值为0或1，需要建立约束。操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图3.19所示。图3.19 输入约束表达式最后，单击“保存”图标，SQL Server将弹出一个“选择名称”对话框，输入表名“students”，单击“确定”按钮，新建的students表结构将被保存起来。3.5 在Access中，完成习题3.4的要求。【解答】create table students( SNO text(10) not null primary key, NAME text(10) not null, SEX text(10) not null, BDATE datetime, DEPT text(10), DORMITORY text(10) )注意，J-SQL的CREATE TABLE语句没有提供对字段的检查约束。可以在创建表后，使用基本操作方式，对SEX字段建立有效性规则。3.7 在SQL Server中，创建表Depts（DNO,DNAME,MGR）。用SQL语句在习题3.4中创建的Students表中将DEPT设置为外键，引用Depts表中的DNO列值。若某系还有学生时，不得在Depts表中删除该系的记录。use mydbcreate table Depts(DNO varchar(10) not NULL primary key, DNAME char(10) not NULL, MGR char(10) )alter table students add constraint c1 foreign key(Dept) references Depts(Dno) on delete cascade3.8 什么是视图？视图的作用是什么？在习题3.4所创建的Students表的基础上，建立一个显示所有计算机系学生的视图，假设计算机系的代号为CS。use mydbgocreate view student_cs_view /*SQL SERVER约定：本语句必须为批处理的第一个语句*/asselect *from studentswhere students.DEPT=cs3.11在SQL Server中完成下列操作： 用修改表结构语句在Students表中添加整型的Height和Weight字段。 创建一个规则并绑定到Height列，用以限制插入到该列的整数范围。 创建一个默认值并绑定到Weight列，插入记录时，默认值自动填充到该列中。 基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照(引用)完整性约束，并辅以其他操作予以验证。 创建并执行一个带SELECT查询语句的存储过程,统计出每个系的学生平均身高。 创建一个触发器，其功能是：当试图在Depts表中修改数据时将发出警告消息。【解答】 在Students表中添加整型的Height和Weight字段：use mydbgoalter table students add Height int,Weight int 创建一个规则并绑定到Height列，用以限制插入到该列的整数范围：use mydbgocreate rule R1 as range=145 and range=200goexec sp_bindrule rulename=R1, objname=students.Height 创建一个默认值并绑定到Weight列，插入记录时默认值自动填充到该列中：use mydbgocreate default D1 as 50goexec sp_bindefault defname=D1,objname=students.Weight 基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照(引用)完整性约束：/* 假设在mydb库中建表，若表students已存在，创建前先把该表删除 */use mydbcreate table students(SNO char(10) not NULL primary key, /*主键约束*/ NAME varchar(10) not NULL unique, /*惟一性约束*/ SEX char(1) not NULL check(sex=0or sex=1), /*检查约束*/ BDATE datetime, DEPT varchar(10) constraint c1 foreign key(Dept) /*外键约束*/ references Depts(Dno) on delete cascade, /*参照完整性约束*/ DORMITORY varchar(10)注：创建表后，有关验证性的操作，请读者自行完成。 创建一个带SELECT查询语句的存储过程p1，统计出每个系的学生平均身高：use mydbgocreate proc p1as select dept, avg(Height) as avg_h from students group by deptreturn 执行存储过程p1的语句如下：use mydbgoexec p1 创建一个触发器，其功能是当试图在Depts表中修改数据时将发出警告消息：use mydbgocreate trigger t1 on deptsfor updateas raiserror(警告！,10,1)go当使用update语句修改Depts表中数据时，将在消息栏显示“警告！”消息。3.12 假设教学数据库中有三个表，其数据结构如下： 学生表S(学号SNO,姓名SNAME,年龄AGE,性别SEX)； 选修表SC(学号SNO,课程号CNO,成绩GRADE)； 课程表C(课程号CNO,课程名CNAME,任课教师TEACHER)； 试用基本的SELECT语句表达下列操作： 检索选修课程号为C06的学生学号与成绩。 检索选修课程号为C06的学生学号与姓名。 检索选修课程名为ENGLISH的学生学号与姓名。 检索选修课程号为C08或C12的学生学号与成绩。 检索至少选修课程号为C08和C12的学生学号与成绩。 检索没有选修C02号课程的学生姓名与年龄。 检索选修了全部课程的学生姓名。 检索选修课程中包含了学生S05所学课程的学生学号。 求女学生的总人数和平均年龄。 统计选修了课程的学生人数。【解答】 检索选修课程号为C06的学生学号与成绩。use mydbselect sno,gradefrom scwhere cno=c06 检索选修课程号为C06的学生学号与姓名。第1种查询方法连接查询：use mydbSELECT s.sno, snameFROM s,scWHERE s.sno=sc.sno and cno=c06;第2种查询方法嵌套查询：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno= c06 );嵌套查询(使用相关查询)： use mydbSELECT sno, snameFROM sWHERE c06 in ( SELECT cno FROM sc WHERE sno=s.sno);第3种查询方法使用存在量词的嵌套查询：use mydbSELECT sno,snameFROM sWHERE exists (SELECT * FROM sc WHERE s.sno=sc.sno and cno=c06 ); 检索选修课程名为ENGLISH的学生学号与姓名。嵌套查询：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno in ( SELECT cno FROM cWHERE cname= ENGLISH );连接查询：use mydbSELECT s.sno, snameFROM s,c,scWHERE s.sno = sc.sno and o = o and cname = english ; 检索选修课程号为C08或C12的学生学号与成绩。use mydbSELECT *FROM scWHERE cno= C08 or cno= C12 ; 注：这里输出选修表sc的所有列，除学生学号与成绩外，还有选课的课程号。若某个学生同时选修了C08和C12两门课程，可通过选课的课程号予以区分。 检索至少选修课程号为C08和C12的学生学号与成绩。use mydbSELECT A.sno, A.grade, B.gradeFROM sc AS A, sc AS BWHERE A.sno = B.sno and A.cno= C08 and B.cno= C12 ; 检索没有选修C02号课程的学生姓名与年龄。use mydbSELECT sname, ageFROM sWHERE sno not in ( SELECT sno FROM sc WHERE cno in ( SELECT cno FROM c WHERE cno= C02 ) ;若把最外层的WHERE子句由“not in”改为“not exists”，则代码如下：use mydbSELECT sname, ageFROM sWHERE not exists ( SELECT sno FROM sc WHERE s.sno=sc.sno and cno in ( SELECT cno FROM c WHERE cno= C02 ) ; 检索选修了全部课程的学生姓名。use mydbSELECT sno, snameFROM sWHERE not exists ( SELECT * FROM c WHERE not exists ( SELECT * FROM sc WHERE s.sno=sc.sno and o=o ) ; 检索选修课程中包含了学生S05所学课程的学生学号。use mydbSELECT DISTINCT snoFROM scWHERE snoS05 and cno in ( SELECT cno FROM sc WHERE sno= S05 ) ; 求女学生的总人数和平均年龄。use mydbSELECT count(*) as 总人数, avg(age) as 平均年龄FROM sWHERE sex=女 统计选修了课程的学生人数。use mydbselect count(distinct sno) as 选课人数from sc3.13 对习题3.12给出的表，用完整的SELECT语句或使用限定等方式表达下列操作： 统计每一年龄选修课程的学生人数。 求S表中男学生的每一年龄组(超过3人)的人数；查询结果按人数升序排列，若人数相同按年龄降序排列。 检索女学生选修的所有课程号。 检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名: XM,CSNF。 检索1820岁且姓名以字符L打头的学生姓名。 检索至少没有选修C02和C03两门课程的学生学号。 检索选修表SC中平均成绩最高的学生学号。 检索出每门课程的最高分和最低分。 使用COMPUTE子句生成Students表中Weight列的和及平均值。 建立另一个Students1表，求Students表与Students1表的并集、差集、交集。【解答】 统计每一年龄选修课程的学生人数。use mydbselect age as 年龄, count(distinct s.sno) as 人数from s,scwhere s.sno=sc.snogroup by age 求S表中男学生的每一年龄组(超过3人)的人数；查询结果按人数升序排列，若人数相同按年龄降序排列。use mydbselect age as 年龄, count(distinct s.sno) as 人数from s,scwhere s.sno=sc.sno and sex=男group by agehaving count(distinct s.sno)3order by 2, age desc 检索女学生选修的所有课程号。use mydbselect distinct o as 课程号from s,scwhere s.sno=sc.sno and sex=女 检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名:XM,CSNF。use mydbselect sname as XM, year(getdate()-age as CSNFfrom s 检索1820岁且姓名以字符L打头的学生姓名。use mydbselect snamefrom swhere age between 18 and 20 and sname like L% 检索至少没有选修C02和C03两门课程的学生学号。use mydbSELECT snoFROM sWHERE sno not in ( SELECT A.sno FROM sc AS A, sc AS B WHERE A.sno = B.sno and A.cno= C02 and B.cno= C03 ) ; 检索选修表SC中平均成绩最高的学生学号。use mydbselect sno as 学号, m as 最高平均分from (SELECT sno,m=avg(grade) FROM sc GROUP BY sno) as A where m=all (SELECT avg(grade) FROM sc GROUP BY sno) 检索出每门课程的最高分和最低分。use mydbselect cno as 课程号, min(grade) as 最高分, max(grade) as 最低分from scgroup by cno 使用COMPUTE子句生成Students表中Weight列的和及平均值。use mydbselect *from studentscompute sum(weight), avg(weight) 建立另一个Students1表，求Students表与Students1表的并集、差集、交集。use mydb/*并集*/select *from studentsunionselect *from student1/*差集*/select *from studentswhere sno not in (select sno from student1)/*交集*/select students.*from students INNER JOIN student1 ON students.sno=student1.sno ;3.14 对于如下关系模式：雇员表 EMP(雇员编号EID,姓名ENAME,出生年月BDATE,性别SEX,居住城市CITY)；公司表 COMP(公司编号CID,公司名称CNAME,公司所在城市CITY)；工作表 WORKS(雇员编号EID,公司编号CID,加入公司日期STARTDATE，薪酬SALARY)； 试用SQL完成下列操作： 检索出所有为“IBM公司”工作的雇员名字。 检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。 检索出所有居住城市与公司所在城市相同的雇员。 检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。 检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。 检索出工资高于其所在公司雇员平均工资的所有雇员。 检索雇员最多的公司。 为工龄超出10年的雇员加薪10%。 年龄大于60岁的雇员应办理退休手续，删除退休雇员的所有相关记录。“IBM公司”增加某新雇员，将该雇员有关的记录插入到EMP表和WORKS表中，假设新进雇员薪酬未定，暂以空值表示。【解答】 检索出所有为“IBM公司”工作的雇员名字。use mydbselect ENAMEfrom EMPwhere EID in (select EID from WORKS where CID in (select CID from COMP where CNAME= IBM公司 ) 本题的检索可以使用多种不同的形式，例如：use mydbselect ENAMEfrom EMP,WORKS,COMPwhere COMP.CNAME=IBM公司 AND COMP.CID=WORKS.CID AND WORKS.EID=EMP.EID 检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。use mydbselect ENAME, COMP.CNAMEfrom EMP,WORKS,COMPwhere year(getdate()-year(EMP.BDATE)50 AND EMP.SEX=女 AND EMP.EID= WORKS.EID AND WORKS.CID =COMP.CID 检索出所有居住城市与公司所在城市相同的雇员。use mydbselect ENAME,EMP.CITY,COMP.CITYfrom EMP,WORKS,COMPwhere EMP.CITY=COMP.CITY AND EMP.EID= WORKS.EID AND WORKS.CID =COMP.CID 检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。use mydbselect COUNT(EID) AS E#, AVG(SALARY) AS AVG_SAL, MAX(SALARY) AS MAX_SAL, MIN(SALARY) AS MIN_SALfrom WORKS, COMPwhere COMP.CNAME= IBM公司 AND COMP.CID=WORKS.CID 检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。use mydbselect EID, ENAMEfrom EMPwhere EID IN (select EID from WORKS where CID IN (select CID from COMP where CNAME = IBM公司 or CNAME = SAP公司 ) 检索出工资高于其所在公司雇员平均工资的所有雇员。use mydbselect EID as 雇员编号, ENAME as 姓名from EMPwhere EID in (select B.EID from (select CID,M=avg(SALARY) from WORKS group by CID) as A ,WORKS as B where A.CID=B.CID and B.SALARYA.M) 检索雇员最多的公司。use mydbselect CID as 公司编号, CNAME as 公司名称from COMPwhere CID in (select CID from (select CID, M=COUNT(EID) from WORKS group by CID) as A where M=all (select COUNT(EID) from WORKS group by CID ) 为工龄超出10年的雇员加薪10%。use mydbupdate WORKSset SALARY=SALARY*1.1where year(getdate()-year(STARTDATE)10 年龄大于60岁的雇员应办理退休手续，删除退休雇员的所有相关记录。use mydbdelete EMPwhere year(getdate()-year(BDATE)60“IBM公司”增加某新雇员，将该雇员有关的记录插入到EMP表和WORKS表中，假设新进雇员薪酬未定，暂以空值表示。use mydbinsert into EMP(EID,ENAME,BDATE,SEX,CITY) values(E07,andy,1970-3-8,男,广州)goinsert into WORKS(EID,CID,STARTDATE,SALARY) values(E07,C01, getdate(),null)3.16 根据习题3.14给出的关系模式，创建一个视图，按照公司顺序来显示其所有雇员的有关信息。【解答】use mydbgocreate view C_E_VIEWasselect COMP.CID,CNAME,CITY,EID,STARTDATE,SALARYfrom COMP left join WORKS on COMP.CID=WORKS.CIDgoselect *from C_E_VIEW3.19 对习题3.12给出的三个表，试用T-SQL更新语句表达下列更新操作： 在S表中插入一行：(S06,WANG,20 )。 在S表中检索出每一门成绩都大于等于85分的学生学号、姓名和性别，并把检索结果存入一个已存在的表STUDENT(SNO,SNAME,SEX)中。 删除SC表中尚没有成绩的所有行。 把姓名为LILI的学生的所有成绩删去。 把选修ENGLISH课程的不及格成绩(60)全改为空值。 把低于MATHS课平均成绩的外语系女生的MATHS成绩提高5%。 修改SC表中C09课程的成绩，若成绩小于等于70分则提高4%,若成绩大于70分则提高3%。 使用INSTEAD OF触发器，在SC表中插入一行(CHEN)。注: 设CNO为C03【解答】 在S表中插入一行：(S06,WANG,20 )。use mydbinsert into S(SNO,SNAME,AGE) values(S06,WANG,20) 在S表中检索出每一门成绩都大于等于85分的学生学号、姓名和性别，并把检索结果存入一个已存在的表STUDENT(SNO,SNAME,SEX)中。use mydbinsert into STUDENT(SNO,SNAME,SEX)select distinct S.SNO,SNAME,SEXfrom S inner join SC on S.SNO=SC.SNOwhere 85all ( select GRADE from SC where S.SNO=SNO ) 删除SC表中尚没有成绩的所有行。use mydbdelete from SCwhere GRADE is null 把姓名为LILI的学生的所有成绩删去。use mydbupdate SCset GRADE=nullwhere SNO in ( select SNO from S where SNAME=LILI) 把选修ENGLISH课程的不及格成绩(60)全改为空值。use mydbupdate SCset GRADE=nullwhere GRADE60 and CNO in ( select CNO from C where CNAME=ENG