数据库课后习题

作业要求：1. 所有作业书写在稿纸上2. 在封面写清楚班级、姓名、学号3. 抄写题目(表可以不写),命令与题目对应。

使用T-SQL语句实现下列功能：一、数据库及其操作1. 创建一个active数据库，主数据文件的逻辑文件名为arch1，实际文件名为archda1.mdf，事务日志文件的逻辑文件名为archlog1，实际文件名分别为archlog1.ldf，上述文件的初始容量均为5MB，最大容量均为50MB，递增量均为1MB。

Create database active size 5m atuoextend on next 1m mixsize 50m;2. 删除active数据库。

Drop active;3. 在D盘DA TABASE文件夹下创建”学生库”数据库，主数据文件的逻辑文件名为“学生库_Data”,实际文件名为“学生库_Data.mdf”，事务日志文件的逻辑文件名为“学生库_Log”，实际文件名为“学生库_Log.ldf”。

上述文件的初始容量均为5MB，最大容量均为50MB，递增两均为2MB。

Create database 学生库size 5m autoextend on next 2m mixsize 50m二、建表并修改属性4. 在“学生库”中建立“学生信息”新表，结构如下表所示。

Create table 学生信息（学号char(7) not null,姓名char(8) not null,性别char(2)，班级varchar（10），出生日期datetime，民族char(5),是否团员bit，邮政编码varchar（10），地址varchar（40），电话char（8），简历text）5. 设置列属性，将学号设置为主键，设置“性别”默认值为“男”。

Set 学号primary key ;性别char（2）default‘男’6. 修改学生信息表●设置“民族”默认值为“汉”，将出生日期的数据类型改为smalldatetime。

Alter table 学生信息民族set default‘汉‟Alter table 学生信息alter 出生如期smalldatetime；●在班级上方添加年龄列，数据类型为int允许为空,删除邮政编码列，操作完成后存盘。

“学生信息”表Alter table 学生信息add 年龄int ，drop 邮政编码字段名称数据类型字段大小允许空学号char7姓名char8性别char2√班级varchar10√出生日期datetime√民族char6√是否团员bit√邮政编码char6√地址varchar40√电话char8√简历text√7. 建立“课程”表，结构如下：“课程”表字段名称数据类型字段大小允许空课程号char 4课程名称char 10 √学分int √姓名char 8 √8. 设置列属性将课程号设置为主键，设置“学分”默认值为0。

Alter table 课程set 课程号primary key，学分default 09. 修改课程表●将课程名称的数据类型改为varchar（10）。

Alter table 课程alter课程名varchar(10)10.编写“学生成绩”新表，结构如下表所示。

Create table 学生成绩（学号char（7），姓名varchar（8），期中成绩int，期末成绩int）11. 设置列属性将学号设置为主键。

Alter table 课程 set 学号 primary key 12. 修改学生成绩表●添加“平时成绩”列名数据类型int,“总成绩”列名数据类型int ，总评列名数据类型为char(4)。

Alter table 课程 add 平时成绩 int ，总成绩 int ●将主键设置为学号和课程号。

Alter table 课程 set 学号，课程号 primary key三、.输入数据13 按下表所显示的内容输入数据（用命令完成，每个表至少插入三条记录) Intsert into 学生信息 values （1003，‘王丽娟’，‘女’，金融2‘，19，‘1988-5-11‟,‟汉’TRUE,100082，‟平安小区‟ ）字段名称 数据类型字段大小允许空学号char7课程号char4期中成绩int√期末成绩int√学号 姓名 性别 班级 年龄 出生日期民族 是否团员邮政编码地址 简历 1003 王丽娟女 金融2 19 1986-5-11汉 TRUE 100082平安小区1004 黎明 男 金融 20 1983-2-21汉 FALSE 100057宣武区1005 和平 女 计算机应用19 1985-10-9回 TRUE 100876海淀区1006 张证明女 金融 23 1985-4-8汉 TRUE 100023海淀区1007李桦男金融2241985-10-7汉FALSE100022宣武区课程号课程名称学分任课教师1002 英语泛读 2 章华2005 运筹学 3 王国强2012 线性代数 3 李平3102 经济学原理 3 周明鹰3111 企业经营管理 3 裴志新学号课程号班级期中成绩期末成绩平时成绩总成绩总评10031002金融2456610042005金融809010051002计算机应用9810010063102金融809010074009金融2787014. 在学生成绩表修改：期中成绩和期末成绩〉=90总评为优、期中成绩和期末成绩在70～89之间总评为良、期中成绩和期末成绩在60～69之间总评为及格。

15. 查看修改后的数据。

Select * from 学生成绩；四、视图操作16．利用基表“学生成绩”创建“练习视图3”视图，要求的列名为：课程号，班级，期中成绩，期末成绩，平时成绩，并运行结果。

