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

1、数据库第三章课后习题解答3-3 习题3 在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语句如下，操作如图所示。use mydb /* 假设在mydb库中建表

2、*/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)图 用SQL语句创建students表进入企业管理器用基本操作创建students表。用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。或者，用右键单击“mydb”数据库下一级的“表”，从弹出

3、的菜单中选择“新建表”。然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图所示。图 用基本操作创建students表其中，SEX字段取值为0或1，需要建立约束。操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图所示。图 输入约束表达式最后，单击“保存”图标，SQL Server将弹出一个“选择名称”对话框，输入表名“students”，单击“确定”按钮，新建的students表结构将被保存起来。 在Acce

4、ss中，完成习题的要求。【解答】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字段建立有效性规则。 在SQL Server中，创建表Depts（DNO,DNAME,MGR）。用SQL语句在习题中创建的Students表中

5、将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 cascade 什么是视图视图的作用是什么在习题所创建的Students表的基础上，建立一个显示所有计算机

6、系学生的视图，假设计算机系的代号为CS。use mydbgocreate view student_cs_view /*SQL SERVER约定：本语句必须为批处理的第一个语句*/asselect *from studentswhere =cs在SQL Server中完成下列操作： 用修改表结构语句在Students表中添加整型的Height和Weight字段。 创建一个规则并绑定到Height列，用以限制插入到该列的整数范围。 创建一个默认值并绑定到Weight列，插入记录时，默认值自动填充到该列中。 基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参

7、照(引用)完整性约束，并辅以其他操作予以验证。 创建并执行一个带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 rul

8、ename=R1, objname= 创建一个默认值并绑定到Weight列，插入记录时默认值自动填充到该列中：use mydbgocreate default D1 as 50goexec sp_bindefault defname=D1,objname= 基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照(引用)完整性约束：/* 假设在mydb库中建表，若表students已存在，创建前先把该表删除 */use mydbcreate table students(SNO char(10) not NULL primary key, /*主键约束*/

9、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，统计出每个系的学

10、生平均身高：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表中数据时，将在消息栏显示“警告！”消息。 假设教学数据库中有三个表，其数据结构如下：

11、 学生表S(学号SNO,姓名SNAME,年龄AGE,性别SEX)； 选修表SC(学号SNO,课程号CNO,成绩GRADE)； 课程表C(课程号CNO,课程名CNAME,任课教师TEACHER)； 试用基本的SELECT语句表达下列操作： 检索选修课程号为C06的学生学号与成绩。 检索选修课程号为C06的学生学号与姓名。 检索选修课程名为ENGLISH的学生学号与姓名。 检索选修课程号为C08或C12的学生学号与成绩。 检索至少选修课程号为C08和C12的学生学号与成绩。 检索没有选修C02号课程的学生姓名与年龄。 检索选修了全部课程的学生姓名。 检索选修课程中包含了学生S05所学课程的学生学号

12、。 求女学生的总人数和平均年龄。 统计选修了课程的学生人数。【解答】 检索选修课程号为C06的学生学号与成绩。use mydbselect sno,gradefrom scwhere cno=c06 检索选修课程号为C06的学生学号与姓名。第1种查询方法连接查询：use mydbSELECT , snameFROM s,scWHERE = and cno=c06;第2种查询方法嵌套查询：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno= c06 );嵌套查询(使用相关查询)： use mydbS

