数据库原理与应用——SqlServe2012 期末练习1

使用T-SQL语句建立数据库，它包含3个文件组：主文件组、sch1文件组、sch2文件组。主文件组包含20MB的和20MB的两个文件，两个文件的最大尺寸为50MB，增长长度为10%；sch1文件组包含10MB的和10MB的两个文件，两个文件的最大尺寸为默认，增长长度为10％；sch2文件组包含10MB的一个文件，这个文件的最大尺寸为20MB，增长长度为1MB。该数据库同时还包含一个日志文件，文件大小为30MB，增长长度为10％。（存储路径都为d:\）（10分）

create database school
on primary
(name=schmgt1,filename='D:\SQLSERVER\school\schmgt1.mdf',size=20,maxsize=50,filegrowth=10%),(name=schmgt2,filename='D:\SQLSERVER\school\schmgt2.ndf',size=20,maxsize=50,filegrowth=10%),filegroup sch1(name=schmgt3,filename='D:\SQLSERVER\school\schmgt3.ndf',size=10,filegrowth=10%),(name=schmgt4,filename='D:\SQLSERVER\school\schmgt4.ndf',size=10,filegrowth=10%),filegroup sch2(name=schmgt5,filename='D:\SQLSERVER\school\schmgt5.ndf',size=10,maxsize=20,filegrowth=1)--这里，log on 前面不能有逗号，否则编译不通过log on(name=schmgtlog,filename='D:\SQLSERVER\school\schmgtlog.ldf',size=30,filegrowth=10%)

在数据库中包含有学生信息表（）、课程信息表（）和成绩表（score），它们的定义分别为：

(学号 char(6) not null，姓名 char(8)，性别 char(2)，出生时间 ，专业 char(12)，总学分int 0到100学分之间，备注(500))

(课程号 char(3) not null，课程名 char(16)not null，开课学期 1，学时 0，学分 0)

score (学号 char(6) not null，课程号 char(3) not null，成绩 0, 主键为学号与课程号的组合)

（1） 用T-SQL语句分别创建表 表，表，score表，并插入相关数据，相关数据见附录。（3分）

use school 
go
create table student(学号 char(6)  not null,姓名 char(8),性别 char(2),出生时间 datetime,专业 char(12),总学分 int check(总学分>=0 and 总学分<=100),备注 varchar(500)) 
create table course (课程号 char(3)  not null,课程名 char(16) not null,开课学期 tinyint default 1,学时 tinyint default 0,学分 tinyint default 0)
create table score(学号 char(6)  not null,课程号 char(3)  not null,成绩 int default 0, constraint sc_co primary key(学号,课程号))insert into student values('081101','王林','男','1990-2-10','计算机',50,null)
insert into student values('081102','程明','男','1991-2-1','计算机',50,null)
insert into student values('081103','王燕','女','1989-10-6','计算机',50,null)
insert into student values('081104','韦严平','男','1990-8-26','计算机',50,null)  
insert into student values('081106','李方方','男','1990-11-20','计算机',50,null)
insert into student values('081107','李明','男','1990-5-1','计算机',54,'提前修完《数据结构》，并获学分')
insert into student values('081108','林一帆','男','1989-8-5','计算机',52,'已提前修完一门课')
insert into student values('081109','张强民','男','1989-8-11','计算机',50,null)
insert into student values('081110','张蔚','女','1991-7-22','计算机',50,'三好学生')
insert into student values('081111','赵琳','女','1990-3-18','计算机',50,null)
insert into student values('081113','严红','女','1989-8-11','计算机',48,'有一门课不及格，待补考')
insert into student values('081201','王敏','男','1989-6-10','通信工程',42,null)  
insert into student values('081202','王林','男','1989-1-29','通信工程',40,'有一门课不及格，待补考')
insert into student values('081203','王玉民','男','1990-3-26','通信工程',42,null)
insert into student values('081206','李计','男','1989-9-20','通信工程',42,null)
insert into student values('081210','李红庆','男','1989-5-1','通信工程',44,'已提前修完一门课，并获得学分')
insert into student values('081216','孙祥欣','男','1989-3-19','通信工程',42,null)
insert into student values('081218','孙研','男','1990-10-9','通信工程',42,null)
insert into student values('081220','吴薇华','女','1990-3-18','通信工程',42,null)  
insert into student values('081221','刘燕敏','女','1989-11-12','通信工程',42,null)
insert into student values('081241','罗琳琳','女','1990-1-30','通信工程',50,'转专业学习')insert into course values('101','计算机基础',1,80,5)
insert into course values('102','程序设计与语言',2,68,4)  
insert into course values('206','离散数学',4,68,4)
insert into course values('208','数据结构',5,68,4)
insert into course values('210','计算机原理',5,85,5)  
insert into course values('209','操作系统',6,68,4)  
insert into course values('212','数据库原理',7,68,4)
insert into course values('301','计算机网络',7,51,3)  
insert into course values('302','软件工程',7,51,3)insert into score values('081101','101',80)
insert into score values('081101','102',78)
insert into score values('081101','206',76)
insert into score values('081103','101',62)
insert into score values('081103','102',70)
insert into score values('081103','206',81)
insert into score values('081104','101',90)
insert into score values('081104','102',84)
insert into score values('081104','206',65)
insert into score values('081102','102',78)
insert into score values('081102','206',78)
insert into score values('081106','101',65)
insert into score values('081106','102',71)
insert into score values('081106','206',80)
insert into score values('081107','101',78)
insert into score values('081107','102',80)
insert into score values('081107','206',68)
insert into score values('081108','101',85)
insert into score values('081108','102',64)
insert into score values('081108','206',87)
insert into score values('081109','101',66)
insert into score values('081109','102',83)
insert into score values('081109','206',70)
insert into score values('081110','101',95)
insert into score values('081110','102',90)
insert into score values('081110','206',89)
insert into score values('081111','101',91)
insert into score values('081111','102',70)
insert into score values('081111','206',76)
insert into score values('081113','101',63)
insert into score values('081113','102',79)
insert into score values('081113','206',60)
insert into score values('081201','101',80)
insert into score values('081202','101',65)
insert into score values('081203','101',87)
insert into score values('081204','101',91)
insert into score values('081210','101',76)
insert into score values('081216','101',81)
insert into score values('081218','101',70)
insert into score values('081220','101',82)
insert into score values('081221','101',76)
insert into score values('081241','101',90)

