四、编程题（每小题 1 分，共 15 分）
可能用到的表结构如下：
学生表（学号，姓名，性别，年龄，班级）
课程表（课程号，课程名，学分）
成绩表（学号，课程号，成绩）
1.创建存储过程 GetStudentByID，带有参数 sno，根据学生的学号查询学生情况 Create procedure GetStudendByID sno varchar(10) As Begin Select * from 学生表 where 学号=sno end
2. 编写 SQL 语句，查询学生表中所有学生，要求第一按照班级升序排序，第二按照年龄降序排序 select * from 学生表 order by 班级 asc,年龄 desc
3. 编写 SQL 语句，查询学生表中姓张的学生 select * from 学生表 where 姓名 like "张%"
4. 编写 SQL 语句，查询学生表中男女生的人数各多少人 select 性别,count(*) from 学生表 group by 性别
5. 编写 SQL 语句，查询所有比“王平”年龄大的情况 Select * from 学生表 where 年龄>(select 年龄 from 学生表 where 姓名=’王平’) 6. 编写 SQL 语句，在学生表和成绩表中查询学号，姓名，课程号，成绩 SELECT 学生表.学号, 学生表.姓名, 成绩表.课程号, 成绩表.成绩 FROM 学生表 INNER JOIN 成绩表 ON 学生表.学号 = 成绩表.学号
7. 编写 SQL 语句，将（1022，张望，男，19，信息 2）的学生插入到学生表中 insert into 学生表 (学号,姓名,性别,年龄, 班级) values("1022","张望","男",19,"信息 2")
8. 编写 SQL 语句，将学号为 1022 学生的姓名改为张旺 update 学生表 set 姓名="张旺" where 学号="1022"
9. 编写 SQL 语句，删除没有选课的学生 delete from 学生表 sno not in (select sno from sc)
10. 编写 SQL 语句，查询课程号 9 的先行课的情况 select * from 课程表 where 课程号= (select 先行课号 from 课程表 where 课程号=9)
11. 编写关系代数，查询选修 3 号课程的学生学号 ∏学号（∫课程号=‘3’（选课表））
12. 编写关系代数，查询至少选修了一门直接先行课为 5 号课程的学生姓名 参看课本 60 页 13. 编写 SQL 语句，创建学生表 Create table 学生表 (学号 char（10），姓名 varchar(20)，性别 char(2),年龄 int,班级 char(30)) 14. 编写 SQL 语句，建立计算机 1 班男生的视图 Create view as select * from 学生表 where 班级=‘计算机 1 班’ and 性别=‘男’ 15. 编写 SQL 语句，将对学生表的修改权限赋给用户 U1 Grant update on table 学生表 to U1 1.设有一个学生课程数据库，包括学生关系 Student、课程关系 Course、选修关系 SC，图 1 所示：
Student 学号 Sno 姓名 Sname 性别 Ssex 年龄 Sage 所在系 Sdept 95001 李勇 男 20 CS 95002 刘晨 女 19 IS 95003 王敏 女 18 MA 95004 张立 男 19 IS Course 课程号 Cno 课程名 Cname 先行课 Cpno 学分 Ccredit 1 数据库 5 4 2 数学
2 3 信息系统 1 4 4 操作系统 6 3
5 数据结构 7 4 6 数据处理
2 7 PASCAL 语言 6 4
SC 学号 Sno 课程号 Cno 成绩 Grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 图 1
学生-课程数据库 写出完成下列要求的 SQL 语句：
1)查询所有年龄在 20 岁以下的学生姓名与年龄。
1.
select Sname,Sage
from Student
where Sage<20;
或 select Sname,Sage
from Student
where not Sage>=20; 2)查询年龄不在 20～23(包括 20 岁和 23 岁)之间的学生的姓名、系别和年龄。
select Sname,Sdept,Sage
from Student where Sage not between 20 and 23; 3)查询每个学生及其选修课程的情况。
select Student.*,SC.*
from Student,SC
where Student.Sno=SC.Sno; 2.把下面用关系表示的实体、实体与实体之间的联系，用 E-R 图表示出来，要求在图中表示联系的类型(1∶1、 1∶n、 m∶n)。
实体 1：学生(学号，姓名，性别，年龄)
关键字为：学号 实体 2：课程(课程号，课程名，学分数)
关键字为：课程号 实体 1 与实体 2 的联系：学习(学号，课程号，成绩)
关键字为：学号+课程号 注：一个学生可以选多门课程，一门课程也可以被多个学生选，学生选课后有成绩。
学生选课关系 E-R 图
五、编码题 1、下面给出三个关系模式：
（1）学生关系 S（SNO，SName，SAge，SSex）
（2）课程关系 C（CNO，CName，CTeacher）
（3）学习关系 SC（SNO，CNO，Grade）
根据要求用 T-SQL 完成下列各题。
（1）
检索学习课程号为“C01001”的学生学号与姓名； （2）
检索至少选修课程号为“C00101”和“C02341”的学生学号。
答：（1）SELECT S.S#,SNAME
FROM S,SC
WHERE S.S#=SC.S#
AND
C#=’C01001’ （2）SELECT X.S#
FROM SC AS X,SC AS Y
WHERE X.S#=Y.S#
AND
X.C#=’C00101’
AND
Y.C#=’C02341’ 2、图书管理数据库 BookManager 由以下四个关系模式组成，根据该结构用 T-SQL 完成以下各题。
（1）读者（借书证号，姓名，性别，出生日期）
（2）图书（ISBN，图书名称，作者，出版社，价格，数量）
（3）借阅（借书证号，ISBN，借出时间）
根据要求完成下列各题。
（1）创建一个名称为 BookManager 的数据库，其初始大小为 10MB，最大为 20MB，允许数据库自动增长；日志文件初始大小为 2MB，最大为 5MB。文件存储在目录 D:\TEST 下。
（2）编写一个存储过程，查询某读者当前的借书情况。
（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。
解：（1）CREATE DATABASE BookManager ON( NAME=’BookManager_DATA’, FILENAME=’D:\TEST\BookManager.MDF’,
SIZE=10MB, MAXSIZE=20MB, FILEGROWTH=10%) LOG ON( FILENAME=’D:\TEST\BookManager.LDF’, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=10%)
（2）CREATE PROCEDURE proc_reader
@ lib_num char(8)
AS
SELECT A.借书证号,姓名,B.ISBN,书名
FROM Reader
A, JY
B , BOOK
C
WHERE A. 借书证号=B. 借书证号 AND B.ISBN=C.ISBN
AND
A. 借书证号= @ lib_num （3）CREATE TRIGGER trig_reminder ON Reader FOR INSERT ,UPDATE AS
RAISEERROR(4008,16,10)
3、设有学生表 S（SNO，SN）（SNO 为学生号，SN 为姓名）和学生选修课程表 SC（SNO，CNO，CN，G）（CNO 为课程号，CN 为课程名，G 为成绩），试用 SQL 语言完成以下各题：
（1）建立一个视图 V_SSC(SNO,SN,CNO,CN,G)，并按 CNO 升序排序； （2）从视图 V_SSC 上查询平均成线在 90 分以上的 SN、CN 和 G。
答：（1）CREATE VIEW V_SSC(SNO,SN,CNO,CN,G)
AS SELECT S.SNO,S.SN,CNO,SC.CN,SC.G
FROM S,SC
WHERE S.SNO=SC.SNO
ORDER BY CNO
（2）
SELECT SN,CN,G
FROM V_SSC
GROUP BY SNO
HAVING AVG(G)>90 4、图书管理数据库 BookManager 由以下四个关系模式组成，根据该结构用 T-SQL 完成以下各题。
（1）读者（借书证号，姓名，性别，出生日期）
（2）图书（ISBN，图书名称，作者，出版社，价格，数量）
（3）借阅（借书证号，ISBN，借出时间）
根据要求完成下列各题。
（1）创建一个名称为 BookManager 的数据库，其初始大小为 10MB，最大为 20MB，允许数据库自动增长；日志文件初始大小为 2MB，最大为 5MB。文件存储在目录 D:\TEST 下。
（2）编写一个存储过程，查询某读者当前的借书情况。
（3）创建一个触发器，如果在图书表中添加或更改数据，则向客户端显示一条信息。
解：（1）CREATE DATABASE BookManager ON( NAME=’BookManager_DATA’, FILENAME=’D:\TEST\BookManager.MDF’, SIZE=10MB, MAXSIZE=20MB, 字段名 类型与 宽度 是否 主码 是否允许 空值 借书证号 Char(8) 是 NOT NULL 姓名 Char(8)
NOT NULL 专业名 Char(12)
NOT NULL 性别 Char(2)
NOT NULL 出生日期 Datetime
NOT NULL 借书量 Integer
NOT NULL 照片 Image
NOT NULL 字段名 类型与 宽度 是 否主码 是否允许 空值 ISBN Char(16) 是 NOT NULL 书名 Char(26)
NOT NULL 作者 Char(8)
NOT NULL 出版社 Char(20)
NOT NULL 价格 Float
NOT NULL 副本量 Integer
NOT NULL 库存量 Integer
NOT NULL 字段名 类型与 宽度 是否 主码 是否允许 空值 借书证号 Char(8)
NOT NULL ISBN Char(16)
NOT NULL 索书号 Char(10) 是 NOT NULL 借书时间 Datetime 是 NOT NULL 还书时间 Datetime
NOT NULL 字段名 类型与 宽度 是否 主码 是否允许 空值 借书证号 Char(8)
NOT NULL ISBN Char(16)
NOT NULL 索书号 Char(10) 是 NOT NULL 借书时间 Datetime
NOT NULL 表 4.1 读者表(表名为 XS) 表 4.2 图书表(表名为 BOOK) 表 4.3 借阅表(表名为 JY) 表 4.4 借阅历史表(表名为 JYLS)
FILEGROWTH=10%) LOG ON( FILENAME=’D:\TEST\BookManager.LDF’, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=10%)
（2）CREATE PROCEDURE proc_reader
@ lib_num char(8)
AS
SELECT A.借书证号,姓名,B.ISBN,书名
FROM Reader
A, JY
B , BOOK
C
WHERE A. 借书证号=B. 借书证号 AND B.ISBN=C.ISBN
AND
A. 借书证号= @ lib_num （3）CREATE TRIGGER trig_reminder ON Reader FOR INSERT ,UPDATE AS
RAISEERROR(4008,16,10)
5、下面给出一个商品销售数据库中包含的三个基本表：
（1）客户表：table_Client（CNO，Cname，Csex，Cbirthday）
（2）商品表：table_Goods（GNO，Gname，Gunit，Gprice）
（3）购物表：table_CG（CNO，GNO，CGquantity）
用 T-SQL 语句完成下列各题。
（1）查询购买了商品的客户号及姓名。
（2）查询 1978 年出生的客户号及姓名。
（3）购买了三种以上商品的客户号及客户姓名。
答：（1）select CNO,Cname from table_Client where CNO in (select distinct CNO from table_CG) （2）select CNO,Cname
from table_Client
where Cbirthday=’1978’ （3）select CNO,Cname
from table_Client
where CNO in
(select CNO from table_CG group by CNO having count(*)>3)
6、学生-选课-课程数据库由以下四个关系模式构成：
STUDENT（SNO，SName，SAge，SSex，SAddress）
TEACHER（TNO，TTeacher，TPos，TAddress）
COURSE（CNO，CName，CTerm，CCredit，TNO）
SELECTCOURSE（SNO，CNO，Grade）
按要求完成下列各题。
（1）用 SQL 语句建立 STUDENT 表。
（2）为 COURSE 表建立插入触发器，保证教师编号的正确性。
（1）
解：create table student (
sno nvarchar(8),
sname nvarchar(10),
sage int,
ssex char(4),
saddress nvarchar(50),
primary key(sno) ) （2）
create trigger course_insert_tno on course
for insert
as if(not exists
(select tno from teacher
where tno in
(select inserted.tno from inserted)))
begin
print "无此教师"
rollback transaction
end 1、设学生课程数据库中有三个关系：
学生关系 S（S#，SNAME，AGE，SEX）
学习关系 SC（S#，C#，GRADE）
课程关系 C（C#，CNAME）
其中 S#、C#、SNAME、AGE、SEX、GRADE、CNAME 分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。
用 SQL 语句表达下列操作 （1）检索选修课程名称为“MATHS”的学生的学号与姓名
（2）检索至少学习了课程号为“C1”和“C2”的学生的学号 （3）检索年龄在 18 到 20 之间（含 18 和 20）的女生的学号、姓名和年龄 （4）检索平均成绩超过 80 分的学生学号和平均成绩 （5）检索选修了全部课程的学生姓名 （6）检索选修了三门课以上的学生的姓名 答案：（1）SELECT SNAME,AGE
FROM S,SC,C
WHERE S.S#=SC.S#
AND C.C#=SC.C#
AND CNAME=’ MATHS’
(2) SELECT S#
FROM SC
WHERE CNO=’C1’ AND S# IN( SELECT S#
FROM SC
WHERE CNO=’C2’)
(3)SELECT S#,SNAME,AGE
FROM S
WHERE AGE BETWEEN 18 AND 20
(4) SELECT S# ,AVG(GRADE) ‘平均成绩’
FROM SC
GROUP BY S#
HAVING AVG(GRADE)>80 (5) SELECT
SNAME FROM
S WHERE
NOT
EXISTS
(SELECT
*
FROM
C
WHERE NOT
EXISTS
(SELECT
*
FROM
SC
WHERE S#=S.S#
AND C#=C.C#
) )
(6) SELECT SNAME
FROM S,SC
WHERE S.S#=SC.S#
GROUP BY SNAME
HAVING COUNT(*)>3
2、设学生-课程数据库中包括三个表：
学生表：Student （Sno，Sname，Sex，Sage，Sdept）
课程表：Course（Cno，Cname，Ccredit）
学生选课表：SC（Sno，Cno，Grade）
其中 Sno、Sname、Sex、Sage、Sdept、 Cno、Cname、Ccredit 、Grade 分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。
试用 SQL 语言完成下列项操作：
（1）查询选修课程包括“1042”号学生所学的课程的学生学号 （2）创建一个计科系学生信息视图 S_CS_VIEW，包括 Sno 学号、Sname 姓名、Sex 性别; （3）通过上面第 2 题创建的视图修改数据，把王平的名字改为王慧平 （4）创建一选修数据库课程信息的视图，视图名称为 datascore_view，包含学号、姓名、成绩。
答案：(1) SELECT DISTINCT SNO
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.SNO = " 1042"
AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.SNO=SCX.SNO AND
SCZ.CNO=SCY.CNO))；
(2)
CREATE VIEW S_CS_VIEW
AS
SELECT SNO,SNAME,SEX
FROM STUDENT
WHERE Sdept=’CS’
(3)UPDATE S_CS_VIEW
SET SNAME=’ 王慧平’
WHERE SNAME=’ 王平’ (4) CREATE VIEW datascore_view
AS
SELECT
SNO
学号、SNAME
姓名、GRADE 成绩
FROM STUDENT,SC,COURSE
WHERE STUDENT.SNO=SC.SNO
AND COURSE.CNO=SC.CNO
AND CNAME=’ 数据库’