sql server课程设计指导书

PAGEPAGE2SQLServer课程设计指导书长春工业大学人文信息学院[课程设计目的]SQLServer数据库课程设计是计算机类专业的集中实践性环节之一，是课程教学中的一项重要内容，是完成教学计划达到教学目标的重要环节，是帮助学生全面牢固地掌握课堂教学内容、培养学生分析、解决实际问题的能力进行的一次全面的综合练习。通过结合设计题目进行综合性应用，对所学知识达到融会贯通的程度，对提高学生的综合素质具有重要的意义本次课程设计的教学目的：1、加深对数据库基础理论和基本知识的理解。2、掌握数据库设计与使用的基本方法。3、提高运用数据库解决实际问题的能力。[课程设计内容及要求]课程设计的时间为两周时间。题目分为必做和选作两部分，根据题目要求，用T-SQL语句实现各种数据库操作，需要上交课程设计报告。报告内容包括：设计目的设计题目题目的答案（sql语句）设计过程中遇到的问题和心得。目录必做题TOC\o"1-3"\h\z题目一产品销售信息数据库操作 4题目二学生成绩(XSCJ)数据库操作 6题目三职工_社团数据库操作 14题目四图书管理数据库操作 16题目五工程_零件数据库操作 18题目六企业员工信息数据库操作 19选做题学生学籍管理信息系统数据库设计 22题目一产品销售信息数据库操作写出创建产品销售数据库CPXS和表的T-SQL语句：数据库初始大小为10MB，最大大小为100MB，数据库自动增长，按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长；其余参数自定。所包含的表如下：产品表：产品编号，产品名称，价格，库存量销售商表：客户编号，客户名称，地区，负责人，电话产品销售表：销售日期，产品编号，客户编号，数量，销售额（表中数据类型自拟）将所创建的CPXS数据库的增长方式改为按5MB增长将所创建的CPXS数据库的产品表增加“产品简介”列向产品表插入如下数据：0001空调30002000203冰箱25001000301彩电280050将产品数据库的产品表中的每种商品的价格打8折将产品数据库的产品表中价格打8折后小于50的商品删除查找价格在2000~2900元之间的产品名称计算所有产品总价格求指定日期2002年3月18日销售总额求名为“张三”的客户在2002年3月18日所购买的产品名称在产品销售数据库上创建冰箱产品表的视图BXCP在BXCP视图上查询存储量在100台以下的产品编号求各个地区的负责人数基于“产品表”创建AFTERINSERT触发器tr_insert_price，实现新添记录数据时商品的价格限制在10000以内，高于10000的事务回滚。题目二学生成绩(XSCJ)数据库操作创建学生成绩（XSCJ）数据库及其表结构表A.1学生情况表（表名XS）结构列名数据类型长度是否允许为空值默认值说明学号char6否无主键姓名varchar8否无专业名varchar10是1男1女0性别bit1否无出生时间datetime8否无总学分int4是无备注text16是无表A.2课程表（表名KC）结构列名数据类型长度是否允许为空值默认值说明课程号char3否无主键课程名char16否无开课学期tinyint1否1只能为1~8学时tinyint1否无学分tinyint1是无表A.3学生与课程表（表名XS_KC）结构列名数据类型长度是否允许为空值默认值说明学号char6否无外键课程号char3否无外键成绩tinyint1否无学分tinyint1是无2、向学生成绩XSCJ数据的表中插入样本数据学生情况表（表名XS）数据样本课程表（表名KC）数据样本（3）学生与课程表（表名XS_KC）数据样本利用sql语句完成下列数据操作：查询XSCJ数据库的XS表中各个同学的姓名、专业名和总学分查询XS表中计算机专业同学的学号、姓名和总学分查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题分为指定为number,name和mark查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换；若总学分为空值，则显示“尚未选课”；若总学分小于50分则显示为“不及格”；若总学分在50分与52分之间，显示为“合格”；若总学分大于52分，显示为“优秀”。该列标题更改为“等级”查询XS表的专业名和总学分，消除结果集中的重复行查询XS表姓名、专业名和总学分，只返回结果集的前6行查询XS表中通信工程专业总学分大于等于42的同学的情况查询XS表中计算机系的学生情况查询XS表中姓“王”且单名的学生情况查询XS表中不在1979年出生的学生情况查询XS表中专业名为“计算机”、“通信工程”或“无线电”的学生的情况查询总学分尚不定的学生的情况查找选修了课程号为101的课程的学生的情况查找未选修离散数学的学生的情况查找比所有计算机系的学生年龄都打的学生查找课程号206的成绩不低于课程号101的最低成绩的学生的学号查找选修206号课程的学生姓名查找选修了全部课程的同学的姓名查找选修了与学号为001102同学所选修的全部课程的同学的学号在XS表中查找1980年1月1日以前出生的学生的姓名和专业名，分别使用别名stu_name和speciality表示查找每个学生的情况以及选修的课程情况查找选修了206课程且成绩在80分以上的学生姓名及成绩查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名称和成绩查找不同课程成绩相同的学生的学号、课程号和成绩查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况查找被选修了的课程的选修情况和所有开设的课程名称求选修101课程的学生的平均成绩求选修101课程的学号的最高分和最低分求学生的总人数求选修了课程的学生总人数统计离散数学课程成绩在85分以上的人数查询所有专业名称求各专业的学生数求被选修的各门课程的平均成绩和选修改课程的人数在XSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数查找XSCJ数据库总平均成绩在85分以上的学生的学号和平均成绩查找选修课程超过两门且成绩都在80分以上的学生的学号将通信工程专业的学生按出生时间先后排序将计算机专业学生的“计算机基础”课程成绩按降序排列由XS表创建“计算机系学生”表，其中包括学号和姓名创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图中数据的修改都符合专业名为计算机这个条件。创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）使用视图CS_KC查找计算机专业的学生学号和选修的课程号在视图CS_KC_AVG中查找平均成绩在80分以上的学生的学号和平均成绩在XSCJ数据库中创建表XS1和XS2，他们的结构与XS表一样，分别在XS1和XS2的学号列上建立check约束为between‘004001’and‘004050’和between‘003001’and‘003050’并创建视图，语句如下：CreateviewXS12_viewAsSelect*FromXS1UnionallSelect*fromXS2向XS12_view视图中插入一条记录（'004010','蓝源源'，'外语'，0，40）将XS12_view视图中的每个学生的总学分增加10修改41题中的视图，将其定义为：包括学号、姓名、选修的课程、课程名和成绩。定义游标XS_CUR3，游标中存储学号、姓名和总学分，然后打开该游标，输出其行数。创建局部变量，分别赋值为“中国”，“是一个伟大的国家”，然后输出变量的值。创建一个名为sex的局部变量，并在select语句中使用该局部变量查找表XS中所有女同学的学号、姓名。语句如下：useXSCJDeclare@sexbitSet@sex=0Select学号、姓名FromXSWhere性别=@sex运行下面程序，并描述其功能UseXSCJDeclare@CursorVarcursorSet@CursorVar=cursorscrollForSelect学号、姓名FromXSWhere姓名like'王%'Open@CursorVarFetchnextfrom@CursorVarwhile@@fetch_status=0BeginFetchnextfrom@CursorVarEndClose@CursorVarDeallocate@CursorVar查询成绩高于刘丰最高成绩的学生姓名、课程名和成绩查询总学分不在在40~50分的学生学号和姓名查询课程名以C或A开头的课程情况查询所有选课学生的姓名利用T-SQL编程语句实现如下要求，如果“数据库原理”课程的平均成绩高于75分，则输出“平均成绩高于75分”利用T-SQL编程语句实现如下要求，显示字符串“China”中每逢字符的ASCII值和字符定义存储过程，输入任意学生学号，查询该学生的平均成绩，若>75，将返回状态代码1，否则，将返回状态代码2利用字符串函数返回课程名称最左边的8个字符利用case语句显示学生的性别，但值为1时，显示男生，否则显示女生定义函数计算全体学生某门功课的平均成绩(课程号为输入参数)为KC表的课程名列创建唯一索引为XS表的总学分字段设置默认值为0（分别用默认值约束和默认值对象实现）为XS添加“身份证”列，并定义唯一约束题目三职工_社团数据库操作设职工_社团数据库有三个基本表：职工（职工号，姓名，年龄，性别）社会团体（编号，名称，负责人，活动地点）参加（职工号，编号，参加日期）其中：职工表的主码为职工号社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号试用SQL语句表达下列操作：定义职工表，社会团体表和参加表，并说明其主码和参照关系建立下列两个视图社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）参加人情况（职工号，姓名，社团编号，社团名称，参加日期）查找参加唱歌队或篮球队的职工号和姓名查找没有参加任何社会团体的职工情况查找参加了全部社会团体的职工情况查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号求每个社会团体的参加人数求参加人数最多的社会团体的名称和参加人数求参加人数超过100人的社会团体的名称和负责人把对社会团体和参加两个表的数据查看、插入和删除数据的权利赋给用户李平，饼允许他再将此权利授予其他用户编写一个存储过程，该存储过程根据输入的职工号，输出该年龄的职工姓名创建一个名为tr_age的触发器，要求在向职工表插入和更新时检查AGE是否在20到60之间，如不在20到60，则弹出“年龄不合法”提示信息。

