数据库期中考试
--期中考试试卷
--一、用Transact-SQL语句定义数据库
--1、创建名为School的数据库，该数据库包含二个大小为2MB的数据文件（文件名为：<你的姓名>_school_dat1和<你的姓名>_
--school_dat2）和一个大小为1MB的事务日志文件（文件名为：<你的姓名>_school_log）。限制数据文件大小为5MB、日志文
--件为2MB。对于所有文件，允许20%的文件增长。请将创建数据库的Transact-SQL脚本存为<你的姓名>_1_01.sql文件。
create database School
on
( name=hzw_school_dat1,
filename='D:\School\dat1.mdf',
size=2MB,
maxsize=5MB,
filegrowth=20%),
( name=hzw_school_dat2,
filename='D:\School\dat2.ndf',
size=2MB,
maxsize=5MB,
filegrowth=20%)
log on
( name=hzw_school_log,
filename='D:\School\log.ldf',
size=1MB,
maxsize=2MB,
filegrowth=20%)
go
--2、修改数据文件<你的姓名>_school_dat1，增加其大小至3MB,并允许按0.5MB(512KB)的增量增至最大大小6MB；
--删除数据文件<你的姓名>_school_dat2；添加一个新的1MB的数据文件（文件名为：<你的姓名>_school_dat3）,
--允许它以1MB增量增至磁盘被充满。请将Transact-SQL脚本存为<你的姓名>_1_02.sql 文件。
alter database School
modify file
( name=hzw_school_dat1,
size=3 MB,
maxsize=6 MB,
filegrowth=512 KB
go
alter database School
remove file hzw_school_dat2
go
alter database School
add file
( name=hzw_school_dat3,
filename='D:\School\hzw_school_dat3.ndf',
size=1 MB,
filegrowth=1 Mb
)
go
--二、用Transact-SQL语句在School数据库中定义表
--1.根据所给的学生表建立表名为<你的姓名>_student的表,T-SQL脚本存为<你的姓名>_2_01.sql文件。要求：
--设置学号列数据类型为char（11），主键约束，约束名为pk_xh；
--设置身份证号码列唯一约束，约束名为un_xh；
--设置检查约束，名为ck_sfzid,条件为身份证号码列只能输入15或17个数字字符。
--其它列根据学生表的数据定义。
use School
go
create table hzw_student
( SXH char(11) CONSTRAINT PK_xh PRIMARY KEY NONCLUSTERED,
SMZ char(10) ,
SSFZHM char(20) NOT NULL CONSTRAINT un_xh UNIQUE NONCLUSTERED
CONSTRAINT ck_sfzid check(len(SSFZHM)<18 and len(SSFZHM)>14 ),
SJG varchar(12) NOT NULL,
SJTZZ varchar(20) NOT NULL,
SDHHM varchar(7) NOT NULL,
STC varchar(20) NOT NULL,
SJL varchar(20),
SCF varchar(20)
)
go
--2.根据所给的课程表用Transact-SQL语句建立表名为<你的姓名>_course的表, Transact-SQL脚本存为<你的姓名>_2_02.sql文件。要求：
--设置课程代号列数据类型为char(5)，主键约束，约束名为pk_kcdh。
--设置周学时列默认值为3，学分默认值为2。
--设置检查约束，约束名为ck_cc,条件为周学时数大于等于学分数。
--在<你的姓名>_course表的课程名列上创建一个非聚集索引inx_course_kcm。
--其它列根据课程表的数据定义。
use School
go
create table hzw_course
( CKCDH varchar(5) CONSTRAINT pk_kcdh PRIMARY KEY NONCLUSTERED,
CKCM varchar(20),
CZXS int default '3',
CXF int default '2',
CONSTRAINT ck_cc check(CZXS>CXF or CZXS=CXF),
)
create nonclustered index inx_course_kcm on hzw_course(CKCM)
go
--3.根据所给的成绩表建立表名为<你的姓名>_grade的表，Transact-SQL脚本存为<你的姓名>_2_03.sql文件。要求：
--参考学生表和课程表的设置，考察并设置成绩表的主键约束，约束名为pk_id。
--设置学号列外键约束并级联删除和级联更新，约束名为fk_xh。
--设置课程代号列外键约束并级联删除和级联更新，约束名为fk_kcdh。
--设置平时和期中列的默认值为0。
--其它列根据课程表的数据定义。
use School
go
create table hzw_grade
( GXH char(11) NOT NULL,
GKCDH varchar(5) NOT NULL ,
GPS int default '0',
GQZ int default '0',
GQM int NOT NULL,
CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED (GXH ,GKCDH),
CONSTRAINT fk_xh FOREIGN KEY (GXH) REFERENCES hzw_student(SXH)on delete cascade on update cascade ,
CONSTRAINT fk_kcdh FOREIGN KEY (GKCDH) REFERENCES hzw_course(CKCDH) on delete cascade on update cascade
)
go
--4.修改表的定义，Transact-SQL脚本存为<你的姓名>_2_04.sql文件。要求：
--删除<你的姓名>_student表的特长列；添加出生日期列，允许为空，并要求输入的值不能大于当前日期。
--新建<你的姓名>_grade表的ck_cj约束，要求平时、期中和期末的平均成绩不大于100。use School
go
alter table hzw_student
drop column STC
alter table hzw_student
add SCSRQ varchar(20) NULL check(SCSRQ alter table hzw_grade add constraint ck_cj check(GPS<100 or GPS=100 and GQZ<100 or GQZ=100 and GQM<100 or GQM=100) go --三、用Transact-SQL语句在School数据库中操作表 --1.将附表所提供给的学生表（特长列已删除，增加了出生日期列）、课程--表和成绩表的数据插入到已建的相应表中。Transact-SQL脚本存为<你--的姓名>_3_01.sql文件。 insert hzw_student values('9607039','张昆','530120169021101','安徽','江岸小区45幢3单元408','5033226','被评为三好学生','','') insert hzw_student values('9907002','李明','530120170060701','云南','江岸小区50幢2单元409','5033228','被评为三好学生','','') insert hzw_student values('9801055','王风','530120171072501','湖北','白马小区15幢3单元101','4133224','','','') insert hzw_student values('9902006','黄光美','530120*********','湖南','金星小区8幢3单元208','3133218','被评为特优生','','') insert hzw_student values('9704001','钱丽','530120168121101','云南','静园小区12幢3单元109','2133227','','','') insert hzw_student values('9603001','王迷','530120174050101','黑龙江','江岸小区40幢1单元612','5033219','被评为红花少年','','') insert hzw_student values('9606005','赵人','530120175040702','江苏','江岸小区30幢4单元222','5033123','','','') insert hzw_student values('9803011','李游','530120173021201','四川','白马小区25幢3单元410','4133124','获集邮展览三等奖','','') insert hzw_student values('9908088','孙客如','530120172092801','贵州','金星小区55幢3单元214','3133177','获省级长跑第二名','因作弊受处分','') insert hzw_student values('9608066','于云','530120174092201','云南','阳光小区55幢5单元112','3133222','获优秀作文奖','','') insert hzw_student values('9805026','李春','530120174110901','云南','阳光小区2幢1单元444','3133789','','','') insert hzw_student values('9702033','高城','530120170080401','江苏','阳光小区8幢2单元808','3148212','','','') insert hzw_course values('20511','世界近代史',4,4) insert hzw_course values('10101','大学语文',2,2) insert hzw_course values('20801','计算机基础(一)',4,3) values('10218','高等代数',4,4) insert hzw_course values('11001','英语(一)',6,6) insert hzw_course values('20113','外国文学',4,4) insert hzw_course values('30416','接口技术',4,3) insert hzw_course values('20327','报刊编辑学',2,2) insert hzw_course values('20521','中国民族史',3,2) insert hzw_course values('30213','数论',4,4) insert hzw_course values('11101','体育',2,2) insert hzw_course values('21003','英语(二)',4,4) insert hzw_course values('30211','概率统计',3,3) insert hzw_course values('30232','数学分析',2,2) insert hzw_course values('40711','国际投资学',2,2) insert hzw_course values('40722','国际商法',2,2) insert hzw_course values('30832','算法设计',4,4) insert hzw_course values('10812','数字电路',4,4) insert hzw_course values('10715','高等数学',4,4) insert hzw_course values('20111','古代汉语',3,3) insert hzw_course values('30802','计算机基础(二)',3,3) insert hzw_course values('30423','电磁场理论',3,3) insert hzw_course values('40331','传播心理学',2,2) insert hzw_course values('40625','色谱学',2,2) insert hzw_course values('20314','新闻学概论',2,2) values('10811','离散数学',2,2) insert hzw_course values('30819','编辑技术',4,4) insert hzw_course values('20534','中国近代史',4,4) insert hzw_course values('10712','政治经济学',3,3) insert hzw_course values('20115','现代汉语',4,4) insert hzw_course values('10222','解析几何',2,2) insert hzw_course values('30412','近代物理实验',3,2) insert hzw_course values('40316','当代新闻史',2,2) insert hzw_course values('40612','配位化学',3,3) insert hzw_course values('20328','现代新闻报道',4,4) go insert hzw_grade values('9805026','20801',75,87,82) insert hzw_grade values('9702033','30802',80,89,91) insert hzw_grade values('9907002','11001',91,83,85) insert hzw_grade values('9801055','20113',70,65,55) insert hzw_grade values('9902006','11001',78,86,81) insert hzw_grade values('9704001','30416',80,90,90) insert hzw_grade values('9803011','20327',95,93,90) insert hzw_grade values('9908088','11001',90,91,87) insert hzw_grade values('9805026','20521',90,97,96) insert hzw_grade values('9702033','30213',88,65,72) insert hzw_grade values('9907002','11101',88,65,72) values('9801055','21003',70,90,84) insert hzw_grade values('9902006','11101',82,75,78) insert hzw_grade values('9805026','20511',90,82,86) insert hzw_grade values('9702033','30232',80,84,83) insert hzw_grade values('9907002','10101',84,96,92) insert hzw_grade values('9801055','20801',76,78,60) insert hzw_grade values('9902006','10101',85,88,81) insert hzw_grade values('9704001','30802',90,87,82) insert hzw_grade values('9803011','20801',60,50,51) insert hzw_grade values('9607039','40711',85,80,88) insert hzw_grade values('9907002','10715',83,90,86) insert hzw_grade values('9801055','20111',78,60,65) insert hzw_grade values('9902006','10218',75,63,52) insert hzw_grade values('9704001','30423',80,81,85) insert hzw_grade values('9603001','40331',67,72,70) insert hzw_grade values('9606005','40625',83,85,84) insert hzw_grade values('9803011','20314',76,76,76) insert hzw_grade values('9908088','10811',82,92,89) insert hzw_grade values('9608066','30819',78,84,82) insert hzw_grade values('9907002','10712',90,96,97) insert hzw_grade values('9801055','20115',80,82,87) insert hzw_grade values('9902006','10222',85,91,79) values('9704001','30412',78,87,80) insert hzw_grade values('9603001','40316',66,71,73) insert hzw_grade values('9606005','40612',70,78,60) insert hzw_grade values('9803011','20328',90,88,87) insert hzw_grade values('9908088','10812',80,67,83) insert hzw_grade values('9608066','30832',95,92,93) insert hzw_grade values('9908088','10101',80,82,87) GO --2.修改学生表的学号列，在学号前加两位“19”，在第四位后加入二位“00”。例如： --学号“9607039”改为“199********”；Transact-SQL脚本存为<你的姓名>_3_02.sql文件。 use School go update hzw_student set SXH ='19'+substring(SXH,1,4)+'00'+substring(SXH,5,7) go --3\追加你自己的自然情况资料到<你的姓名>_student表中，追加你自己《计算机基础（一）》， --《英语（一）》，《体育》课程的平时，期中，期末成绩到<你的姓名>_grade表中。Transact-SQL 脚本存为<你的姓名>_3_03.sql文件。 USE School go insert hzw_student values ('20101060060','胡志伟','42102319910522791','湖北','云南大学楠苑2幢B616','1878851',' ',' ','19910522') insert hzw_grade values ('20101060060','20801','60','60','60') insert hzw_grade values ('20101060060','11001','60','60','60') insert hzw_grade values ('20101060060','11101','60','60','60') go --4、创建显示学号、姓名、籍贯、家庭住址和奖励列的查询，要求：地址列的标题显示为“家庭住址”籍贯为“ --云南”，并且获得过奖励，或者家庭住址在“江岸小区”，且住在“4单元”的学生。结果按学号降序排序。 --Transact-SQL脚本存为<你的姓名>_3_04.sql文件。 use School go select SXH, SMZ ,SJG,家庭住址=SJTZZ,SJL from hzw_student where SJG='云南'and SJL != ' ' or SJTZZ like '江岸小区%4单元%' order by SXH asc go --5、创建显示云南籍学生所选修的课程的课程代号、课程名和学分的查询，要求：Transact-SQL脚本存为 --<你的姓名>_3_05.sql文件。 use School go select SMZ,SJG,CKCDH,CKCM,CXF from hzw_student,hzw_course,hzw_grade where hzw_student.SXH=hzw_grade.GXH and hzw_grade.GKCDH=hzw_course.CKCDH and SJG='云南' go --6、创建一个计算总评分的用户自定义函数summ(ps,qz,qm), T-SQL脚本存为<你的姓名>_3_06.sql文件。要求： --（1）ps表示平时；qz表示期中；qm表示期末 --（2）计算公式：summ(ps,qz,qm)=ps*10%+qz*30%+qm*60% use School go create function sumn1(@XH varchar(11)) returns table as return(select GXH,GPS,GQZ,GQM,SZP=GPS*0.1+GQZ*0.3+GQM*0.6 from hzw_grade where @XH=GXH) go --7、创建列出每名学生的姓名、选修的课程名、总评分和是否获得学分的视图，Transact-SQL脚本存为<你的姓名>_3_07.sql文件。要求： --（1）视图的名称为<你的姓名>_view1。 --（2）新增“是否获得学分”列，当总评分不小于85分时，该列的值为“√”,否则为“×”。--（3）按学号升序排序。 create view hzw_view1 as select SMZ,CKCM, SZP=GPS*0.1+GQZ*0.3+GQM*0.6 ,HDXF=CASE when (GPS*0.1+GQZ*0.3+GQM*0.6)<85 then '×' else '√' end from hzw_student,hzw_course,hzw_grade where hzw_student.SXH=hzw_grade.GXH and hzw_grade.GKCDH=hzw_course.CKCDH go --8.创建显示每名学生的姓名、选修的课程门数、总周学时数、总学分数和加权平均分的查询，Transact-SQL脚本存为 --<你的姓名>_3_08.sql文件。要求： --计算加权平均分的公式： --结果按加权平均分降序排序。 go create view hzw_view4 as select SMZ,XKMS=count(CKCDH),ZZXS=sum(CZXS),ZXF=sum(CXF),A VGE= sum((GPS*0.1+GQZ*0.3+GQM*0.6)*CXF)/sum(CXF) from hzw_student,hzw_course,hzw_grade where hzw_student.SXH=hzw_grade.GXH and hzw_grade.GKCDH=hzw_course.CKCDH group by SXH,SMZ go select *from hzw_view4 order by A VGE desc go --9.创建统计课程选修人数、最高总评分和最低总评分的查询，Transact-SQL脚本存为<你的姓名>_3_09.sql文件。要求： --所有选修该门课程的学生的总评分必须都及格 --结果按选修人数降序排序； --将查询生成表<你的姓名>_sum。 use School go create view hzw_view3 as select CKCM,KCXXRS=count(CKCDH) ,Cmax=max(GPS*0.1+GQZ*0.3+GQM*0.6),Cmin=min(GPS *0.1+GQZ*0.3+GQM*0.6) from hzw_student,hzw_course,hzw_grade where hzw_student.SXH=hzw_grade.GXH and hzw_grade.GKCDH=hzw_course.CKCDH group by CKCM,CKCDH go select*from hzw_view3 order by KCXXRS go --10.创建查询，Transact-SQL脚本存为<你的姓名>_3_10.sql文件。要求： --计算并显示每一门课程的课程名、最高总评分及对应的学生姓名。 --按课程名升序排序。 --将查询生成表<你的姓名>_report use School go create view hzw_view2 as select CKCDH,CKCM,Cmax=max(GPS*0.1+GQZ*0.3+GQM*0.6)from hzw_student,hzw_course,hzw_grade where hzw_student.SXH=hzw_grade.GXH and hzw_grade.GKCDH=hzw_course.CKCDH group by CKCM ,CKCDH go go select SMZ,hzw_view1.CKCM,SZP from hzw_view1,hzw_view2 where hzw_view1.SZP=hzw_view2.Cmax and hzw_view1.CKCM=hzw_view2.CKCM order by hzw_view1.CKCM go