1、关系数据库操作语言 SQL(四)及答案解析(总分：100.00，做题时间：90 分钟)一、B设计题/B(总题数：9，分数：100.00)在 SQL Server 2000 中，有教师表 Teachers(TeacherID，Name, LeaderID)，其中 TeacherID 是主码，类型是长度为 4 的普通编码定长字符串，且每位是09 的数字字符；Name 的类型是长度为 10 的普通编码可变长字符串；LeaderID 是每个教师的领导的 TeacherID。（分数：10.00）(1).写出创建 Teachers 表的 SQL 语句，要求语句中包含所有的约束。（分数：2.50）_(2).
2、现要查询 TeacherID 为“1234”的教师的领导的领导的 TeacherID，请给出相应的 SQL 语句，要求只使用一条 SQL 语句实现，且此语句中不允许包含子查询。（分数：2.50）_(3).如下两个关系表： Emp(eid, ename, age, did, salary)，其各列含义为：职工号，姓名，年龄，部门号，工资。 Dept(did, dname, mgr_id)，其各列含义为：部门号，部门名称，部门经理职工号。 写出一条 SQL 语句，查询工资大于 10000，且与其所在部门的经理年龄相同的职工姓名。（分数：2.50）_(4).写出创建下述关系表的 SQL 语句。 SB
3、tudent 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码Sname 姓名 普通编码定长字符串，长度为 10 非空Ssex 性别 普通编码定长字符串，长度为 2 取值范围为：男，女Sage 年龄 微整型 大于等于 14Sdept 所在系 普通编码不定长字符串，长度为 20BCourse 表结构/B列名 含义 数据类型 约束Cno 课程号 普通编码定长字符串，长度为 10 主码Cname 课程名 普通编码不定长字符串，长度为 20 非空Periods 学时数 小整型 大于 0Property 课程性质 普通编码定长字符串，长度为 4 取值范围为：必修，选
4、修（分数：2.50）_存在如下表结构： BStudent 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码Sname 姓名 普通编码定长字符串，长度为 10 非空Ssex 性别 普通编码定长字符串，长度为 2 取值范围为：男，女Sage 年龄 微整型 大于等于 14Sdept 所在系 普通编码不定长字符串，长度为 20BCourse 表结构/B列名 含义 数据类型 约束Cno 课程号 普通编码定长字符串，长度为 10 主码Cname 课程名 普通编码不定长字符串，长度为 20 非空Periods 学时数 小整型 大于 0Property 课程性质 普通编码
5、定长字符串，长度为 4 取值范围为：必修，选修BSC 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码，引用 Student 的外码Cno 课程号 普通编码定长字符串，长度为 10 主码，引用 Course 的外码Grade 成绩 小整型 取值范围：0100写出实现下述操作的 SQL 语句：（分数：45.00）(1).查询选课门数超过 2 门的学生的平均成绩和选课门数。（分数：2.50）_(2).列出总成绩超过 200 分的学生，要求列出学号、总成绩。（分数：2.50）_(3).查询选修了“C02”号课程的学生的姓名和所在系。（分数：2.50）_(4).查
6、询成绩在 80 分以上的学生的姓名、课程名和成绩，并将结果按成绩的降序排列。（分数：2.50）_(5).查询计算机系男生修了“数据库基础”的学生的姓名、姓名和成绩。（分数：2.50）_(6).查询学生的选课情况，要求列出每位学生的选课情况(包括未选课的学生)，并列出学生的学号和考试成绩。（分数：2.50）_(7).列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。（分数：2.50）_(8).查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。（分数：2.50）_(9).查询哪些课程没人选，要求列出课程号和课程名。（分数：2.50）_(10).查询计算机系学
7、生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程号和考试成绩。（分数：2.50）_(11).查询 VB 考试成绩最低的学生的姓名、所在系和 VB 成绩。（分数：2.50）_(12).查询选修了 VB 课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：当所在系为“计算机系”时，显示“CS”；当所在系为“信息系”时，显示“IS”；当所在系为“数学系”时，显示“MA”；对其他系，均显示“OTHER”。（分数：2.50）_(13).查询每门课程的选课人数，如果选课人数超过了 100 人，显示“人多”；如果选课人数在 40100之间，显示“一般”；如果选课人数少于 40 人，显示“
8、较少”；如果此门课程没人选，显示“无人选”。（分数：2.50）_(14).在课程表中增加一条新记录：课程号为“C100”，课程名为“Java”，学时数 32，课程性质为“选修”。（分数：2.50）_(15).删除修课成绩小于 50 分的学生的修课记录。（分数：2.50）_(16).删除信息系修课成绩小于 50 分的学生的修课记录。（分数：2.50）_(17).将所有选修了“C01”号课程的学生的成绩加 10 分。（分数：2.50）_(18).将计算机系所有选修了“计算机文化学”课程的学生的成绩加 10 分。（分数：2.50）_有如下关系数据库：学生(学号，姓名，性别，专业，奖学金)课程(课程号
9、，名称，学分)学习(学号，课程号，分数)用 SQL 语言完成下列操作：（分数：10.00）(1).检索没有任何一门课程成绩在 80 分以下的所有学生的信息，包括学号、姓名和专业；（分数：2.50）_(2).检索没有获得奖学金同时至少有一门课程成绩在 95 分以上的学生信息，包括学号、姓名和专业；（分数：2.50）_(3).对成绩得过 100 分的学生，如果没有获得奖学金的，将其奖学金设为 1000 元；（分数：2.50）_(4).定义学生成绩得过满分(100 分)的课程视图 AAA，包括课程号、名称和学分。（分数：2.50）_现有“学生-选课-课程”关系数据库如下：学生表 S(Sno, Sna
10、me, Sage, Ssex, Sdept)，各属性含义依次为学号，姓名，年龄，性别，所在系；选课表 SC(Sno, Cno, Grade)，各属性含义依次为学号，课程号，成绩；课程表 C(Cno, Cname, Cteacher)，各属性含义依次为课程号，课程名，授课教师；试用 SQL 语言完成以下操作：（分数：5.00）(1).检索年龄在 18 到 22 之间(含 18 和 22)的男生的学号、姓名及年龄；（分数：2.50）_(2).检索选修了“王杰”老师所授课程的学生的学号和姓名。（分数：2.50）_学生管理数据库包括 3 个基本表，其结构为：学生(学号，姓名，年龄，所在系)课程(课程表
11、，课程号)选课(学号，课程号，成绩)试用 SQL 语言完成操作：（分数：15.00）(1).求数学系或计算机系姓张的学生信息；（分数：2.50）_(2).求学生人数；（分数：2.50）_(3).求选修高等数学的学生人数；（分数：2.50）_(4).求选修了高等数学的学生学号和姓名；（分数：2.50）_(5).求其他系中比计算机系学生年龄都小的学生；（分数：2.50）_(6).求选修课程号为 C1 的课程且成绩为 90 分以上的学生学号、姓名及成绩。（分数：2.50）_已知关系模式：S(SNO, SNAME)学生关系，SNO 为学号，SNAME 为姓名；C(CNO, CNAME, CTEACHE
12、R)课程关系，CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师；SC(SNO, CNO,SCGRADE)选课关系，SCGRADE 为成绩。（分数：6.00）(1).找出没有选修过“李明”老师讲授课程的所有学生姓名；（分数：1.50）_(2).列出有两门以上(含两门)不及格课程的学生姓名及其平均成绩；（分数：1.50）_(3).列出既学过“1”号课程，又学过“2”号课程的所有学生姓名。（分数：1.50）_(4).在 SQL Server 2000 中，设某数据库中有商品表(商品号，商品名，进货价格)，商品号为主码；销售表(商品号，销售时间，销售数量，销售价格，本次利润)，商品
13、号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前 4 列数据时(假设一次只插入一行数据)，系统自动计算本次销售产生的利润，并将该利润赋给销售表的第 5 列“本次利润”。请编写实现上述功能的后触发型触发器代码。（分数：1.50）_在 SQL Server 2000 的某数据库中有如下两张关系表：学生表(学号，姓名，性别，系号)，学号为主码系表(系号，系名，系简称)，系号为主码（分数：3.00）(1).在数据库中执行如下 T-SQL 代码： DECLARE DeptID varchar(10) DECLARE cnt int Set cnt = 0 DECLARE cu
14、rsor1 cursor FOR SELECT 系号 FROM 系表 WHERE 系名 LIKE %电% OPEN cursor1 FETCH NEXT FROM cursor1 INTO DeptID WHERE FETCH_STATUS=0 BEGIN DECLARE temp_cnt int SELECT temp_int = COUNT(*) FROM 学生表 WHERE 系号 = DeptID SET cnt = cnt + temp_cnt FETCH NEXT FROM cursor1 INT0 DeptID END CLOSE cursor1 DEALLOCATE cursor
15、1 SELECT cnt 执行过程中发现速度比较慢，为了解决性能问题，需在功能不变的情况下，将此 T-SQL 代码改为一条 SQL 语句。请写出此SQL 语句(语句中不能含有子查询)并说明为什么此种修改可以提高性能。（分数：1.50）_(2).设在学生表的(姓名，系号)列上建有一个复合索引，该索引是否有助于提高下列两个语句的查询效率，并说明原因。 SELECT * FROM 学生表 WHERE 系号=1； SELECT * FROM 学生表 WHERE 姓名=张三（分数：1.50）_有学生表(学号，姓名，年龄，性别，系名，专业名，班号)，设一个系可有多个专业，每个专业可有多个班，各班班号不重复
16、，一个学生只在一个班学习，现经常需要按“系名”和“班号”进行查询，为提高查询效率，需要为“系名”和“班号”两个列建立一个非聚集索引，建立此索引有下列两种方法：方法 1：索引列顺序为(系名，班号)方法 2：索引列顺序为(班号，系名)（分数：3.00）(1).这两种方法哪个更合理?请简要说明原因。（分数：1.50）_(2).针对你认为合理的方法，写出创建该索引的 SQL 语句。（分数：1.50）_在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：系(系号，系名)，系号为主码学生(学号，姓名，所在系号)，学号为主码课程(课程号，课程名，开课系号)，课程号为主码选课(学号，
17、课程号，选课时间)，学号和课程号为主码开发人员在将关系模式实施到 SQL Server 2000 的“教务”数据库时，使用了如下表结构定义语句：CREATE TABLE 系(系号 varchar(10)NOT NULL,系名 varchar(100)CREATE TABLE 学生(学号 varchar(50)NOT NULL,姓名 varchar(50),所在系号 varchar(10)CREATE TABLE 课程(课程号 varchar(50)NOT NULL,课程名 varchar(100),开课系号 varchar(10)CREATE TABLE 选课(学号 varchar(50)NO
18、T NULL,课程号 varchar(50)NOT NULL,选课时间 datetime)在执行如下查询语句时发现执行效率很低：SELECT*FROM 选课 JOIN 学生 ON 学生. 学号=选课. 学号JOIN 系 ON 系. 系号=学生. 所在系号JOIN 课程 ON 课程. 课程号=选课. 课程号WHERE 系. 系号=012AND convert(varchar(10)，选课时间，120)=2010-01-01（分数：3.00）(1).在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。（分数：1.50）_(2).设已在“选课”表的“选课时间”列及“
19、学生”表的“所在系号”列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率?如果不能，请说明原因。（分数：1.50）_关系数据库操作语言 SQL(四)答案解析(总分：100.00，做题时间：90 分钟)一、B设计题/B(总题数：9，分数：100.00)在 SQL Server 2000 中，有教师表 Teachers(TeacherID，Name, LeaderID)，其中 TeacherID 是主码，类型是长度为 4 的普通编码定长字符串，且每位是09 的数字字符；Name 的类型是长度为 10 的普通编码可变长字符串；LeaderID 是每个教师的领导的 TeacherID。（分
20、数：10.00）(1).写出创建 Teachers 表的 SQL 语句，要求语句中包含所有的约束。（分数：2.50）_正确答案：(create table Teachers( TeacherID char(4) check(TeacherID like“09090909“), Name varchar(10), LeaderID char(4), primary key TeacherID, foreign key LeaderID references Teachers(TeacherID) )解析：解析 创建表格使用 CREATE 语句。(2).现要查询 TeacherID 为“1234”
21、的教师的领导的领导的 TeacherID，请给出相应的 SQL 语句，要求只使用一条 SQL 语句实现，且此语句中不允许包含子查询。（分数：2.50）_正确答案：(select T2. LeaderID from Teachers as T1 join Teachers as T2 on T1. LeaderID=T2. TeacherID where T1. TeacherID=1234)解析：(3).如下两个关系表： Emp(eid, ename, age, did, salary)，其各列含义为：职工号，姓名，年龄，部门号，工资。 Dept(did, dname, mgr_id)，其各列
22、含义为：部门号，部门名称，部门经理职工号。 写出一条 SQL 语句，查询工资大于 10000，且与其所在部门的经理年龄相同的职工姓名。（分数：2.50）_正确答案：(select a. * from Emp a, Dept b, Emp c where a. did=b. did and b. mgr_id=c. eid and a. salary10000 and a. age=c. age)解析：(4).写出创建下述关系表的 SQL 语句。 SBtudent 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码Sname 姓名 普通编码定长字符串，长度为
23、10 非空Ssex 性别 普通编码定长字符串，长度为 2 取值范围为：男，女Sage 年龄 微整型 大于等于 14Sdept 所在系 普通编码不定长字符串，长度为 20BCourse 表结构/B列名 含义 数据类型 约束Cno 课程号 普通编码定长字符串，长度为 10 主码Cname 课程名 普通编码不定长字符串，长度为 20 非空Periods 学时数 小整型 大于 0Property 课程性质 普通编码定长字符串，长度为 4 取值范围为：必修，选修（分数：2.50）_正确答案：(创建 Student 表的 SQL 语句为： CREATE TABLE Student( Sno char(7)
24、 primary key, Sname char(10) not null, Ssex char(2) check(Ssex In(男,女), Sage tinyint check(Sage = 14), Sdept varchar(20) ) 创建 Course 表的 SQL 语句为： CREATE TABLE Course( Cno char(10) primary key, Cname varchar(20) not null, Periods smallint check (Periods0), Property char(4) check(Property in(必修,选修) )解析
25、：解析 本题考查的是 SQL 语句。存在如下表结构： BStudent 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码Sname 姓名 普通编码定长字符串，长度为 10 非空Ssex 性别 普通编码定长字符串，长度为 2 取值范围为：男，女Sage 年龄 微整型 大于等于 14Sdept 所在系 普通编码不定长字符串，长度为 20BCourse 表结构/B列名 含义 数据类型 约束Cno 课程号 普通编码定长字符串，长度为 10 主码Cname 课程名 普通编码不定长字符串，长度为 20 非空Periods 学时数 小整型 大于 0Property 课程
26、性质 普通编码定长字符串，长度为 4 取值范围为：必修，选修BSC 表结构/B列名 含义 数据类型 约束Sno 学号 普通编码定长字符串，长度为 7 主码，引用 Student 的外码Cno 课程号 普通编码定长字符串，长度为 10 主码，引用 Course 的外码Grade 成绩 小整型 取值范围：0100写出实现下述操作的 SQL 语句：（分数：45.00）(1).查询选课门数超过 2 门的学生的平均成绩和选课门数。（分数：2.50）_正确答案：(select sno, sum (grade) as/总成绩，avg (grade) as/平均成绩，count (*) as/选课门数 fro
27、m sc group by sno having count (*) 2)解析：解析 本题考查的是 SQL 操作。(2).列出总成绩超过 200 分的学生，要求列出学号、总成绩。（分数：2.50）_正确答案：(select sno, sum (grade) 总成绩 from sc group by sno having sum (grade) 200)解析：(3).查询选修了“C02”号课程的学生的姓名和所在系。（分数：2.50）_正确答案：(select sname, sdept from Student join SC on Student. Sno =SC. Sno where cno=
28、 C02)解析：(4).查询成绩在 80 分以上的学生的姓名、课程名和成绩，并将结果按成绩的降序排列。（分数：2.50）_正确答案：(select sname, cno, grade from student s join sc on s. sno=sc. sno where grade 80 Order by grade desc)解析：(5).查询计算机系男生修了“数据库基础”的学生的姓名、姓名和成绩。（分数：2.50）_正确答案：(select sname，ssex, grade from student s join sc on s. sno=sc. sno join course c
29、 on c. cno=sc. cno where sdept= 计算机系 and ssex= 男 and cname= 数据库基础)解析：(6).查询学生的选课情况，要求列出每位学生的选课情况(包括未选课的学生)，并列出学生的学号和考试成绩。（分数：2.50）_正确答案：(select s. sno, sname, cno, grade from Student s left join SC on s. Sno=SC. Sno)解析：(7).列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。（分数：2.50）_正确答案：(select top 3 s. sno, sna
30、me, sdept, grade from Student s join sc on s. Sno=sc. Sno join Course c on c. Cno = sc. Cno where cname = 数据库基础 order by grade desc)解析：(8).查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。（分数：2.50）_正确答案：(select t1. sno, t2. sno, o from sc as t1 join sc as t2 on t1. cno = t2. cno where t1. sno t2. sno)解析：(9).查询哪些课程没
31、人选，要求列出课程号和课程名。（分数：2.50）_正确答案：(select c. cno, cname from course c left join sc on c. cno = sc. cno where sc. cno is null)解析：(10).查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程号和考试成绩。（分数：2.50）_正确答案：(select sname, cname, grade from student s join sc on s. sno = sc. sno join course c on o = sc. cno where sdept =计算机系 and grade (select avg(grade) from sc)解析：(11).查询 VB 考试成绩最低的学生的姓名、所在系和 VB 成绩。（分数：2.50）_正确答案：(select sname, sdept, grade from student s join sc on s. sno = sc. sno join course c on c. cno = sc. cno where grade = ( select min(grade) from sc
