结构化查询语言(A)

注意：

1、先将此文件改名为自己的学号+姓名，然后做题，最后只要提交该文件即可

2、必须严格按分配表中指定的题目作答，否则，成绩记为0分。

3、遵守考试纪律，考试时只允许打开考试文件和SQL SERVER；有软件录屏，只要在屏幕上发现其它软件窗口，一律按作弊处理。

一、创建数据库XSKC，并设置数据库的主数据文件名为XSKC_DATA，初始大小为10MB，最大为无限大，增长速度为20%。日志文件名为XSKC_LOG，初始大小为2MB，最大为5MB，增长速度为1MB。所有的文件放在D:\DATA中。

XSKC数据库包含以下图示数据表：

要求：

1、将SC表中的学生号，课程号设置为外键，分别参照学生表中的学生号和课程表中的课程号

2、为课程表设置外键CPNO，参照课程表的主键CNO

3、为学生表设置约束（Check），规定性别只能是‘男’或者‘女’，默认值为“男”；为成绩表设置约束，规定成绩值为0-100之间的数值。Student

Course

SC

请在此处写出创建数据库和各数据表及插入数据的SQL语句：

二、请完成以下SQL语句操作：（按如下方法答题，将正确的SQL语句运行，有运行结果后，拷贝屏幕，以图形形式粘贴在对应题号下方，要求SQL语句字号大一些，能看清楚些）

0、查询所有学生的信息

（一）简单选择和投影及空值：

1、查找计算机系或信息系的女生学号、姓名

2、查找计算机系的学生信息或所有男生的信息（用集合查询做）

3、查找成绩在80到90之间的学生的成绩信息

4、查找没有被选修的课程号

5、查找没有选修课程的学生学号

6、查找1998年出生的学生名单，以如下形式显示：Sname、Birthyear

7、查找每个学生的学号，增加5岁后的年龄及转化为大写后的系部,以如下形式显示：Sno、NewSage、UpSdept

8、查询先修课为空值的课程号和课程名

（二）LIKE子句的应用

9、查找姓张的学生信息

10、查找姓名中第二个字为“立”的学生信息

11、查找系部中不包含“S”的学生

12、查找系部中第一个字符为A-H的学生信息

13、查询"张"姓学生的数量

14、查找系部的第二个字符不为‘S’的学生信息

15、查询姓"欧阳"且全名为三个汉字的学生的姓名

（三）分组统计

16、计算所有有选课的学生的总分、平均分、最高分、最低分

17、计算'201215121'选课的总分、平均分、最高分、最低分及选课个数

18、计算选修了'2'号课程的学生的总分、平均分、最高分、最低分及选课人数

19、计算所有有选课的学生的人数

20、统计学生表中男女生的个数

21、统计每个学生选课的门数

22、统计每门课程的选课人数

23、查询选修了两门及以上的学生的学号和选课门数

24、查询选课人数为两个以上的课程的课号和选课人数

25、查询平均成绩大于等于85分的学生学号和平均成绩

26、计算每个学生大于80分的课程的平均成绩，再查询平均成绩大于等于90分的学生学号和平均成绩

（四）排序：

27、查询选修了“2”号课程的学生的成绩，结果按分数降序排列

28、查询前三名学生的成绩信息

29、查询学生表中每一个年龄的学生人数，要求查询结果按人数升序排列，人数相同按年龄降序排列

30、查询平均成绩最好的前三名学生的学号和分数

（五）连接操作：

31、查询每个学生及其选课情况(两个表)

32、查询每个学生选修的课程名、成绩和学号

33、检索学习了"2"号课程的学生的学号和姓名

34、查询选修了‘2’号课程且成绩在90分以上的学生的学号、姓名、

成绩

35、查询“李勇”同学所学课程的课程号、课程名、成绩、学分

36、查询学号为"201215121"的学生在其各科成绩中，最高分成绩所对应的课程名和成绩

37、查询出只选修了一门课程的全部学生的学生号和姓名

38、查询有选课的学生成绩为90分以上的学号、姓名、所选课程的课程号、课程名、成绩、学分

（六）自连接

39、查询与"刘晨"在同一个系的学生姓名（自连接）

40、查询同名学生的学号和姓名（自连接）

（七）子查询：

41、查询选修了“数据库”课程的学生的学号、年龄和姓名

42、查询没有选课的学生信息

43、查询没有选课的课程信息

44、查找'CS'系中年龄最大的学生的学号和年龄

45、查询其他系中所有与"CS"系学生同龄的学生信息

46、检索不学“2”号课程的学生的姓名和年龄（必须用子查询做）

47、查询学生选课中大于自己的平均成绩的课程成绩信息

48、查询与“刘晨”在同一个系学习的学生（子查询）

（八）复杂查询

49、查询同名学生的姓名，并统计同名的人数

50、查询选修了“1”号课程也选修了“2”号课程的学生的学号

51、查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息

52、查询每门课程中成绩最好的前两名的课程号和成绩

（九）视图：

53、建立信息系学生的视图ISSTU_VIEW，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

54、创建“CJ_VIEW”视图，包括学生的学号、姓名、课程号和成绩。

55、建立“CS”系选修了2号课程的学生的视图CJ2_VIEW，包括学号、姓名、课程号和成绩

56、创建“CJ80_VIEW”视图，包括成绩为80分以上的学生的学号、姓名、课程名、成绩和学分。

57、创建视图“PJCJ_VIEW”,包括被选修课程的课程号及平均成绩

（十）存储过程

58、在XSKC数据库中创建无参存储过程STU_PROC，查询学生的个人信息、所选课程和成绩信息

59、在XSKC数据库中创建存储过程UPDATEGRADE，将指定学生选修指定课程的成绩清零，若该生未选修该课程，则输出信息“*****学生未选修**课程！”

60、在XSKC数据库中编写带输入参数的存储过程INSERTSTU，代替