题目四图书管理数据库操作设图书管理数据库数据表如下：借书卡card（cno,name,calss），其中：cno为卡号，name为姓名，class为班级图书books（bno，bname，author，price，quantity），其中：bno为书号，bname为图书名，author为作者，price为单价，quantity为库存册数借书记录borrow（cno，bno，rdate），其中：cno为借书卡号，bno为书号，rdate为还书日期约束：限定每人每种书只能借一本；库存册数随借书、还书而变化。用sql语句实现下列操作：1、写出建立borrow表的sql语句，要求定义主键完整性约束和引用完整性约束2、找出借书超过五本的读者，输出借书卡号及所借图书册数3、查找借阅了“水浒”一书的读者，输出姓名及班级4、查找过期未还图书，输出借阅者（卡号）、书号及还书日期5、查询书名包括“网络“关键词的图书，输出书号、书名、作者6、查询现有图书中价格最高的图书，输出书名及作者7、查询当前借了“计算方法“但没有借”计算方法习题集“的读者，输出其借书卡号，并按卡号降序排序输出8、将“C01“班同学所借图书的还书日期延长一周9、从books表中删除当前无人借阅的图书记录10、在borrow表上建立一个触发器，完成如下功能：如果读者借阅的书名是“数据库技术及应用“，就将该读者的借阅记录保存在borrowsave表中（注：borrowsave表结构同borrow表）11、如果经常按书名查询图书信息，请建立合适的索引12、建立一个视图，显示“C01“班学生的借书信息（只要求显示姓名和书名）13、查询当前同时借有“计算方法“和”组合数学“两本书的读者，输出其借书卡号，并按卡号升序排序输出14、假定在建books表时没定义主键，写出为books表追加定义主键的语句15、对card表做如下修改：A）将name最大列宽增加到10个字符（假定原为6个字符）B）为该表增加一列dname（系名），可变长，最大20个字符题目五工程_零件数据库操作设工程_零件数据库中有四个基本表：供应商（供应商代码，姓名，所在城市，联系电话）工程（工程代码，工程名，负责人，预算）零件（零件代码，零件名，规格，产地，颜色）供应零件（供应商代码，工程代码，零件代码，数量）试用sql语句完成下列操作：找出天津市供应商的姓名和电话查找预算在50000~100000元之间的工程的信息，并将结果按预算降序排列找出使用供应商S1所提供零件的工程号码找出工程项目J2使用的各种零件名称及其数量找出上海厂商供应的所有零件号码找出使用上海产的零件的工程名称找出没有使用天津产零件的工程号码把全部红色零件的颜色改为蓝色将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并做其他必要的修改从供应商关系中删除S2的记录，并从供应零件关系中删除相应记录题目六企业员工信息数据库操作数据库介绍：这是一个企业员工信息数据库，主要存储员工信息，薪水以及部门等信息。该数据库包括了员工信息表（emp表）和部门信息表（dept表）。各个表的内容与格式如下：emp表字段名数据类型含义备注empno整型员工编号主键，标识列（初始值为1000，增量为1）ename字符串姓名非空job字符串职位非空mgr整型部门负责人编号hiredate日期聘用日期非空sal浮点薪水非空comm浮点奖金deptno整型部门编号外键dept表字段名数据类型含义备注deptno整型部门编号主键标识列（初始值为10，增量为10）dname字符串部门名称非空Loc字符串地址利用T-sql语句创建数据库orcl，建立一个主数据文件orcl.mdf，一个次数据文件orcl1.ndf，以及一个日志文件orcl_log.ldf，各文件的初始大小为20M，可以自增，每次增长5M。根据上述两表的结构，用T-sql语句创建表。（根据实际情况确定数据类型和字段长度，按照备注中的要求定义约束条件）向emp表添加一个字段sex（性别），字符串类型为sex字段添加检查约束，要求该字段只能取值为男或者是女添加一个用户自定义的数据类型名字为“工资”，该字段数据类型与sal和comm列数据类型一致，并将sal和comm改为该类型为hiredate列添加默认值约束，默认值为当前时间向emp表插入以下数据：（张三，经理，null，1982-12-7，800，null，20）（王新，销售员，1001，1981-2-20，1600，300，30）（李立，销售员，1001，1981-2-22，1250，500，30）向dept表插入以下数据：（销售部，北京）（会计部，北京）（生产部，上海）查询部门编号为20的员工信息查询徐偶有销售员的姓名、编号和部门名称找出奖金高于薪水60%的员工找出部门20中所有销售员或者部门30中的经理找出有奖金的员工的职位，去掉重复行找出10年前受雇佣的员工信息查找名字为两个汉字的员工信息查找不姓“张”的所有员工信息显示所有员工的姓名、职位和薪水，按职位的降序排序，职位相同时按照薪水和奖金的和升序排序显示所有雇员的姓名和加入公司的年份和月份，按照年份降序排序，同一年的按照月份升序排序找出在（任何年份的）2月受聘的员工信息计算出每个员工加入公司的天数查询至少有一个员工的部门信息查询比“张三”薪水高的所有员工信息列出所有雇员的姓名及其直接上级的姓名列出入职日期早于其直接上级的所有雇员列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门列出各种职位的最低薪水，显示最低薪水大于1500的记录列出薪水高于公司平均水品的所有雇员列出与“张三”从事相同工作的所有雇员列出某些雇员的姓名和薪水，条件是他们的薪水等于部门30中任何一个雇员的薪水列出某些雇员的姓名和薪水，条件是他们的薪水高于部门30中所有雇员的薪水列出每个部门的信息以及该部门中雇员的数量，即使是分配有0个雇员列出从事同一种工作但属于不同部门的雇员的不同组合列出各种类别工作的最低工资列出按年薪排序的所有雇员的年薪创建一个视图，存放“北京”的员工信息。