13、ELECT sno, snameFROM sWHERE c06 in ( SELECT cno FROM sc WHERE sno=;第3种查询方法使用存在量词的嵌套查询：use mydbSELECT sno,snameFROM sWHERE exists (SELECT * FROM sc WHERE = and cno=c06 ); 检索选修课程名为ENGLISH的学生学号与姓名。嵌套查询：use mydbSELECT sno, snameFROM sWHERE sno in ( SELECT sno FROM sc WHERE cno in ( SELECT cno FROM cWHER

14、E cname= ENGLISH );连接查询：use mydbSELECT , snameFROM s,c,scWHERE = and = and cname = english ; 检索选修课程号为C08或C12的学生学号与成绩。use mydbSELECT *FROM scWHERE cno= C08 or cno= C12 ; 注：这里输出选修表sc的所有列，除学生学号与成绩外，还有选课的课程号。若某个学生同时选修了C08和C12两门课程，可通过选课的课程号予以区分。 检索至少选修课程号为C08和C12的学生学号与成绩。use mydbSELECT , , FROM sc AS A,

15、sc AS BWHERE = and = C08 and = 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 = an

16、d 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 = and = ) ; 检索选修课程中包含了学生S05所学课程的学生学号。use mydbSELECT DISTINCT snoFROM scWHERE snoS05 and cno in ( SELECT cno FROM sc WHERE sno= S0

17、5 ) ; 求女学生的总人数和平均年龄。use mydbSELECT count(*) as 总人数, avg(age) as 平均年龄FROM sWHERE sex=女 统计选修了课程的学生人数。use mydbselect count(distinct sno) as 选课人数from sc 对习题给出的表，用完整的SELECT语句或使用限定等方式表达下列操作： 统计每一年龄选修课程的学生人数。 求S表中男学生的每一年龄组(超过3人)的人数；查询结果按人数升序排列，若人数相同按年龄降序排列。 检索女学生选修的所有课程号。 检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名: XM,

18、CSNF。 检索1820岁且姓名以字符L打头的学生姓名。 检索至少没有选修C02和C03两门课程的学生学号。 检索选修表SC中平均成绩最高的学生学号。 检索出每门课程的最高分和最低分。 使用COMPUTE子句生成Students表中Weight列的和及平均值。 建立另一个Students1表，求Students表与Students1表的并集、差集、交集。【解答】 统计每一年龄选修课程的学生人数。use mydbselect age as 年龄, count(distinct as 人数from s,scwhere =group by age 求S表中男学生的每一年龄组(超过3人)的人数；查询结

19、果按人数升序排列，若人数相同按年龄降序排列。use mydbselect age as 年龄, count(distinct as 人数from s,scwhere = and sex=男group by agehaving count(distinct 3order by 2, age desc 检索女学生选修的所有课程号。use mydbselect distinct as 课程号from s,scwhere = and sex=女 检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名:XM,CSNF。use mydbselect sname as XM, year(getdate(

20、)-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 FROM sc AS A, sc AS B WHERE = and = C02 and = C03 ) ; 检索选修表SC中平均成绩最高的学生学号。use mydbselect sno as 学号, m as 最高平均分f

21、rom (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(w

22、eight) 建立另一个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 = ; 对于如下关系模式：雇员表 EMP(雇员编号EID,姓名ENAME,出生年月BDATE,性别SEX,居住

23、城市CITY)；公司表 COMP(公司编号CID,公司名称CNAME,公司所在城市CITY)；工作表 WORKS(雇员编号EID,公司编号CID,加入公司日期STARTDATE，薪酬SALARY)； 试用SQL完成下列操作： 检索出所有为“IBM公司”工作的雇员名字。 检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。 检索出所有居住城市与公司所在城市相同的雇员。 检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。 检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。 检索出工资高于其所在公司雇员

24、平均工资的所有雇员。 检索雇员最多的公司。 为工龄超出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 m

25、ydbselect ENAMEfrom EMP,WORKS,COMPwhere =IBM公司 AND = AND = 检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。use mydbselect ENAME, from EMP,WORKS,COMPwhere year(getdate()-year50 AND =女 AND = AND = 检索出所有居住城市与公司所在城市相同的雇员。use mydbselect ENAME,from EMP,WORKS,COMPwhere = AND = AND = 检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别用E#，AVG

26、_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 = IBM公司 AND = 检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。use mydbselect EID, ENAMEfrom EMPwhere EID IN (select EID from WORKS where CID IN (select CID from COMP whe

27、re CNAME = IBM公司 or CNAME = SAP公司 ) 检索出工资高于其所在公司雇员平均工资的所有雇员。use mydbselect EID as 雇员编号, ENAME as 姓名from EMPwhere EID in (select from (select CID,M=avg(SALARY) from WORKS group by CID) as A ,WORKS as B where = and 检索雇员最多的公司。use mydbselect CID as 公司编号, CNAME as 公司名称from COMPwhere CID in (select CID fr

28、om (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*where year(getdate()-year(STARTDATE)10 年龄大于60岁的雇员应办理退休手续，删除退休雇员的所有相关记录。use mydbdelete EMPwhere year(getdate()-year(BDATE)60“IBM公司

29、”增加某新雇员，将该雇员有关的记录插入到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) 根据习题给出的关系模式，创建一个视图，按照公司顺序来显示其所有雇员的有关信息。【解答】use mydbgocreate view C_E_VIEWasselect ,CNAME,CITY,EID,STARTDATE,SALARYfrom COMP left join WORKS on =goselect *from C_E_VIEW 对习题给出的三个表，试用T-SQL更新语句表达下列更新操作： 在S表中插入一行：(S06,WANG,20 )。 在S表中检索出每一门成绩