SQL数据库课程设计实训报告-2

SQL数据库课程设计实训报告（2011~2012）学年第2学期）姓名：学号：专业：班级：指导教师：

SQL数据库实训报告日期2012.6.18-2012.6.22地点科技楼205项目名称SQL数据库课程设计实训目的能力目标能够熟练掌握并灵活运用SQLServer2005软件，初步具备开发有一定使用价值数据库应用系统能力。实训要求：1、实训期间每天登记考勤，作为实训成绩的一部分。2、实训期间不能玩游戏、看电影、小说等做与学习无关的事。如果有不遵守的同学将取消当天成绩。3、完成整个实训以后，要求每人写出实训报告。作为评定整个实训成绩的重要参考依据。实训内容（过程及步骤）2、使用T-SQL语句，在数据库student中创建如下student表.（注：要求表字段取英文名）createtablestudent(S_nochar(11)notnullprimarykey,S_namechar(8)notnull,S_sexchar(2)nullcheck(S_sex='男'orS_sex='女')default('男'),S_brithdatetimenulldefault(getdate()),S_addressvarchar(30)null,S_classvarchar(10)null,)INSERTstudentVALUES('20021003011','刘晶晶','女','1985-4-14','湖南株洲','信息')INSERTstudentVALUES('20021003016','周天','男','1984-4-20','广东广州','信息')INSERTstudentVALUES('20021003012','张以能','女','1985-10-2','湖南长沙','信息')INSERTstudentVALUES('20021003013','曾建桥','男','1984-1-22','湖南株洲','信息')INSERTstudentVALUES('20031201001','王玉梅','女','1986-5-18','湖南株洲','电商')INSERTstudentVALUES('20031001001','李婷','女','1986-6-10','湖北武汉','软件')INSERTstudentVALUES('20021001001','谭桂香','女','1984-7-25','湖南长沙','软件')3、使用T-SQL语句，在数据库student中创建如下course表.（注：要求表字段取英文名）（10分）CREATETABLEcourse(c_novarcharNOTNULL,c_namevarcharNOTNULL,c_creditCHAR(2)NOTNULL,c_typeCHAR(10)NOTNULL)INSERTcourseVALUES('1203dzswzjs','电子商务网站建设','4','专业课')INSERTcourseVALUES('1002sjkyl','数据库原理与应用','4','专业基础课')INSERTcourseVALUES('1003xmkf','项目开发','2','专业课')INSERTcourseVALUES('1003c#_w','C#的windows程序开发','4','专业课')4、使用T-SQL语句，在数据库student中创建如下score表.（注：要求表字段取英文名）（10分）CREATETABLEscore(s_nochar(11)notnull,semesterchar(8)notnull,c_novarchar(12)notnull,gradedecimal(5))INSERTscoreVALUES('20031201001','200401','1203dzswwzjs','98')INSERTscoreVALUES('20031001001','200401','1002sjkyl','96')INSERTscoreVALUES('20021001001','200402','1003xmkf','87')INSERTscoreVALUES('20021003011','200402','1003c#_w','92')INSERTscoreVALUES('20021003016','200402','1003c#_w','76')INSERTscoreVALUES('20021003012','200402','1003c#_w','83')INSERTscoreVALUES('20021003013','200402','1003c#_w','87')5．在student表中添加一个长为20个字符，名为S_major的类型为CHAR的列：altertablestudentadds_majorchar(20)6、修改学生“周天”的家由“广东广州”搬到“湖南株洲”。updatestudentsetS_address='湖南株洲'whereS_name='周天'7． 将班级为“20021003”课程号为“1003c#_wupdatescoresetgrade=75whereleft(S_no,8)='20021003'andC_no='1003c#_w'8． 查询全体学生的姓名、学号、所在班级。selectS_no,S_name,S_classfromstudent9． 查询全体学生的姓名及其年龄。selectS_name,year(getdate())-(year(S_brith))年龄fromstudent10．查询全体学生的学号、姓名和年龄，同时以汉字标题来表示学号、姓名和年龄。selectS_no学号,S_name姓名,year(getdate())-(year(S_brith))年龄fromstudent11、查询学号为’20021003010’考试成绩80分以上的学生学号、课程号、学期和成绩并显示汉字标题。selectS_no学号,C_no课程号,semester学期,grade成绩fromscorewhereS_no='20021003010'andgrade>8012．查询年龄在18至22岁之间的学生的S_name（姓名）、S_class（班级）、和Nl（年龄不是基本表中的字段，是计算出来的字段）。selectS_name姓名,S_class班级,year(getdate())-year(S_brith)N1fromstudentwhere(year(getdate())-year(S_brith))>18and(year(getdate())-year(S_brith))<2213．查询家庭地址为“湖南株洲”和“湖南长沙”班学生的详细信息。select*fromstudentwhereS_address='湖南株洲'orS_address='湖南长沙'14．查询缺少成绩的学生的学号和相应的课程号。selectS_no,C_nofromscorewheregrade=null15．查询所有选修过课程的学生的学号。selectS_nofromscorewhereC_noisnotnull16．查询课程号为“1003c#_w”的成绩为前三名的学生的学号和成绩。selecttop3S_no,gradefromscorewhereC_no='1003c#_w'orderbygradedesc17．查询选修了“1003c#_w”课程的学生的学号及其成绩，查询结果按分数的降序排列。selectS_no,gradefromscorewhereC_no='1003c#_w'orderbygradedesc18．查询’20021003’班各门课程最高成绩，并显示最高成绩大于80的课程号和最高成绩。selectC_no,max(grade)fromscorewhereleft(S_no,8)='20021003'groupbyC_nohavingmax(grade)>8019、查询每个学生的S_no（学号）、S_name（姓名）、S_class（班级）及其所选修课程的成绩情况。selectstudent.S_no,S_name,S_class,gradefromstudent,scorewherestudent.S_no=score.S_noandC_noisnotnull20．查询比“王玉梅”年龄大或同龄的学生的学号、姓名和出生年份，结果按出生年月升序排列。selectS_no,S_name,S_brithfromstudentwhere(year(getdate())-year(S_brith))>=(select(year(getdate())-year(S_brith))fromstudentwhereS_name='王玉梅')orderbyS_brith21、查询与“刘晶晶”在同一个班学习的学生。select*fromstudentwhereleft(S_no,8)='20021003'22．查询其他班级中比“信息021”select*fromstudentwhere(year(getdate())-year(S_brith))>(selectmin(year(getdate())-year(S_brith))fromstudentwhereS_class='信息')23．查询所有选修了“1003c#_w”课程的学生S_no（学号）和S_name（姓名）。selectstudent.S_no,S_namefromstudent,scorewherestudent.S_no=score.S_noandC_no='1003c#_w'24、查询选修了课程名为“数据库原理与应用”的学生学号和姓名。selectstudent.S_no,S_namefromstudent,score,coursewherestudent.S_no=score.S_noandscore.C_no=course.C_noandC_name='数据库原理与应用'25、建立关于student表的s_no列的聚集索引。createuniqueclusteredindexstu_indexonstudent(S_no)26．建立关于course表的c_no列的惟一非聚集索引。createuniqueindexcou_indexoncourse(C_no)27．建立关于score表的s_no列和c_no列的复合非聚集索引。createnonclusteredindexsco_indexonscore(C_no)28．创建一个关于学生成绩的视图，要求含有学号，姓名，课程号，课程名和成绩列。createviewview1asselectscore.S_no,S_name,C_no,gradefromstudent,scorewherestudent.S_no=score.S_no29．创建关于学生信息的视图stu_info_view，使之仅包含学生的学号、姓名和性别等基本信息。createviewstu_info_viewasselect*fromstudent30．从视图grade_view中查询达到80分以上的成绩。createviewgrade_viewasselectgradefromscorewheregrade>=8031．通过视图stu_info_view向表student中插入一条记录。INSERTINTOstu_info_viewvalues('20031201002','王红','女','1987-3-2','湖南长沙','电商','')32、更新s_no为“20031001002”的学生的姓名为“王勇”updatescoresetgrade=grade+2fromstudent,scorewherestudent.S_no=score.S_noandS_name='王勇'andstudent.S_no='20031001002'33、创建一存储过程，检索信息021班学生的记录。createprocedureproc1asselect*fromstudentwhereS_class='信息'34、创建一存储过程GetCredit，通过用户输入课程号，输出学分。创建：createprocedureGetCredit@课程号char(11),@学分char(2)outputasset@学分=(selectC_creditfromcoursewhereC_no=@课程号)print@学分查询：declare@课程号char(11),@学分char(2)set@课程号='1003c#_w'execGetCredit@课程号,@学分创建关于性别的默认，默认值为‘男’，并将其绑定到数据表student的s_sex列上。创建默认值：createdefaultsex_defas'男'绑定默认值：execsp_bindefault'sex_def','student.S_sex'为数据表score的grade列设置CHECK约束，使grade列的值在0~100之间。altertablescoreaddconstraintchecheck(grade>=0andgrade<=100)为数据表score时，为s_no和c_no设置PRIMARYKEY约束和FOREIGNKEY约束。ALTERTABLEscoreADDCONSTRAINTPK_SCORE_SNOPRIMARYKEY(S_no)ALTERTABLEscoreADDCONSTRAINTPK_SCORE_CNOFOREIGNKEY(C_no)38、为数据表score创建一个UPDATE触发器。当试图修改数据表score中的记录时，检查修改后记录中的s_no（学号）是否在数据表information存在，同时c_no（课程号）是否在数据表course中存在，若不是同时存在，则撤消UPDATE操作，并返回一条错误消息。createtriggertrionscoreafterupdateasdeclare@学号char(11),@s_nochar(11),@课程号varchar(12),@c_novarchar(12)select@学号=S_nofrominsertedselect@s_no=S_nofromstudentselect@课程号=C_nofrominsertedselect@c_no=C_nofromcoursebeginifnotexists(select*frominsertedwhereS_noin(selectS_nofromstudent))beginraiserror('学号不存在!',16,1)rollbackend