《SQL - Server - 2000 - 实验指导》课后作业

目录

创建数据库JXSK： ......................................................................................................................... - 1 - 创建JXSK中的表： ........................................................................................................................ - 1 - 插入JXSK库中各表值： ................................................................................................................ - 1 - 创建数据库JIAOXUEDB： ................................................................................................................ - 3 - 创建JIAOXUEDB中的表： ............................................................................................................... - 3 - 插入JIAOXUEDB库中各表值： ....................................................................................................... - 3 - 实验4：习题 .................................................................................................................................. - 5 - 实验5：习题 .................................................................................................................................. - 7 - 实验6：习题 .................................................................................................................................. - 7 - 实验7：习题 .................................................................................................................................. - 8 - 实验8：习题 ................................................................................................................................ - 12 - 实验9：习题 ................................................................................................................................ - 17 - 实验10：习题 .............................................................................................................................. - 21 - 实验11：习题 .............................................................................................................................. - 24 - 实验12：习题 .............................................................................................................................. - 26 - 实验13：习题 .............................................................................................................................. - 39 -

创建数据库jxsk： create database jxsk on (name=jxsk_dat,filename='d:\\jxsk_dat.mdf') log on (name=jxsk_log,filename='d:\\jxsk_log.ldf') go

创建jxsk中的表： use jxsk

create table s(

插入jxsk库中各表值： use jxsk insert into s values('s2','钱尔','男',18,'信息') insert into s values('s3','孙姗','女',20,'信息') sno char(2) not null, sn char(8) not null, sex char(2) null, age tinyint null, dept char(10) null) tno char(2) not null, tn char(8) not null, sex char(2) null, age tinyint null, prof char(10) null, sal smallint null, comm smallint null, dept char(10) null) cno char(2) not null, cn char(10) not null, ct tinyint null, id_tc tinyint null) sno char(2) not null, cno char(2) not null, score tinyint null) tno char(2) not null, cno char(2) not null)

create table t(

create table c(

create table sc(

create table tc(

- 1 -

insert into s values('s4','李思','男',21,'自动化') insert into s values('s5','周武','男',19,'计算机') insert into s values('s6','吴丽','女',20,'自动化') insert into s values('s7','李奇','男',18,'外语') insert into s values('s8','古明','男',null,'计算机') insert into s values('s9','赵亦','女',17,'计算机')

insert into t values('t5','张兰','女',39,'副教授',1300,2000,'信息') insert into t values('t4','张雪','女',51,'教授',1600,3000,'自动化') insert into t values('t3','刘伟','男',30,'讲师',900,1200,'计算机') insert into t values('t2','王平','女',28,'讲师',800,1200,'信息') insert into t values('t1','李力','男',47,'教授',1500,3000,'计算机')

insert into c values('c1','程序设计',60,1) insert into c values('c2','微机原理',80,2) insert into c values('c3','数字逻辑',60,3) insert into c values('c4','数据结构',80,4) insert into c values('c5','数据库',60,5) insert into c values('c6','编译原理',60,6) insert into c values('c7','操作系统',60,7)

insert into sc values('s2','c5',57) insert into sc values('s2','c6',80) insert into sc values('s2','c7',null) insert into sc values('s3','c1',75) insert into sc values('s3','c2',70) insert into sc values('s3','c4',85) insert into sc values('s4','c1',93) insert into sc values('s4','c2',85) insert into sc values('s4','c3',83) insert into sc values('s5','c2',89) insert into sc values('s9','c1',90) insert into sc values('s9','c2',85)

insert into tc values('t2','c5') insert into tc values('t3','c1') insert into tc values('t3','c5') insert into tc values('t4','c2') insert into tc values('t4','c3') insert into tc values('t5','c5') insert into tc values('t5','c7') insert into tc values('t1','c1') insert into tc values('t1','c4')

- 2 -

创建数据库jiaoxuedb： create database jiaoxuedb on (name=jiaoxuedb_dat,filename='d:\\jiaoxuedb_dat.mdf') log on (name=jiaoxuedb_log,filename='d:\\jiaoxuedb_log.ldf') go

创建jiaoxuedb中的表： use jiaoxuedb

create table student(

插入jiaoxuedb库中各表值： use jiaoxuedb /*insert values to student table*/ insert into student values('001101','宋大方','男',19,'计算机') sno char(10) not null, sname char(10) not null, sex char(2) null, age tinyint null, dept char(10) null) tno char(10) not null, tname char(10) not null, sex char(2) null, age tinyint null, prof char(10) null, sal smallint null, comm smallint null, dept char(10) null) cno char(10) not null, cname char(10) not null, chour tinyint null, ccredit tinyint null) sno char(10) not null, cno char(10) not null, score tinyint null) tno char(10) not null, cno char(10) not null)

