SQL-SERVER上机考试题

泉州理工学院2015-2016第二学期期末考Sqlserver数据库管理泉州理工职业学院2015-2016学年第二学期期末考SQLSERVER数据库管理上机操作题班级：学号姓名成绩说明：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）泉州理工学院2015-2016第二学期期末考Sqlserver数据库管理四、使用语句向数据库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查看各个专业所有课程的平均成绩，结果格式为：专业平均成绩…………六、数据库关系图泉州理工学院2015-2016第二学期期末考Sqlserver数据库管理创建视图D_BASE七、以下题目均在JYxxxx（学生管理）数据库中完成。并写出操作SQL语句（一）简单查询1.查询StudentInfos表中各个同学的姓名、生日和性别。答：selectS_name,S_birth,S_sexfromStudentInfos2.查询StudentInfos表中所有同学的学号、姓名，结果中各列的标题分别指定为num，name。答：selectS_idnum,S_namename,C_creditmarkfromStudentInfos3.查询StudentInfos表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。selectDISTINCTS_majorfromStudentInfos答“4.查询StudentInfos表中专业为“计算机”的男学生信息。答：select5.查询年龄最小的前三个同学的姓名、专业和生日。答：（二）数据汇总1.求专业为“计算机”的学生的总人数。答selectcount(*)fromXSwhere专业='计算机'2.求选修了“101”课程的学生的人数。答：selectCOUNT(*)fromXS_KCwhere课程号='101'3.统计各个专业的学生数。（按专业分组）答：select专业,性别,count(*)as人数fromXSgroupby专业,性别4.统计各个专业的男女生人数。格式如下：专业性别人数………………答：（三）连接查询1.查询每个学生的基本情况及选修的课程情况，格式如下：学号姓名专业课程号成绩成绩等级(=90A；=80B;=70C；=60D;60D)………………………………答：2.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。答：selectxs.学号,xs.姓名,xs_kc.成绩fromxsjoinxs_kconxs.学号=xs_kc.学号joinkconxs_kc.课程号=kc.课程号andxs.专业='计算机'andkc.课程名='计算机基础'3.显示每门课程的平均成绩并显示总平均成绩。格式如下：课程名平均成绩…………泉州理工学院2015-2016第二学期期末考Sqlserver数据库管理总平均……答：selectCASEWHEN(GROUPING(a.课程名)=1)THEN'总平均'ELSEa.课程名ENDAS课程名,avg(b.成绩)as'平均成绩'fromkcajoinxs_kcbona.课程号=b.课程号groupbya.课程名WITHrollup4.查询各个专业各门课程的平均成绩。格式如下专业课程名平均成绩………………答：selecta.专业,c.课程名,avg(b.成绩)as'平均成绩'fromxsajoinxs_kcbona.学号=b.学号joinkcconb.课程号=c.课程号groupbya.专业,c.课程名5.查询“计算机”专业平均成绩在85分以上的学生的学号、姓名和平均成绩。答：selectb.学号,a.姓名,avg(成绩)as'平均成绩'fromxsajoinxs_kcbon(a.学号=b.学号)and(a.专业='计算机')groupbyb.学号,a.姓名havingavg(成绩)856.查找平均成绩在80分以上的学生的学号和平均成绩。答：select学号,AVG(成绩)平均成绩fromXS_KCgroupby学号havingAVG(成绩)80（四）子查询的使用1.查询平均成绩高于101号课程平均分的课程号及平均成绩。答：select课程号,avg(成绩)'平均成绩'fromxs_kcgroupby课程号havingavg(成绩)(selectavg(成绩)fromxs_kcgroupby课程号having课程号='101')2.查找未选修“C语言”课程的学生的情况。(嵌套子查询)答：select*fromxswhere学号notin(select学号fromxs_kcwhere课程号in(select课程号fromkcwhere课程名='C语言'))3.查找选修了“计算机基础”课程的学生的情况。答：select*fromxswhere学号in(select学号fromxs_kcwhere课程号=(select课程号fromkcwhere课程名='计算机基础'))4.查询“计算机基础”成绩及格的学生的学号、姓名、性别和专业情况。答：select学号,姓名,性别,专业fromxswhere学号in(select学号fromxs_kcwhere成绩=60and课程号=(select课程号fromkcwhere课程名='计算机基础'))5.查询至少选修了“4102102”同学选修课程的学生的基本信息。答：select*fromxswhere学号in(select学号fromxs_kcwhere课程号='4102102')（五）数据的插入、删除和修改1.在StudentInfos添加一条自己的信息，并选择数据库课程，以及给自己打分。答：2.将StudentCourseInfos表中学号为“4102101”的同学的“计算机基础”课程的成绩增加10分。泉州理工学院2015-2016第二学期期末考Sqlserver数据库管理答：updateStudentCourseInfosset成绩=成绩+10where学号='4102101'3.将StudentCourseInfos表中“软件工程”课程的成绩不及格的信息删除。答：（六）SQL基础语法随机求班上的一个学生的学号。要求：1、最终显示学生的学号格式是：152011XX2、随机出来的学号，必须是班上存在的学生。排除休学的学生。答：