综合题及答案

3、假设在“人事管理”数据中有三个表。结构如下：

（1） （2） （3） （4） （5） （6）

编写创建数据库语句。数据库名为“人事管理”，其所有参数都取默认值。 编写在“人事管理”的数据库中创建T1、T2表的语句。

编写一条insert语句，向student表中插入一条记录，数据如左表：

编写一条Select语句，查询选修课成绩大于等于60分的考试信息。考试包括姓名、选修课名、成绩三个字段。 编写一个触发器del_tri，当用户删除student表中一条记录时，自动删除T1，T2表中该学生的记录。

编写一个存储过程proc1，查询指定姓名的信息。学生信息包括学号、姓名、选修课名、出生日期三个字段。

五、T-SQL填空题

1、问题描述：下面为权限管理创建了3个表： ? ? ?

权限表（权限代号，权限系统编号，名称）：其中权限代号为主键，权限系统编号为唯一索引。

用户组表（组代号，组系统编号，组名称）：其中组代号为主键，组系统编号为唯一索引。

权限分配表（权限系统编号，组系统编号，分配与否）：其中权限系统编号及

组系统编号为联合主键。分配与否为char(1),其中表示方式（0表示未分配，1表示分配） 下面编写了一个存储过程实现权限批量分配存储过程。

Create 1 SP_QXFP @ZXTBH varchar(10) , @FP 2 As Insert into 权限分配表（权限系统编号，组系统编号， 3 ）

Select 4 , @ZXTBH , @FP from 权限表；

在查询分析器中调用存储过程： 5 SP_QXFP ?DBO? , ?1? 2、问题描述：下面为学生成绩管理建立了3个表:

学生信息表S(S#,SN,SD,SA) S#、SN、SD、SA分别代表学号、学员姓名、所属班级、学员入学日期，其中S#为主键。 科目表C(C#,CN) C#、CN分别代表课程编号、课程名称，其中C#为主键

成绩表SC(S#,C#,G) S#、C#、G分别代表学号、所选修的课程编号、学习成绩，其中S#，C#为联合主键。 (1) (2) (3) (4) (5) (9)

利用SQL语句创建出上面3个表。 利用SQL语句为SC表添加上外键约束。 使用标准SQL嵌套语句查询选修课程名称为?数据库原理?的学员学号和姓名。 查询选修课程超过5门的学员信息。 为C表中CN添加唯一索引。

定义一个S表的更新触发器，实现当S中S# 改变时，能够修改SC中的学号。 Create 1 TR_updateS on S For 2 As

Declare @oldS# varchar(10), @ news# varchar(10) Update SC Set S#= 4 Where S#= 5 3、设有一个顾客商品关系数据库，有三个基本表，表的结构如下： 商品表：Produt（商品号,商品名,单价,库存量） 客户表：Customer（顾客号,顾客名,性别,年龄,电话） 订单表：OrderItem（顾客号,商品号,数量,购买价,日期） (1)

用SQL语句创建一个视图GM_VIEW，检索顾客的顾客号、顾客名和订购商品的商品名、金额和日期。（金额=数量*购买价） CREATE VIEW GM_VIEW AS

(2)

SELECT___________________

FROM Product, Customer, OrderItem WHERE_______________;

用SQL语言检索一次订购的商品数量最多的顾

Select @oldS#= Deleted.S#，@newS#= Inserted.S# From Deleted, Inserted Where 3

(6) 检索出成绩小于60的学生信息。

(7) 查询出1门课有2个以上学生不及格的课程。 (8) 如果成绩G字段数据类型为整型，从性能上分析，执行

where G>59 和 where G>=60 两种查询条件有无区别？为什么？

客名。

SELECT 顾客名

FROM Customer, OrderItem

WHERE__________=(SELECT________FROM OrderItem)

(3)

用SQL语句检索订购4种商品以上的每个顾客的顾客号，顾客名和订购的商品金额合计，并按金额合计进行降序排序。（金额=数量*购买价）

参考答案：

一、是非题。错误的有： 1

4

6

7

9

10

11

14

16

17

18

19

21

22

23 (4)

SELECT______________________ FROM Customer, OrderItem

WHERE Customer.顾客号=OrderItem.顾客号 GROUP BY Customer .顾客号，顾客名__________; ORDER BY

请用SQL语句ALTER TABIE 命令给商品表Product增加一个字段，字段名为：产地，数据类型为：CHAR，长度为：30，命令如下：ALTER TABLE________________

24 25 31 33 34 36 37

38

二、填空题

1、 安全 10、 select set 2、 DBMS

11、 begin end 3、 多对多或M：N 12、 break continue 4、 批

13、 msdb 5、 @ @@ 14、 关系数据型 6、 T-SQL 15、 操作系统文件 7、 GO

16、 更新

检查

8、 常规标识符和分隔标

17、 空(或 NULL) 识符 18、 数据的完整性检查 9、 查询窗格 19、 alter database

drop database

三、选择题

1、 D 6、 C 11、 C 16、 B 2、 B 7、 A 12、 D 17、 D3、 B 8、 D 13、 A 18、 D4、 A 9、 B 14、 B 19、 C5、 A 10、 C

15、 A

20、 D四、编写T-SQL语句。

1、解答：

create database student on primary

( name=studentdata , filename=?c:\\date\\student_data.mdf ?, size=10, maxsize=50, filegroth=25%) log on

( name=studentlog , filename=? c:\\date\\student_log.ldf ?, size=10, maxsize=unlimited, filegroth=2) 2、解答：（1）create database market

go

（2）use market

create table goods

( 货品名称 char(20) primary key , 单价 money not null , 库存量 int not null ) create table orders

( 订单号 int not null identity (1,1) , 货品名称 char(20) null, 数量 int not null

20、 主键、外键、惟一性、

28、 fillfactor 默认值、检查、空值约29、 聚集和非聚集 束 30、 主键和惟一性 21、 域完整性 31、 检索 或 查询 22、 修改、插入 32、 insert、delete、update 23、 检查约束 33、 create trigger 24、 where 34、 登录名和密码 25、 视图 35、 备份

26、 distinct 27、 having

21、 D 26、 C 31、 B 36、 B 22、 D 27、 B 32、 D 37、 B 23、 A 28、 C 33、 D 38、 D 24、 B 29、 D 34、 A 39、 C

25、 D

30、 C

35、 B

constraint pk_orders primary key (订单号) constraint ck_orders check(数量>0) constraint fk_orders

foreign key (货品名称) references goods(货品名称) ) （3）create trigger ins_tri on orders for insert

as declare @订购量 int , @库存量 int

select @订购量=inserted.数量 , @库存量=库存量 from inserted, goods

where inserted.货品名称=goods.货品名称 if @订购量>@库存量 rollback transaction

else

update goods

set 库存量=库存量-@订购量

where 货品名称 like ? %计算机%? （4）select * from orders

where 货品名称 like ? %计算机%? （5）create procedure proc1

@货品名称 char(20) as

select 订单号，orders.货品名称，数量*库存量 as 金额 from orders, goods

where orders.货品名称=goods.货品名称 and orders.货品

(学号 int not null ,

选修课程 char(20) not null , 成绩 tinyint null )

名称=@货品名称

3、解答：（1）create database 人事管理

go

（2）use 人事管理

--创建student表，在每列定义后创建约束 create table student (学号 int primary key , 姓名 char(10) not null ,

出生日期 datatime )

--创建t1表，在所有列定义后创建约束 create table t1 (学号 int not null , 语文 tinyint null, 数学 tinyint null

constraint pk_t1 primary key (学号) ) --先建t2表，后添加约束 create table t2 五、T-SQL填空题 1、解答：①proc

②char(1) ③分配与否

④权限系统编号

⑤exec

2、解答：（1）create table S

(S# varchar(10) not null primary key , SN varchar(30) not null , SA datetime ) Create table C

(C# varchar(10) not null primary key , CN varchar(60) not null ) Create table SC (S# varchar(10) not null , C# varchar(10) not null , G decimal(3,0) )

alter table SC add primary key (S#,C#) （2）Alter table SC

add constraint fk_sc_s foreign key(S#) references S(S#) Alter table SC

add constraint fk_sc_c foreign key(C#) references S(C#) （3）SELECT S#,SN FROM S

WHERE S# IN ( SELECT S# FROM C,SC

WHERE C.C#=SC.C# AND CN='数据库原理' )

（4）查询选修课程超过5门的学员信息

SELECT * FROM S

WHERE S# IN (SELECT S# FROM SC

GROUP BY S#

HAVING COUNT(DISTINCT C# )>5 )

（5）alter table C

add constraint ix_C unique nonclustered (CN)

alter table t2 add primary key (学号,选修课程) alter table t2 add check(成绩>=0 and 成绩<=100) （3）insert into student valuse(1,?李红?,?1980-2023?) （4）select 姓名,选修课程,成绩 from student, t2

where 成绩>=60 and student.学号=t2.学号 （5）create trigger del_tri on student for delete as declare @学号 int

select @学号=deleted.学号 from deleted print ?正准备删除t1和t2表中相应学号……? delete t1 where 学号=@学号 delete t2 where 学号=@学号 （6）create procedure proc1

@姓名 char(10) as

select student.学号，姓名，选修课程，出生日期

form student , t2

where student.学号=t2.学号 and 姓名=@姓名

（6）Select * from S

Where S# in (select S# from SC where G<60) （7）Select * from C

Where exists (Select count(C#) from SC

Group by C# Having count(C#) >=2 )

（8）有区别，虽然G>59和G>=60得到的结论都是一样，查询出成绩及格的信息。但是，大于59在查询过程中是以59为条件比较，再找其大于的记录，搜索的记录要多一些。等于60以60为基数搜索，故性能要好一些。

（9）①trigger ②update ③deleted.s#=inserted.s#

④@news#

⑤@olds#

3、解答：

（1）select customer.顾客号,顾客名,商品名,数量*购买价 as 金额,日期 from Product, Customer, Orderltem

where product.商品号=orderltem.商品号 and

customer.顾客号=orderltem.顾客号

（2）select 顾客名 from Customer

where customer.顾客号=orderitem.顾客号 and 数量 =(select max(数量) from OrderItem )

（3）select customer.顾客号,顾客名,数量*购买价 as 金额

from Customer, OrderItem

where Customer.顾客号=OrderItem.顾客号 group by Customer .顾客号，顾客名 having count( 商品号) >4 order by 3 desc （4）alter table product add 产地 char(30)