create table teacher(

create table course(

create table sc(

create table tc(

- 3 -

insert into student values('002102','李王','男',20,'信息') insert into student values('991101','张彬','男',18,'计算机') insert into student values('991102','王蕾','女',19,'计算机') insert into student values('991103','张建国','男',18,'计算机') insert into student values('991104','李平方','男',18,'计算机') insert into student values('991201','陈东辉','男',19,'计算机') insert into student values('991202','葛鹏','男',21,'计算机') insert into student values('991203','潘桃芝','女',19,'计算机') insert into student values('991204','姚一峰','男',18,'计算机') insert into student values('001102','许辉','女',22,'计算机') insert into student values('001201','王一山','男',20,'计算机') insert into student values('001202','牛莉','女',19,'计算机') insert into student values('002101','李丽丽','女',19,'信息')

/*insert values to teacher table*/

insert into teacher values('000006','许红霞','女',39,'讲师',1100,1200,'计算机') insert into teacher values('000007','许永军','男',57,'教授',2000,3000,'计算机') insert into teacher values('000008','李贵青','女',65,'教授',2000,3000,'计算机') insert into teacher values('000009','王一凡','女',43,'讲师',1200,1200,'计算机') insert into teacher values('000010','田峰','男',33,'助教',500,800,'信息') insert into teacher values('000001','李英','女',39,'副教授',1500,2000,'信息') insert into teacher values('000002','张雪','女',51,'教授',1900,3000,'信息') insert into teacher values('000003','张朋','男',30,'讲师',1000,1200,'计算机') insert into teacher values('000004','王平','女',28,'讲师',850,1200,'信息') insert into teacher values('000005','李力','男',47,'教授',1800,3000,'计算机')

/*insert values to course table*/

insert into course values('01001','计算机基础',60,3) insert into course values('01002','程序设计',80,5) insert into course values('02003','数据结构',60,6) insert into course values('02001','数据库',80,6) insert into course values('02002','计算机网络',60,6) insert into course values('01003','微机原理',60,8) insert into course values('02004','操作系统',60,6) insert into course values('03001','软件工程',60,3) insert into course values('03002','大型数据库',48,2) insert into course values('03003','图像处理',48,2)

/*insert values to sc table*/

insert into sc values('991101','01001',88) insert into sc values('991102','01001',93) insert into sc values('991103','01001',90) insert into sc values('991101','01002',90) insert into sc values('991102','01002',98)

- 4 -

if @prof_old='副教授' and @prof_new='教授' begin go 5、 为表sc创建一触发器，将成绩按下列对应关系由分数转换成等级；

（×题目有错：原sc表score列数据类型定义为tinyint无法转换成“不及格”，“及格”，“中”??等char型的数据） 小于60：不及格 60~70：及格 70~80：中 80~90：良 90~100：优 use jxsk go

create trigger usc on sc for update,insert as

declare @sno char(2) declare @score tinyint

select @sno=sno from sc select @score=score from sc

if @score < 60 begin

if @score > 60 and @score < 70 begin

if @score > 70 and @score < 80 begin

if @score > 80 and @score < 90 begin

update sc set score='良' where sno=@sno update sc set score='中' where sno=@sno end

update sc set score='及格' where sno=@sno end

update sc set score='不及格' where sno=@sno end

update t set comm=comm+900 where tno=@tno end - 20 -

end if @score > 90 and @score < 100 begin go

实验10：习题 基于jxsk实验 1、求

update sc set score='优' where sno=@sno end ?i

i?1100declare @sum smallint,@i smallint set @sum=0 set @i=1 while (@i <= 100) begin set @sum = @sum + @i set @i = @i + 1 end print '总和是：'+str(@sum) 2、求 10!

declare @n int,@i smallint set @n=1 set @i=2 while (@i <= 10) begin set @n = @n * @i set @i = @i + 1 end print '10! ='+str(@n)

3、查询各系的教师人数、学生人数，并按学生人数和教师人数升序排列。 use jxsk select s.dept,count(distinct tno) \教师数\学生数\from t full join s on s.dept=t.dept group by s.dept order by \教师数\学生数\go - 21 -

4、查询姓王学生的总人数及在各系的人数分布以及每个人的姓名、姓名、年龄。 use jxsk select dept as 系别,sn as 姓名,sex as 性别,age as 年龄 from s where sn like '王%' order by dept,sn,sex,age compute count(sn) by dept compute count(sn) go

5、查询学生中重名的名字，人数和分布在几个系中，并按重名人数降序排列。 use jxsk select s1.sn as 姓名,s1.dept as 系别 from s s1,s s2 where s1.sn=s2.sn and s1.sno <> s2.sno order by s1.dept,s1.sn compute count(s1.sn) by s1.dept compute count(s1.sn) go

6、求出每个职称中工资最高、最低教师的姓名、性别、年龄、工资，并给出各职称的平均工资。职称有：助教、讲师（包括工程师）、副教授（包括高级工程师）、教授（包括研究员）。 use jxsk select prof,tn as 姓名,sex,age,sal+comm as 工资 from t order by prof compute max(sal+comm) by prof compute min(sal+comm) by prof compute avg(sal+comm) by prof go 7、按分数段查询课程名、各分数段人数。分数段划分：60分以下、60~70、70~80、80~90、90~100。 use jxsk select cn as 课程名,count(*) as '60以下人数' from c,sc where sc.cno = c.cno and score < 60 group by cn select cn as 课程名,count(*) as '60~70人数' from c,sc where sc.cno = c.cno and score > 60 and score < 70 group by cn - 22 -

select cn as 课程名,count(*) as '70~80人数' from c,sc where sc.cno = c.cno and score > 70 and score < 80 group by cn select cn as 课程名,count(*) as '80~90人数' from c,sc where sc.cno = c.cno and score > 80 and score < 90 group by cn select cn as 课程名,count(*) as '90~100人数' from c,sc where sc.cno = c.cno and score > 90 and score < 100 group by cn go

8、教师“刘伟”已调离该校，要求删除骄傲学数据库jxsk中与刘伟有关的信息，并列出这些被删除的信息。 use jxsk select tno,tn,sex,age,prof,sal,comm,dept from t where tn='刘伟' delete from t where tn='刘伟' go

9、查询比教师“张雪”工资低的教师的姓名、性别、出生日期、职称、工资。要求先列出“张雪”的姓名、性别、出生日期、职称和工资。然后依次列出所有查询得到的教师的信息。 use jxsk (select tn as 姓名,sex as 性别,2008-age as 出生日期, prof as 职称, sal+comm as 工资 from t where tn = '张雪') union (select t1.tn as 姓名,t1.sex as 性别,2008-t1.age as 出生日期, t1.prof as 职称, t1.sal+t1.comm as 工资 from t t1,t t2 where t2.tn = '张雪' and t1.tn <> '张雪'and (t1.sal+t1.comm) < (t2.sal+t2.comm)) order by (sal+comm) desc go 10、查询每位教师的姓名、职称、课程数、总课时数，并按课时数降序排列。 use jxsk - 23 -

select distinct tn as 姓名, prof as 职称,cn as 课程,ct as 课时 from t,c,tc where t.tno=tc.tno and tc.cno=c.cno order by tn,ct desc compute sum(ct) by tn compute sum(ct) go

实验11：习题 基于jiaoxuedb实验

1、用sql语句创建一用户定义的数据类型agetype。要求：系统数据类型为tinyint，可为空，取值范围是0~100，用于学生表和教师表中的年龄字段和选课表中成绩字段的数据类型。 use jiaoxuedb go exec sp_addtype agetype,'tinyint','null' go create rule ru_agetype as @agetype between 0 and 100 go exec sp_bindrule 'ru_agetype','agetype' go

2、用企业管理器创建数据类型nametype。要求：系统数据类型为varchar，长度10个字节，不能为空，用于教师名，学生名，职称字段的数据类型。

--tsql use jiaoxuedb go - 24 -

exec sp_addtype nametype,'varchar (10)','not null' go

3、创建一个函数，要求：根据学生姓名和课程名查询该生该课程的成绩。 --创建函数 use jiaoxuedb go create function score_fun (@sname_in char(10), @cname_in char(10)) returns tinyint as begin go --执行函数 use jiaoxuedb declare @s_score tinyint exec @s_score=score_fun '张建国','程序设计' print '张建国 的 程序设计 成绩是：'+str(@s_score) go

4、创建一个函数，要求：根据教师姓名查询该教师所教课程名、学生人数、平均成绩、最高成绩、最低成绩。 --创建函数 use jiaoxuedb go use jiaoxuedb go create function t_sc_fun (@tname_in char(10)) returns table as go return (select cname as 课程名,count(sno) as 学生人数,avg(score) as 平均成 from teacher,tc,course,sc where @tname_in=teacher.tname and teacher.tno=tc.tno and course.cno=tc.cno and sc.cno=tc.cno group by cname) declare @score_out tinyint select @score_out=score from sc,student,course where student.sname=@sname_in and course.cname=@cname_in and student.sno=sc.sno and course.cno=sc.cno return (@score_out) end 绩,max(score) as 最高成绩,min(score) as 最低成绩 - 25 -

--执行函数 use jiaoxuedb declare @tname_in char(10) select * from t_sc_fun ('李英') go

5、创建一个函数，要求：统计各系个支撑的总人数、男女人数、平均年龄。 --创建函数 use jiaoxuedb go

create function t_prof_fun (@prof_in char(10))

returns @t_prof_table table (职称 char(10) ,人数 tinyint, as begin

insert @t_prof_table select prof,count(tno),

man=(select count(*) as man from teacher where @prof_in=prof and sex=' female=(select count(*) as female from teacher where @prof_in=prof and declare @man tinyint declare @female tinyint

男性人数 tinyint,女士人数 tinyint,均年龄 tinyint)

男' group by prof), sex='女' group by prof), go

--执行函数 use jiaoxuedb

select * from t_prof_fun ('讲师') go

实验12：习题

1、创建一个windows认证的登陆账号newuser，只允许该用户对数据库jiaoxuedb查询。

avg(age) as 平均年龄 from teacher

where @prof_in=teacher.prof group by prof return

end

- 26 -

- 27 -

- 28 -

2、创建一个windows认证的登陆账号student，并将其设置为系统管理员帐号。

- 29 -

- 35 -

- 36 -

5、创建一个角色newstudent，使其具有对数据库jiaoxuedb进行任何操作的权限。并将上面创建的用户：newuser、sqladmin、sqlteacher添加到此用户中。

- 37 -

- 38 -

实验13：习题

1、编写一个事务：对于考试违纪的学生，对其采取考试成绩降一个档次的处罚。考试成绩分为5档次：优、良、中、及格、不及格。 begin transaction use jiaoxuedb go

select sno,cno,grade=

case when score<60 then '不及格'

when score<70 and score>=60 then '及格' when score<80 and score>=70 then '中等' when score<90 and score>=80 then '良好' when score>=90 and score<=100 then '优秀' end

from sc where flag=0 union

select sno,cno,grade=

case when score-10<60 then '不及格'

when score-10<70 and score>=60 then '及格' when score-10<80 and score>=70 then '中等' when score-10<90 and score>=80 then '良好' end

from sc where flag=1 commit

2.对于发生教学事故的教师要进行罚款处理，假如用x表示罚款数额。编写一个罚款事务，要求：从被罚款的教师工资中扣除罚款额x，若工资额大于等于罚款额x，则提交罚款，否则，扣除工资的一半作为罚款额，剩余罚款额以后再扣除，并修改显示剩余的罚款额x。 begin transaction use jiaoxuedb declare @pay int,@name char(10),@sal int set @pay=1000 set @name='李英' select @sal=sal from teacher where tn=@name if @sal>@pay begin update teacher set sal=sal-@pay where tn=@name select @sal=sal from teacher where tn=@name print @name+'经过处罚后还剩余的工资为：'+str(@sal) commit end else begin update teacher set sal=sal-sal/2 where tn=@name - 39 -

select @pay=@pay-sal from teacher where tn=@name print @name+'还欠款'+str(@pay) end go 3.完成下面事务设计：某高校嘉奖优秀教师：增加津贴500元；若此教师为助教，则可破格将其晋升为讲师。获得优秀教师的条件是：至少任两门课课程，并且每门课程的及格率100%，优秀率33%。 4.设计一个事务，给所有在册的非新生学生增加1岁，要求，50人作为一个事务提交一次。

- 40 -

本文来源：https://www.bwwdw.com/article/gfht.html