学生学籍管理信息系统数据库设计学生学籍管理工作包括学生档案、学籍、成绩、升学等内容的管理；面对大量的学生数据、报表，手工处理方式已经很难跟上现代化的步伐。随着计算机技术及网络通讯技术的飞速发展，许多学校已经有了较好的计算机应用甚至网络硬件建设基础。因此为提高学校管理工作的现代化、科学化水平，保证信息处理的即时化、准确化，开发一套对学生学籍进行管理的软件是极其重要的。应完成的主要功能：（1）学生档案的管理，即录入、修改、查询、输出学生档案信息，这些信息包括学生基本情况、学生简历情况、学生奖励情况、学生处分情况、学生家庭信息、学生体检情况。（2）学生学籍管理，录入、修改、查询、输出学生学籍信息，这些信息包括学生奖贷学金情况、学生注册、学生异动情况、学生军训情况、学生毕业情况。（3）学生成绩管理，录入修改、查询、输出学生入校成绩，各学期、各门课程的成绩信息，并支持按年级、班级等条件的统计、查询、报表输出。《学生学籍管理信息系统》功能需求参考按系汇总各专业各年级的学生总数及其中的男、女生数，并统计全校总人数。要求：当任意输入系部名称、专业名称和年级均可返回相应信息。按系汇总各年龄段的学生人数及其中的男、女生数。要求：当任意输入系部名称、年龄时，均可返回相应信息。按系统计各地区的学生人数。要求：当任意输入系部名称、地区名称时，均可返回相应学生人数。按任一学期（用户输入）统计学生变更数据（如：复学、休学、退学、开除、转入、转出、毕业、肄业等）。按系统计学生中的党员、团员人数。要求：当任意输入系部名称时，均可返回该系的学生党员和团员人数。按系统计各民族的学生人数。要求：当任意输入系部名称、民族时，均可返回相应信息。按学年根据学生变更数据自动修改班级总人数（提示：用触发器实现）、系部总人数、学校总人数。统计各门课程的成绩分布情况：其中：(90-100、80-90、70-80、60-70、0-59)的人数及各自占实际考试人数的百分比数。统计缺考人数。统计及格、不及格人数。统计每个学生的综合测评总成绩：总成绩=平均分*0.7+德体分*0.3综合测评的排名。根据总成绩评定奖学金等级（特等、一等、二等、三等）。按学年根据