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

3-3 习题3

在SQL Server中，创建一个名为students且包含有下列几个属性的表。

SNO char(10)；

NAME varchar(10)；

SEX char(1)；

BDATE datetime；

DEPT varchar(10)；

DORMITORY varchar(10).

要求：1.采用两种形式创建表，即用SQL语句和用图形界面的形式来创建。

2.定义必要的约束，包括主键SNO,NAME值不允许为空，且SEX取值为0或1。【解答】

·进入SQL查询分析器建立查询，创建students表的SQL语句如下，操作如图所示。

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)

)

图用SQL语句创建students表

·进入企业管理器用基本操作创建students表。用右键单击“mydb”数据库，从弹出的菜单中选择“新建”,再从其下一级菜单中选择“表”。或者，用右键单击“mydb”数据库下一级的“表”，从弹出的菜单中选择“新建表”。然后，在弹出的窗体中，把students表所包含的字段逐一输入，每个字段都要指明列名、数据类型、长度和是否允许空值、是否主键等内容，如图所示。

图用基本操作创建students表

其中，SEX字段取值为0或1，需要建立约束。操作是用右键单击SEX字段，从弹出的菜单中选择“CHECK约束”，再从弹出的“属性”窗体中，选择“CHECK约束”卡，在约束表达式框中输入约束表达式，如图所示。

图输入约束表达式

最后，单击“保存”图标，SQL Server将弹出一个“选择名称”对话框，输入表名“students”，单击“确定”按钮，新建的students表结构将被保存起来。

在Access中，完成习题的要求。

【解答】

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表中将DEPT设置为外键，引用Depts表中的DNO列值。若某系还有学生时，不得在Depts表中删除该系的记录。

use mydb

create 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表的基础上，建立一个显示所有计算机系学生的视图，假设计算机系的代号为‘CS’。

use mydb

go

create view student_cs_view /*SQL SERVER约定：本语句必须为批处理的第一个语句*/

as

select *

from students

where ='cs'

在SQL Server中完成下列操作：

①用修改表结构语句在Students表中添加整型的Height和Weight字段。

②创建一个规则并绑定到Height列，用以限制插入到该列的整数范围。

③创建一个默认值并绑定到Weight列，插入记录时，默认值自动填充到该列中。

④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照(引用)完整性约束，并辅以其他操作予以验证。

⑤创建并执行一个带SELECT查询语句的存储过程,统计出每个系的学生平均身高。

⑥创建一个触发器，其功能是：当试图在Depts表中修改数据时将发出警告消息。【解答】

①在Students表中添加整型的Height和Weight字段：

use mydb

go

alter table students add Height int,Weight int

②创建一个规则并绑定到Height列，用以限制插入到该列的整数范围：

use mydb

go

create rule R1 as @range>=145 and @range<=200

go

exec sp_bindrule @rulename='R1', @objname=''

③创建一个默认值并绑定到Weight列，插入记录时默认值自动填充到该列中：

use mydb

go

create default D1 as 50

go

exec sp_bindefault @defname='D1',@objname=''

④基于Students表与Depts表，分别建立惟一性约束、检查约束、主键约束、外键约束和参照(引用)完整性约束：

/* 假设在mydb库中建表，若表students已存在，创建前先把该表删除 */

use mydb

create table students

(SNO char(10) not NULL primary key, /*主键约束*/

NAME varchar(10) not NULL unique, /*惟一性约束*/

SEX char(1) not NULL check(sex='0'or sex='1'), /*检查约束*/

BDATE datetime,

DEPT varchar(10) constraint c1 foreign key(Dept) /*外键约束*/

references Depts(Dno) on delete cascade, /*参照完整性约束*/

DORMITORY varchar(10)

)

注：创建表后，有关验证性的操作，请读者自行完成。

⑤创建一个带SELECT查询语句的存储过程p1，统计出每个系的学生平均身高：

use mydb

go

create proc p1

as

select dept, avg(Height) as avg_h