（2） 在表中查找女学生的最高总学分、最低总学分。（6分）

use school
go
select max(总学分) as 最高总学分 from student
select min(总学分) as 最低总学分 from student

（3） 查询有一门或一门以上课程成绩小于70分的所有学生的信息，包括学号、姓名、专业。（6分）

use school
go
select distinct(student.学号),student.姓名,student.专业 
from student,score 
where score.成绩<70 and student.学号= score.学号

（4） 找出“离散数学”课程的平均成绩，以及选修了此门课程的学生姓名和专业。（6分）

use school
go
select avg(成绩)as 离散数学平均成绩 from score ,course
where score.课程号=(select  课程号 from course
where course.课程名='离散数学')select distinct(姓名),专业 from student ,course ,score
where student .学号 in (select 学号 from score,course --注意这里要用in而不能用=，因为子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
where score.课程号=(select 课程号 from course where 
course.课程名='离散数学

（5） 查找与“李计”同一专业的女同学的姓名及与他的年龄差。（6分）

use school
go
select 姓名 ,datediff(yy,(select 出生时间 from student where
姓名='李计'),出生时间)as 与李计的年龄差
from student 
where 性别='女' and 专业=(select 专业 from student 
where 姓名='李计')--这里要注意计算时间差的函数 datediff（datepart，startdate，enddate）
--startdate 和 enddate 参数是合法的日期表达式。
--datepart 参数可以是下列的值：
--datepart--缩写
--年--yy或yyyy
--季度--qq或q
--月--mm或m
--年中的日--dy或y
--日--dd或d
--周--wk或ww
--星期--dw或w
--小时--hh
--分钟--mi或n
--秒--ss或s
--毫秒--ms
--微妙--mcs
--纳秒--ns

（6） 创建视图，包括计算机专业王姓同学的相关信息，包括姓名（在视图中列名为name）、其选修的课程号及成绩。（8分）

use school
GO
create view W_VIEW
as
select 姓名 as name ,课程号,成绩 from score,student
where score.学号=student .学号 and 姓名 like '王%'
go

（7） 创建用户定义函数，查询全体学生某门功课最高分、平均分和最低分，并调用该函数查询课程号为101课程的最高分、平均分、最低分。（函数名为）（9分）

use school
GO
create function MAM_FUN(@course  char(3)) returns table
as return 
(select 课程号,max(成绩) as 最高分,avg(成绩) as 平均分,min(成绩) as 最低分from scorewhere 课程号=@coursegroup by 课程号 )

use school
go
select * from  MAM_FUN(101)

（8） 创建一个存储过程CPA，比较两个学生(用学号表示)的平均成绩，若前者比后者高就输出1，否则输出0。（8分）

use school
go
create procedure CPA @sno1 char(6),@sno2 char(6)
output, @n int OUTPUT
as declare @s1 char(6)declare @s2 char(6)set @s1 =(select avg(成绩) from score where 学号=@sno1)set @s2 =(select avg(成绩) from score where 学号=@sno2)if( @s1 > @s2 )set @n=1else set @n=0go

（9） 创建触发器，当向score表中插入数据时，检查学号字段的值在表中是否存在，若存在，则允许插入，并提示“插入数据成功”；若不存在，则取消插入操作，并提示“该学号不存在于表中，不能插入记录，插入将终止！”。（8分）

use school
go
create trigger score_insert on score
for insert
as if (select 学号 from inserted)  in (select 学号 from score)print '插入数据成功'elsebeginrollbackprint '该学号不存在于student表中，不能插入记录，插入将终止！'end 

附录：

student表：
insert into student values('081101','王林','男','1990-2-10','计算机',50,null)
insert into student values('081102','程明','男','1991-2-1','计算机',50,null)
insert into student values('081103','王燕','女','1989-10-6','计算机',50,null)
insert into student values('081104','韦严平','男','1990-8-26','计算机',50,null)  
insert into student values('081106','李方方','男','1990-11-20','计算机',50,null)
insert into student values('081107','李明','男','1990-5-1','计算机',54,'提前修完《数据结构》，并获学分')
insert intostudent values('081108','林一帆','男','1989-8-5','计算机',52,'已提前修完一门课')
insert into student values('081109','张强民','男','1989-8-11','计算机',50,null)
insert into student values('081110','张蔚','女','1991-7-22','计算机',50,'三好学生')
insert into student values('081111','赵琳','女','1990-3-18','计算机',50,null)
insert intostudent values('081113','严红','女','1989-8-11','计算机',48,'有一门课不及格，待补考')
insert into student values('081201','王敏','男','1989-6-10','通信工程',42,null)  
insert intostudent values('081202','王林','男','1989-1-29','通信工程',40,'有一门课不及格，待补考')
insert into student values('081203','王玉民','男','1990-3-26','通信工程',42,null)
insert intostudent values('081206','李计','男','1989-9-20','通信工程',42,null)
insert into student values('081210','李红庆','男','1989-5-1','通信工程',44,'已提前修完一门课，并获得学分')
insert into student values('081216','孙祥欣','男','1989-3-19','通信工程',42,null)
insert into student values('081218','孙研','男','1990-10-9','通信工程',42,null)
insert into student values('081220','吴薇华','女','1990-3-18','通信工程',42,null)  
insert into student values('081221','刘燕敏','女','1989-11-12','通信工程',42,null)
insert intostudent values('081241','罗琳琳','女','1990-1-30','通信工程',50,'转专业学习')course表：
insert into course values('101','计算机基础',1,80,5)
insert into course values('102','程序设计与语言',2,68,4)  
insert into course values('206','离散数学',4,68,4)
insert into course values('208','数据结构',5,68,4)
insert into course values('210','计算机原理',5,85,5)  
insert into course values('209','操作系统',6,68,4)  
insert into course values('212','数据库原理',7,68,4)
insert into course values('301','计算机网络',7,51,3)  
insert into course values('302','软件工程',7,51,3)score表：
insert into score values('081101','101',80)
insert into score values('081101','102',78)
insert into score values('081101','206',76)
insert into score values('081103','101',62)
insert into score values('081103','102',70)
insert into score values('081103','206',81)
insert into score values('081104','101',90)
insert into score values('081104','102',84)
insert into score values('081104','206',65)
insert into score values('081102','102',78)
insert into score values('081102','206',78)
insert into score values('081106','101',65)
insert into score values('081106','102',71)
insert into score values('081106','206',80)
insert into score values('081107','101',78)
insert into score values('081107','102',80)
insert into score values('081107','206',68)
insert into score values('081108','101',85)
insert into score values('081108','102',64)
insert into score values('081108','206',87)
insert into score values('081109','101',66)
insert into score values('081109','102',83)
insert into score values('081109','206',70)
insert into score values('081110','101',95)
insert into score values('081110','102',90)
insert into score values('081110','206',89)
insert into score values('081111','101',91)
insert into score values('081111','102',70)
insert into score values('081111','206',76)
insert into score values('081113','101',63)
insert into score values('081113','102',79)
insert into score values('081113','206',60)
insert into score values('081201','101',80)
insert into score values('081202','101',65)
insert into score values('081203','101',87)
insert into score values('081204','101',91)
insert into score values('081210','101',76)
insert into score values('081216','101',81)
insert into score values('081218','101',70)
insert into score values('081220','101',82)
insert into score values('081221','101',76)
insert into score values('081241','101',90)