CREA TE VIEW 联系视图3（课程号，班级，期中成绩，期末成绩，平时成绩）AS select 课程号，班级，期中成绩，期末成绩，平时成绩from 学生成绩；17．利用基表“学生成绩”创建“练习视图4”视图，要求的条件为：期中成绩在80和90之间的列名为：学号，课程号，班级，期中成绩，期末成绩。

并运行结果。

CREA TE VIEW 联系视图4（课程号，班级，期中成绩，期末成绩，平时成绩）AS select 课程号，班级，期中成绩，期末成绩，平时成绩from 学生成绩where 成绩between 80 and 90；18. 利用基表“学生成绩”创建“练习视图5”视图，要求的列名为：课程号，班级，期中成绩，期末成绩，平时成绩并对文件加密，运行结果。

CREA TE VIEW 联系视图5（课程号，班级，期中成绩，期末成绩，平时成绩）19. 在“练习视图3”视图，从基表“学生成绩”中添加总评成绩的列，并改名为“练习视图6”。

ALTER VIEW 练习视图3 ADD 总评成绩20. 修改“练习视图6”视图，将“计算机应用”班级改为“网络工程”班级。

ALTER VIEW 练习视图6 ALTER …计算机应用TO 网络工程21. 修改“练习视图6”视图，将网络工程班级期末成绩〉80的总评成绩为良好。

ALTER VIEW 练习视图6 ALTER 总评成绩良好（期末成绩>80）22. 修改“练习视图6”视图，期中成绩和期末成绩在90以上平时成绩为20分，期中成绩和期末成绩在80-90之间平时成绩为15分，期中成绩和期末成绩在70-80之间平时成绩为10分，期中成绩和期末成绩在60-70之间平时成绩为5分，期中成绩和期末成绩在60以下平时成绩为2分。

查看视图信息23. 用系统存储过程显示定义视图的T-SQL语句查看练习视图5和练习视图6，并进行比较。

Select * from 练习视图5，练习视图524．用系统存储过程查看视图信息的T-SQL语句查看练习视图6的内容。

25. 查看练习视图6的视图关联性。

26. 试比较上述的3个命令的相同点，和不同点。

删除视图27. 删除“练习视图6”视图中的平时成绩为2分以下的记录。

Drop VIEW 练习视图6 where 平时成绩<228. 删除“练习视图5”视图。

Drop VIEW 练习试题5五、基本查询及其操作29. 在“学生信息”表中，查询前5条的所有记录。

Select top(5) from 学生信息30. 在“学生信息”表中，查询20%的所有记录。

Select top(（select count(*) from 学生信息)*0.2）from 学生信息31. 在“学生信息”表中，查询班级为金融1的学号,班级,性别和年龄记录。

Select 学号，班级，性别，年龄from学生信息where 班级=‟金融1‟32. 在“学生信息”表中查询班级不能重复的记录。

Select distinct 班级from 学生信息33. 在“学生信息”表中查询学号和班级连接显示的记录并将合成的列名为“学号班级”。

Select （学号，班级）AS 学号班级from 学生信息34. 在“学生成绩”表中查询男生的期末成绩大于80的并平时成绩为20分的记录。

Select * from 学生成绩where 期末成绩>80AND平时成绩=20AND性别=‘男‟35. 在“学生成绩”表中查询期末成绩在70—80的所有记录。

Select * from 学生成绩where 期末成绩between70 and 8036. 在“学生信息”表中，查询学号为1003,1005, 1007的班级,性别和年龄记录。

Select * from 学生信息where 学号IN (1003,1005，.1007)37. 在“学生信息”表中，查询姓李同学的所有记录。

SELECT * FROM 学生信息where name LIKE ‘李%‟38. 在“学生信息”表中，查询姓李或姓王同学的学号，姓名和班级的记录。

Select 学号，姓名，班级FROM 学生信息where name LIKE ‘李%‟ OR …王%‟39. 在“学生信息”表中，查询叫李某同学的所有记录。

Select * from 学生信息where 姓名LIKE …李_‟40. 在“学生信息”表中，查询除了姓李同学的所有记录。

Select * from 学生信息where 姓名NOT LIKE …李%‟41. 在“学生信息”表中，查询邮政编码不是以0开头，是以00082结尾的，长度为6位的所有记录。

Select * from 学生信息where len（邮政编码）=6 AND substring(邮政编码，1,1)<>0;42. 在“学生信息”表中，查询出生日期在1985年内出生的所有女生的所有记录。

Select * from 学生信息where datepart（year，出生日期）<1985 AND 性别=‟女‟43. 在“学生成绩”表中，查询期末成绩在前5名同学的学号，期中成绩和期末成绩的记录。