《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 -

insert into sc values('991103','01002',74) insert into sc values('991104','01002',85) insert into sc values('001201','01002',64) insert into sc values('991104','02001',33) insert into sc values('991104','01001',35) insert into sc values('991201','01001',76)

/*insert values to tc table*/

insert into tc values('000001','02001') insert into tc values('000008','02002') insert into tc values('000003','02001') insert into tc values('000011','02003') insert into tc values('000001','01001') insert into tc values('000002','01002') insert into tc values('000002','01003') insert into tc values('000004','02002') insert into tc values('000005','01001') insert into tc values('000006','01002') insert into tc values('000003','01003')

实验4：习题

基于jiaoxuedb实验 1、 对个表创建实体完整性； use jiaoxuedb

alter table student

alter table teacher

alter table course

alter table student

alter table teacher

alter table course

add constraint unique_c unique(cno) add constraint unique_t unique(tno) add constraint unique_s unique(sno) add constraint pk_cno primary key(cno) add constraint pk_tno primary key(tno) add constraint pk_sno primary key(sno)

- 5 -

2、 创建表之间的参照完整性。 use jiaoxuedb alter table sc with nocheck alter table sc with nocheck alter table tc with nocheck alter table tc with nocheck

3、 用不同的方法创建下面约束。 （1） “姓名”字段取唯一值： use jiaoxuedb alter table student

(2) “性别”字段取值为：‘男’或‘女’；默认值：‘男’； use jiaoxuedb alter table teacher with check alter table student with check （3）“年龄”和“分数”字段取值限定为：0~100之间。 use jiaoxuedb alter table student with check add constraint check_s_age check(age>0 and age<100) add constraint check_s_sex check(sex='男' or sex='女') add constraint df_s_sex default '男' for sex alter table student add constraint check_t_sex check(sex='男' or sex='女') add constraint df_t_sex default '男' for sex alter table teacher add constraint unique_sna unique(sname) add constraint unique_tna unique(tname) alter table teacher add constraint fk_c_tc foreign key(cno) references course(cno) on delete cascade add constraint fk_t_tc foreign key(tno) references teacher(tno) on delete cascade add constraint fk_c_sc foreign key(cno) references course(cno) on delete cascade add constraint fk_s_sc foreign key(sno) references student(sno) on delete cascade - 6 -

alter table teacher with check

实验5：习题

1、 对教师表teacher中的教师号tno创建聚族索引，并按降序排列。 use jiaoxuedb create clustered index ind_tno

2、 对学生选课表sc，先按课号cno升序排列，再按成绩score降序排列。 cno升序： use jiaoxuedb create clustered index ind_sc_cno on sc(cno asc) score降序： use jiaoxuedb create clustered index ind_sc_score on sc(score desc) 3、 对表student中的学号sno创建唯一索引，并按升序排列。 use jiaoxuedb create unique index ind_s_sno on student(sno asc)

实验6：习题

基于jiaoxuedb实验

1、创建成绩视图score_view,包含学号sno,姓名sn，课程名cn，成绩score。 use jiaoxuedb go create view score_view go

2、创建一个计算机系学生名单视图s_view，包含学号sno，姓名sn，性别sex。 use jiaoxuedb go create view s_view go

4、 通过上面的视图，修改学号为991102，课号为01001的成绩记录。 use jiaoxuedb

- 7 -

as select sno,sname,sex from student where dept='计算机' as select student.sno,sname,cname,score from student,course,sc where student.sno=sc.sno and sc.cno=course.cno on teacher(tno desc) add constraint check_t_age check(age>0 and age<100)

update score_view set score=100 where sno='991102' and cname= go 5、创建一个视图，计算机系学生的成绩单score_view_cdept,版含学号sno，姓名sn，课程名cn，成绩score。 use jiaoxuedb go create view score_view_cdept go

6、给视图s_view增加一个年龄age字段。 use jiaoxuedb go alter view s_view go

7、创建一个教师工资表视图sal_view，版含字段教师名tname，性别sex，职称prof，工资总和salsum，系别dept。 use jiaoxuedb go create view sal_view go

8、通过视图查询教师“张明”的工资收入。 use jiaoxuedb select tname,salsum from sal_view where tname='张朋' go 实验7：习题

基于jiaoxuedb实验

- 8 -

as select tname,sex,prof,sal+comm as salsum from teacher as select sno,sname,sex,age from student where dept='计算机' as select student.sno,sname,cname,score from student,course,sc and student.sno=sc.sno and sc.cno=course.cno (select cname from course where cno='01001') where student.dept='计算机'

1、 查询成绩在80~90之间的记录。

select student.sno,sname,dept,course.cno,score from student,sc,course where student.sno=sc.sno 2、 查询至少有4个同学选修的课程名。 use jiaoxuedb select cno as 课程号,cname as 课程名 from course where cno=any go

3、 查询其他系中比“信息系”所有学生年龄都打的学生名单及年龄，并按年龄降序输出。 use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where age>all go

4、 查询与学生张建国同岁的所有学生的学号、姓名和系别。 /*方法一*/ use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where age=any go /*方法二*/ (select age from student where sname='张建国') and sname <> '张建国' (select age from student where dept='信息') and dept <> '信息' (select cno as 课程号 from sc group by cno having count(*)>=4) and sc.score between 80 and 90 and sc.cno=course.cno; order by age desc order by age desc - 9 -

- 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/oq4g.html