from students

group by dept

return

执行存储过程p1的语句如下：

use mydb

go

exec p1

⑥创建一个触发器，其功能是当试图在Depts表中修改数据时将发出警告消息：

use mydb

go

create trigger t1 on depts

for update

as

raiserror('警告！',10,1)

go

当使用update语句修改Depts表中数据时，将在消息栏显示“警告！”消息。

假设教学数据库中有三个表，其数据结构如下：

学生表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 mydb

select sno,grade

from sc

where cno='c06'

②检索选修课程号为C06的学生学号与姓名。

·第1种查询方法——连接查询：

use mydb

SELECT , sname

FROM s,sc

WHERE = and cno='c06';

·第2种查询方法——嵌套查询：

use mydb

SELECT sno, sname

FROM s

WHERE sno in

( SELECT sno

FROM sc

WHERE cno= 'c06' );

·嵌套查询(使用相关查询)：

use mydb

SELECT sno, sname

FROM s

WHERE 'c06' in

( SELECT cno

FROM sc

WHERE sno=;

·第3种查询方法——使用存在量词的嵌套查询：use mydb

SELECT sno,sname

FROM s

WHERE exists (SELECT *

FROM sc

WHERE = and cno='c06' );

③检索选修课程名为ENGLISH的学生学号与姓名。

·嵌套查询：

use mydb

SELECT sno, sname

FROM s

WHERE sno in

( SELECT sno

FROM sc

WHERE cno in

( SELECT cno

FROM c

WHERE cname= 'ENGLISH' ));

·连接查询：

use mydb

SELECT , sname

FROM s,c,sc

WHERE = and = and cname = 'english' ;

④检索选修课程号为C08或C12的学生学号与成绩。

use mydb

SELECT *

FROM sc

WHERE cno= 'C08' or cno= 'C12' ;

注：这里输出选修表sc的所有列，除学生学号与成绩外，还有选课的课程号。若某个学生同时选修了C08和C12两门课程，可通过选课的课程号予以区分。

⑤检索至少选修课程号为C08和C12的学生学号与成绩。

use mydb

SELECT , ,

FROM sc AS A, sc AS B

WHERE = and = 'C08' and = 'C12' ;

⑥检索没有选修C02号课程的学生姓名与年龄。

use mydb

SELECT sname, age

FROM s

WHERE sno not in

( SELECT sno

FROM sc

WHERE cno in

( SELECT cno

FROM c

WHERE cno= 'C02' )) ;

若把最外层的WHERE子句由“not in”改为“not exists”，则代码如下：

use mydb

SELECT sname, age

FROM s

WHERE not exists

( SELECT sno

FROM sc

WHERE = and cno in

( SELECT cno

FROM c

WHERE cno= 'C02' )) ;

⑦检索选修了全部课程的学生姓名。

use mydb

SELECT sno, sname

FROM s

WHERE not exists

( SELECT *

FROM c

WHERE not exists

( SELECT *

FROM sc

WHERE =

and = )) ;

⑧检索选修课程中包含了学生S05所学课程的学生学号。

use mydb

SELECT DISTINCT sno

FROM sc

WHERE sno<>'S05' and cno in

( SELECT cno

FROM sc

WHERE sno= 'S05' ) ;

⑨求女学生的总人数和平均年龄。

use mydb

SELECT count(*) as 总人数, avg(age) as 平均年龄

FROM s

WHERE sex='女'

⑩统计选修了课程的学生人数。

use mydb

select count(distinct sno) as 选课人数

from sc

对习题给出的表，用完整的SELECT语句或使用限定等方式表达下列操作：

①统计每一年龄选修课程的学生人数。

②求S表中男学生的每一年龄组(超过3人)的人数；查询结果按人数升序排列，

若人数相同按年龄降序排列。

③检索女学生选修的所有课程号。

④检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名: XM,CSNF。

⑤检索18～20岁且姓名以字符L打头的学生姓名。

⑥检索至少没有选修C02和C03两门课程的学生学号。

⑦检索选修表SC中平均成绩最高的学生学号。

⑧检索出每门课程的最高分和最低分。

⑨使用COMPUTE子句生成Students表中Weight列的和及平均值。

⑩建立另一个Students1表，求Students表与Students1表的并集、差集、交集。

【解答】

①统计每一年龄选修课程的学生人数。

use mydb

select age as 年龄, count(distinct as 人数

from s,sc

where =

group by age

②求S表中男学生的每一年龄组(超过3人)的人数；查询结果按人数升序排列，

若人数相同按年龄降序排列。

use mydb

select age as 年龄, count(distinct as 人数

from s,sc

where = and sex='男'

group by age

having count(distinct >3

order by 2, age desc

③检索女学生选修的所有课程号。

use mydb

select distinct as 课程号

from s,sc

where = and sex='女'

④检索每个学生的出生年份,输出学生姓名和出生年份分别用新列名:XM,CSNF。

use mydb

select sname as XM, year(getdate())-age as CSNF

from s

⑤检索18～20岁且姓名以字符L打头的学生姓名。

use mydb

select sname

from s

where age between 18 and 20 and sname like 'L%'

⑥检索至少没有选修C02和C03两门课程的学生学号。

use mydb

SELECT sno

FROM s

WHERE sno not in

( SELECT

FROM sc AS A, sc AS B

WHERE = and = 'C02' and = 'C03' ) ;

⑦检索选修表SC中平均成绩最高的学生学号。

use mydb

select 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 mydb

select cno as 课程号, min(grade) as 最高分, max(grade) as 最低分

from sc

group by cno

⑨使用COMPUTE子句生成Students表中Weight列的和及平均值。

use mydb

select *

from students

compute sum(weight), avg(weight)

⑩建立另一个Students1表，求Students表与Students1表的并集、差集、交集。

use mydb

/*并集*/

select *

from students

union

select *

from student1

/*差集*/

select *

from students

where sno not in

(select sno

from student1)

/*交集*/

select students.*

from students INNER JOIN student1 ON = ;

对于如下关系模式：

雇员表 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 mydb

select ENAME

from EMP

where EID in

(select EID

from WORKS

where CID in

(select CID

from COMP

where CNAME= 'IBM公司' ))

本题的检索可以使用多种不同的形式，例如：

use mydb

select ENAME

from EMP,WORKS,COMP

where ='IBM公司' AND

= AND

=

②检索出所有年龄超过50岁的女性雇员的姓名和所在公司的名称。

use mydb

select ENAME,

from EMP,WORKS,COMP

where year(getdate())-year>50 AND

='女' AND

= AND

=

③检索出所有居住城市与公司所在城市相同的雇员。

use mydb

select ENAME,,

from EMP,WORKS,COMP

where = AND

= AND

=

④检索出“IBM公司”雇员的人数，平均工资，最高工资和最低工资，并且分别

用E#，AVG_SAL，MAX_SAL，MIN_SAL作为列标题。

use mydb

select COUNT(EID) AS E#, AVG(SALARY) AS AVG_SAL,

MAX(SALARY) AS MAX_SAL, MIN(SALARY) AS MIN_SAL

from WORKS, COMP

where = 'IBM公司' AND =

⑤检索同时在“IBM公司”和“SAP公司”兼职的雇员名字。

use mydb

select EID, ENAME

from EMP

where EID IN

(select EID

from WORKS

where CID IN

(select CID

from COMP

where CNAME = 'IBM公司' or CNAME = 'SAP公司' ))

⑥检索出工资高于其所在公司雇员平均工资的所有雇员。

use mydb

select EID as 雇员编号, ENAME as 姓名

from EMP

where EID in

(select

from (select CID,M=avg(SALARY) from WORKS group by CID) as A ,WORKS as B where = and >

⑦检索雇员最多的公司。

use mydb

select CID as 公司编号, CNAME as 公司名称

from COMP