关系数据库操作语言SQL(四)
(总分100, 做题时间90分钟)
设计题
在SQL Server 2000中，有教师表Teachers(TeacherID，Name, LeaderID)，其中TeacherID是主码，类型是长度为4的普通编码定长字符串，且每位是0～9的数字字符；Name的类型是长度为10的普通编码可变长字符串；LeaderID是每个教师的领导的TeacherID。
1. 
写出创建Teachers表的SQL语句，要求语句中包含所有的约束。
2. 
现要查询TeacherID为“1234”的教师的领导的领导的TeacherID，请给出相应的SQL语句，要求只使用一条SQL语句实现，且此语句中不允许包含子查询。
3. 
如下两个关系表：
    Emp(eid, ename, age, did, salary)，其各列含义为：职工号，姓名，年龄，部门号，工资。
    Dept(did, dname, mgr_id)，其各列含义为：部门号，部门名称，部门经理职工号。
    写出一条SQL语句，查询工资大于10000，且与其所在部门的经理年龄相同的职工姓名。
4. 
写出创建下述关系表的SQL语句。
Student表结构
列名 含义 数据类型 约束
Sno 学号 普通编码定长字符串，长度为7 主码
Sname 姓名 普通编码定长字符串，长度为10 非空
Ssex 性别 普通编码定长字符串，长度为2 取值范围为：{男，女}
Sage 年龄 微整型 大于等于14
Sdept 所在系 普通编码不定长字符串，长度为20  
Course表结构
列名 含义 数据类型 约束
Cno 课程号 普通编码定长字符串，长度为10 主码
Cname 课程名 普通编码不定长字符串，长度为20 非空
Periods 学时数 小整型 大于0
Property 课程性质 普通编码定长字符串，长度为4 取值范围为：{必
修，选修}
存在如下表结构：
Student表结构
列名 含义 数据类型 约束
Sno 学号 普通编码定长字符串，长度为7 主码
Sname 姓名 普通编码定长字符串，长度为10 非空
Ssex 性别 普通编码定长字符串，长度为2 取值范围为：{男，女}
Sage 年龄 微整型 大于等于14
Sdept 所在系 普通编码不定长字符串，长度为20  

Course表结构
列名 含义 数据类型 约束
Cno 课程号 普通编码定长字符串，长度为10 主码
Cname 课程名 普通编码不定长字符串，长度为20 非空
Periods 学时数 小整型 大于0
Property 课程性质 普通编码定长字符串，长度为4 取值范围为：{必
修，选修}

SC表结构
列名 含义 数据类型 约束
Sno 学号 普通编码定长字符串，长度为7 主码，引用Student的外码
Cno 课程号 普通编码定长字符串，长度为10 主码，引用Course的外码
Grade 成绩 小整型 取值范围：0～100
    写出实现下述操作的SQL语句：
5. 
查询选课门数超过2门的学生的平均成绩和选课门数。
6. 
列出总成绩超过200分的学生，要求列出学号、总成绩。
7. 
查询选修了“C02”号课程的学生的姓名和所在系。
8. 
查询成绩在80分以上的学生的姓名、课程名和成绩，并将结果按成绩的降序排列。
9. 
查询计算机系男生修了“数据库基础”的学生的姓名、姓名和成绩。
10. 
查询学生的选课情况，要求列出每位学生的选课情况(包括未选课的学生)，并列出学生的学号和考试成绩。
11. 
列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。
12. 
查询哪些学生合选了一门课程，要求列出合选课程的学生的学号和课程号。
13. 
查询哪些课程没人选，要求列出课程号和课程名。
14. 
查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程号和考试成绩。
15. 
查询VB考试成绩最低的学生的姓名、所在系和VB成绩。
16. 
查询选修了VB课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：当所在系为“计算机系”时，显示“CS”；当所在系为“信息系”时，显示“IS”；当所在系为“数学系”时，显示“MA”；对其他系，均显示“OTHER”。
17. 
查询每门课程的选课人数，如果选课人数超过了100人，显示“人多”；如果选课人数在40～100之间，显示“一般”；如果选课人数少于40人，显示“较少”；如果此门课程没人选，显示“无人选”。
18. 
在课程表中增加一条新记录：课程号为“C100”，课程名为“Java”，学时数32，课程性质为“选修”。
19. 
删除修课成绩小于50分的学生的修课记录。
20. 
删除信息系修课成绩小于50分的学生的修课记录。
21. 
将所有选修了“C01”号课程的学生的成绩加10分。
22. 
将计算机系所有选修了“计算机文化学”课程的学生的成绩加10分。
有如下关系数据库：
    学生(学号，姓名，性别，专业，奖学金)
    课程(课程号，名称，学分)
    学习(学号，课程号，分数)
    用SQL语言完成下列操作：
