SQL-SERVER上机考试题

1、泉州理工学院2015-2016第二学期期末考SQL-SERVE比机考试题泉州理工职业学院2015-2016学年第二学期期末考SQL SERVER数据库管理上机操作题班级：学号 姓名 成绩说明：1、五答题的题目的答案直接写在相应题目后面，其 余的内容直接保存在数据库中。2、提交的内容包括数据库文件还有该WORD：档。统一建立一个文件夹以学号+姓名方式命名。题目：1、 在指定的服务器上创建数据库 JYxxxx (其中xxxx为自己的学号，创建方法和数据库参数自定)。2、 在JYxxxx数据库中创建如下的表结构：1 .学生情况表(Studentinfos )结构列名说 明数据类型长 度可 否 为 空

2、默 认 值说明S_id学 号Char7否无主键S_name姓 名Char8否无S_major专业VarChar20否无S_sex性 别Bit否1男二1, 女=0S_出smalldatetime否无birth生日期2.课程表(Courseinfos )结构列名说明数据类 型长 度可 否 为 空默认 值说明C_id课程 号Char3否无主键C_name课程 名Char20否无C_credit学分Tinyint否03.学生与课程表(StudentCourseInfos )结构列名说明数据类 型长 度可否 为空默认 值说明S_id学号Char7否无外键(参 照学生表中的学 号）C_id课程 号Char

3、3否无外键（参 照课程表 中的课程 号）Sc_ grade成绩,Tinyint是00W成绩<1003、 数据完整性1、在Studentinfos 中，要求性别只能输入 0或1。2、在Courseinfos中，要求学分不能小于 1,且不能大于103、在StudentCourseinfos 中，要求成绩分数在 0到100之间（包含0和100）4、 使用语句向数据库 JYxxxx的Studentinfos 表、Courseinfos表和StudentCourseinfos 表中插入或修改成如下数据：学生情况表（Studentinfos ）学号姓名性别专业出生日期4102101王林男计算机198

4、3-1-234102102吴荣华女计算机1984-3-284202103张强男电子1981-11-194202110王向荣男电子1980-12-94202221李丽女电子1982-7-304302101刘明男自控1982-10-184402130叶凡男数学1983-11-18课程表( Courseinfos )课程号课程名学分101计算机基础3102C语言5103数据库4201离散数学4202计算机网络4301软件工程4学生与课程表( StudentCourseInfos )学号课程号成绩41021011018041021011028941021011037841021021015741021

5、0210267420210310390420211010185420211010291420222110169440213010178440213010374五、视图操作1 .创建视图V_SCORE 1使其显示计算机专业学生 的所有基本信息（性别用中文标识，例如：1则显 示男），并保证以后对该视图的修改都要满足专业 为“计算机”这个条件。2 .创建视图V_SCORE2通过V_SCORE2看各个 专业所有课程的平均成绩，结果格式为：专业平均成绩六、数据库关系图创建视图D_BASE七、以下题目均在JYxxxx （学生管理）数据库中完 成。并写出操作SQL语句Sqlserver数据库管理泉州理工学院

6、 2015-2016 第二学期期末考(一 简单查询1. 查询 StudentInfos 表中各个同学的姓名、 生日和性别。答： select S_name , S_ birth , S_sex from StudentInfos2. 查询 StudentInfos 表中所有同学的学号、姓名，结果中各列的标题分别指定为num， name。答： select S_id num, S_name name, C_creditmark from StudentInfos3. 查询 StudentInfos 表中的学生数据来自哪些专业(使用DISTINCT子句消除结果集中的重复行)select DISTI

7、NCT S_major from StudentInfos答“4. 查 询 StudentInfos 表中专业为 “计算机” 的男学生信息。答： select5. 查 询年龄最小的前三个同学的姓名、 专业和生日。答：数据汇总1. 求 专业为“计算机”的学生的总人数。答 select count (*) from XS where 专业 =' 计算机 '2. 求 选修了“ 101”课程的学生的人数。答： select COUNT(*) from XS_KC where 课程号 ='101'3. 统 计各个专业的学生数。 (按专业分组)答： select 专业 ,

8、性别 , count (*) as 人数 from XS group by 专业 , 性别4. 统 计各个专业的男女生人数。格式如下：专业 性别 人数答：连接查询1. 查询每个学生的基本情况及选修的课程情况，格式如下：学号 姓 名 专 业 课 程号成绩 成绩等级(>=90A； >=80B;>=70C； >=60D;<60D)答：2. 查 询 “计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。答： select xs . 学号 , xs . 姓名 , xs_kc . 成绩 from xs join xs_kc on xs . 学号 =xs_kc 学号

9、 join kc on xs_kc . 课程号 =kc . 课程号 and xs . 专业 =' 计算机 ' and kc . 课程名 =' 计算机基础'3. 显 示每门课程的平均成绩并显示总平均成绩。格式如下：课程名 平均成绩 总平均答：selectCASE WHEN （ GROUPING（ a. 课程名 ） = 1 ） THEN ' 总平均 ' ELSE a. 课程名 END AS 课程名 , avg （ b . 成绩 ） as ' 平均成绩 ' from kc a join xs_kc b on a. 课程号=b.课程号gr

10、oup by a.课程名 WITH rollup4. 查 询各个专业各门课程的平均成绩。格式如下专业 课程名 平均成绩答： select a . 专业 , c. 课程名 , avg （ b . 成绩 ） as ' 平均成绩 ' from xs a join xs_kc b on a. 学号 =b. 学号 join kc c on b . 课程号 =c. 课程号 group by a. 专业 , c. 课程名5. 查 询“计算机” 专业平均成绩在85 分以上的学生的学号、姓名和平均成绩。答： select b . 学号 , a . 姓名 , avg （ 成绩 ） as '

11、 平均成绩 ' from xs a join xs_kc b on （ a . 学号 =b . 学号 ） and （ a . 专业 =' 计算机 ' ） group by b . 学号 , a . 姓名 having avg （ 成绩 ） 856. 查 找平均成绩在80 分以上的学生的学号和平均成绩。答： select 学号 , AVG（ 成绩 ） 平均成绩 from XS_KC group by 学号 having AVG（ 成 绩 ） 80子查询的使用1 . 查询平均成绩高于 101 号课程平均分的课程号及平均成绩。答： select 课程号 , avg （ 成绩

12、） ' 平均成绩 ' from xs_kc group by 课程号 havingavg （ 成绩 ）（ select avg （ 成绩 ） from xs_kc group by 课程号having 课程号 ='101'）2 .查找未选修“ C语言”课程的学生的情况。（嵌套 子查询 ）答：select* fromxs where 学号not in（ select学号from xs_kc where课程号 in（ select课程号from kc where课程名 ='C 语言 '）3. 查 找选修了“计算机基础”课程的学生的情况。答：selec

13、t* fromxs where 学号in （ select 学号fromxs_kc where 课程号=（ select 课程号 from kc where 课程名 =' 计算机基础' ）4. 查 询“计算机基础”成绩及格的学生的学号、姓名、性别和专业情况。答： select 学号 , 姓名 , 性别 , 专业 from xs where 学号 in （ select 学号 from xs_kc where 成绩 >= 60 and 课程号 =（ select 课程号 from kc where 课程名 =' 计算机基 础' ）5. 查 询至少选修了“4102102” 同学选修课程的学生的基本信息。答： select * from xs where 学号 in （ select 学号 from xs_kc where 课程号 ='4102102'）数据的插入、删除和修改1. 在 StudentInfos 添加一条自己的信息， 并选择数据库课程，以及给自己打分。答：2. 将 StudentCourseInfos 表中学号为“ 4102101”的同学的“计算机基础”课程的成绩增加 10 分。答： upd