SQL-SERVER上机考试题.doc

泉州理工学院2015-2016第二学期期末考泉州理工职业学院2015-2016学年第二学期期末考SQL SERVER数据库管理上机操作题班级： 学号 姓名 成绩 说明：1、五答题的题目的答案直接写在相应题目后面，其余的内容直接保存在数据库中。2、提交的内容包括数据库文件还有该WORD文档。统一建立一个文件夹以学号+姓名方式命名。题目：一、 在指定的服务器上创建数据库JYxxxx（其中xxxx为自己的学号，创建方法和数据库参数自定）。二、 在JYxxxx数据库中创建如下的表结构：1学生情况表（StudentInfos）结构 列名说明数据类型长度可否为空默认值说明S_id学号Char7否无主键S_name姓名Char8否无S_major专业VarChar20否无S_sex性别Bit否1男=1，女=0S_ birth出生日期smalldatetime否无2课程表（CourseInfos）结构 列名说明数据类型长度可否为空默认值说明C_id课程号Char3否无主键C_name课程名Char20否无C_credit学分Tinyint否03学生与课程表（StudentCourseInfos）结构 列名说明数据类型长度可否为空默认值说明S_id学号Char7否无外键（参照学生表中的学号）C_id课程号Char3否无外键（参照课程表中的课程号）Sc_ grade成绩Tinyint是00成绩100三、 数据完整性1、 在StudentInfos中，要求性别只能输入0或1。2、 在CourseInfos中，要求学分不能小于1，且不能大于103、 在StudentCourseInfos中，要求成绩分数在0到100之间（包含0和100）四、 使用语句向数据库JYxxxx的StudentInfos表、CourseInfos表和StudentCourseInfos表中插入或修改成如下数据： 学生情况表（StudentInfos）学号姓名性别专业出生日期4102101王林男计算机1983-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）学号课程号成绩410210110180410210110289410210110378410210210157410210210267420210310390420211010185420211010291420222110169440213010178440213010374五、 视图操作1创建视图V_SCORE1，使其显示计算机专业学生的所有基本信息（性别用中文标识，例如：1则显示男），并保证以后对该视图的修改都要满足专业为“计算机”这个条件。2创建视图V_SCORE2，通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：专业 平均成绩 六、 数据库关系图创建视图D_BASE 七、 以下题目均在JYxxxx（学生管理）数据库中完成。并写出操作SQL语句（一） 简单查询 1. 查询StudentInfos表中各个同学的姓名、生日和性别。答：select S_name,S_ birth,S_sex from StudentInfos2. 查询StudentInfos表中所有同学的学号、姓名，结果中各列的标题分别指定为num，name。答：select S_id num, S_name name, C_credit mark from StudentInfos3. 查询StudentInfos表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。select DISTINCT S_major from StudentInfos答“4. 查询StudentInfos表中专业为“计算机”的男学生信息。答：select 5. 查询年龄最小的前三个同学的姓名、专业和生日。答：（二） 数据汇总1. 求专业为“计算机”的学生的总人数。答 select count(*) from XS where 专业=计算机2. 求选修了“101”课程的学生的人数。答：select COUNT(*) from XS_KC where 课程号=1013. 统计各个专业的学生数。（按专业分组）答：select 专业,性别,count(*) as 人数 from XS group by 专业,性别4. 统计各个专业的男女生人数。格式如下：专业性别人数 答： （三） 连接查询1. 查询每个学生的基本情况及选修的课程情况，格式如下：学号姓名专业课程号 成绩 成绩等级(=90A；=80B;=70C；=60D;856. 查找平均成绩在80分以上的学生的学号和平均成绩。答： select 学号,AVG(成绩) 平均成绩 from XS_KC group by 学号 having AVG(成绩)80（四） 子查询的使用1. 查询平均成绩高于101号课程平均分的课程号及平均成绩。答：select 课程号,avg(成绩) 平均成绩 from xs_kc group by 课程号 having avg(成绩)(select avg(成绩) from xs_kc group by 课程号 having 课程号=101 )2. 查找未选修“C语言”课程的学生的情况。(嵌套子查询)答：select * from xs where 学号 not in( select 学号 from xs_kc where 课程号 in (select 课程号 from kc where 课程名=C语言 )3. 查找选修了“计算机基础”课程的学生的情况。答：select * from xs where 学号 in (select 学号 from xs_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分。答：update StudentCourseInfos set 成绩=成绩+10 wh