23. 
检索没有任何一门课程成绩在80分以下的所有学生的信息，包括学号、姓名和专业；
24. 
检索没有获得奖学金同时至少有一门课程成绩在95分以上的学生信息，包括学号、姓名和专业；
25. 
对成绩得过100分的学生，如果没有获得奖学金的，将其奖学金设为1000元；
26. 
定义学生成绩得过满分(100分)的课程视图AAA，包括课程号、名称和学分。
现有“学生-选课-课程”关系数据库如下：
    学生表S(Sno, Sname, Sage, Ssex, Sdept)，各属性含义依次为学号，姓名，年龄，性别，所在系；
    选课表SC(Sno, Cno, Grade)，各属性含义依次为学号，课程号，成绩；
    课程表C(Cno, Cname, Cteacher)，各属性含义依次为课程号，课程名，授课教师；
    试用SQL语言完成以下操作：
27. 
检索年龄在18到22之间(含18和22)的男生的学号、姓名及年龄；
28. 
检索选修了“王杰”老师所授课程的学生的学号和姓名。
学生管理数据库包括3个基本表，其结构为：
    学生(学号，姓名，年龄，所在系)
    课程(课程表，课程号)
    选课(学号，课程号，成绩)
    试用SQL语言完成操作：
29. 
求数学系或计算机系姓张的学生信息；
30. 
求学生人数；
31. 
求选修高等数学的学生人数；
32. 
求选修了高等数学的学生学号和姓名；
33. 
求其他系中比计算机系学生年龄都小的学生；
34. 
求选修课程号为C1的课程且成绩为90分以上的学生学号、姓名及成绩。
已知关系模式：S(SNO, SNAME)学生关系，SNO为学号，SNAME为姓名；C(CNO, CNAME, CTEACHER)课程关系，CNO为课程号，CNAME为课程名，CTEACHER为任课教师；SC(SNO, CNO,SCGRADE)选课关系，SCGRADE为成绩。
35. 
找出没有选修过“李明”老师讲授课程的所有学生姓名；
36. 
列出有两门以上(含两门)不及格课程的学生姓名及其平均成绩；
37. 
列出既学过“1”号课程，又学过“2”号课程的所有学生姓名。
38. 
在SQL Server 2000中，设某数据库中有商品表(商品号，商品名，进货价格)，商品号为主码；销售表(商品号，销售时间，销售数量，销售价格，本次利润)，商品号和销售时间为主码，销售价格为本次销售商品的单价。现要求每当在销售表中插入前4列数据时(假设一次只插入一行数据)，系统自动计算本次销售产生的利润，并将该利润赋给销售表的第5列“本次利润”。请编写实现上述功能的后触发型触发器代码。
在SQL Server 2000的某数据库中有如下两张关系表：
    学生表(学号，姓名，性别，系号)，学号为主码
    系表(系号，系名，系简称)，系号为主码
39. 
在数据库中执行如下T-SQL代码：
    DECLARE @DeptID varchar(10)
    DECLARE @cnt int
    Set @cnt = 0
    DECLARE cursor1 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 cursor1
    SELECT @cnt
    执行过程中发现速度比较慢，为了解决性能问题，需在功能不变的情况下，将此T-SQL代码改为一条SQL语句。请写出此SQL语句(语句中不能含有子查询)并说明为什么此种修改可以提高性能。
40. 
设在学生表的(姓名，系号)列上建有一个复合索引，该索引是否有助于提高下列两个语句的查询效率，并说明原因。
    SELECT * FROM学生表 WHERE 系号='1'；
    SELECT * FROM 学生表 WHERE 姓名='张三'
有学生表(学号，姓名，年龄，性别，系名，专业名，班号)，设一个系可有多个专业，每个专业可有多个班，各班班号不重复，一个学生只在一个班学习，现经常需要按“系名”和“班号”进行查询，为提高查询效率，需要为“系名”和“班号”两个列建立一个非聚集索引，建立此索引有下列两种方法：
    方法1：索引列顺序为(系名，班号)
    方法2：索引列顺序为(班号，系名)
41. 
这两种方法哪个更合理?请简要说明原因。
42. 
针对你认为合理的方法，写出创建该索引的SQL语句。
在进行某学校教务管理系统的数据库设计时，数据库设计人员设计了如下几个关系模式：
    系(系号，系名)，系号为主码
    学生(学号，姓名，所在系号)，学号为主码
    课程(课程号，课程名，开课系号)，课程号为主码
    选课(学号，课程号，选课时间)，学号和课程号为主码
    开发人员在将关系模式实施到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)NOT NULL,
    课程号varchar(50)NOT NULL,
    选课时间datetime
    )
    在执行如下查询语句时发现执行效率很低：
    SELECT*FROM 选课 JOIN 学生 ON 学生. 学号=选课. 学号
    JOIN 系 ON 系. 系号=学生. 所在系号
    JOIN 课程 ON 课程. 课程号=选课. 课程号
    WHERE 系. 系号=012
    AND convert(varchar(10)，选课时间，120)＞=2010-01-01
43. 
在查找原因时发现建表语句有问题。请指出问题并说明该问题是否会影响此查询语句的执行效率。
44. 
设已在“选课”表的“选课时间”列及“学生”表的“所在系号”列上建立了索引。请问这两个索引是否能够提高该查询语句的执行效率?如果不能，请说明原因。