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 -