数据库原理张红娟答案.docx

1、数据库原理张红娟答案11.（1）给学生表增加一个属性Nation,数据类型为Varchar（20）：ALTER TABLE StudentADD Nation VARCHAR(20) NULL;（2）删除Nation：ALTER TABLE StudentDrop Column Nation;（3）向成绩表中插入记录(2001110,3,80)：insert into Gradevalues(2001110,3,80);（4）将学号为2001110的学生的成绩改为70分：update Gradeset Gmark=70 where Sno=2001110;（5）删除学号为2001110的学生的

2、成绩记录：delete from Grade where Sno=2001110;（6）在学生表的clno属性上创建一个名为IX_Class的索引，以班级号的升序排序：create index IX_Class on Student (clno Asc);（7）删除IX_Class索引：drop index Student.IX_Class;12.（1）找出所有被学生选修了的课程号：select distinct Cnofrom Grade;（2）找出01311班女学生的个人信息：select * from Studentwhere Clno=01311 and Ssex=女;（3）找出013

3、11班和01312班的学生姓名、姓名、出生年份select Sname,Ssex,2014-Sage as year of birthfrom Studentwhere Clno=01311 or Clno=01312;（4）找出所有姓李的学生的个人信息select * from Student where Sname like 李%;（5）找出学生李勇所在班级的学生人数select number from student inner join class on student.clno=class.clno where sname=李勇（6）找出课程名为操作系统的平均成绩、最高分、最低分se

4、lect AVG(Gmark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分 from Gradewhere Cno in(select Cno from Coursewhere Cname=操作系统)（7）选修了课程的学生人数;select COUNT(distinct sno) 学生人数from Grade（8）选修了操作系统的学生人数;select COUNT(sno) 学生人数from course inner join gradeon o=owhere Cname=操作系统（9）找出2000级计算机软件班的成绩为空的学生姓名select Sname 学生姓名

5、from (Student inner join class on student.clno=class.clno)inner join grade on student.sno=grade.snowhere Speciality=计算机软件 and inyear=2000 and gmark is null13.1)找出和李勇在同一个班级的学生信息select * from Studentwhere clno in(select Clno from Studentwhere Sname=李勇)2)找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere

6、sno in(select sno from gradewhere cno in(select cno from gradewhere sno in (select sno from studentwhere Sname=李勇);3)找出年龄介于学生李勇和25岁之间的学生信息select * from Studentwhere Sage (select Sage from Student where Sname=李勇) 4)找出选修了课程是操作系统的学生学号和姓名select Sno 学号,Sname 姓名 from Studentwhere sno in(select sno from Gr

7、ade where Cno in(select Cno from Course where cno in (select cno from coursewhere Cname=操作系统);5)找出没有选修1号课程的所有学生姓名select Sname 姓名 from Studentwhere not exists(select * from Grade where Student.Sno=Grade.Sno and Cno=1)6)找出选修了全部课程的学生姓名select Sname 姓名 from Studentwhere not exists(select * from Coursewhe

8、re not exists(select * from Gradewhere Student.Sno=Grade.Snoand Go=o)14.1)查询选修了3号课程的学生学号及成绩,并按成绩的降序排列select Sno 学号,Gmark 成绩 from Gradewhere Cno=3order by Gmark desc2)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列select * from Studentorder by Clno asc ,Sage desc3)求每个课程号及相应的选课人数select Grade.Cno 课程号,COUNT(Grade

9、.Cno) 选课人数 from Grade join Course on Grade.Cno=Course.Cnogroup by Grade.Cno4)查询选修了3门以上课程的学生学号select Sno 学号 from Gradegroup by Snohaving COUNT(Sno)315.1)将01311班的全体学生的成绩置零update Grade set Gmark=0where Sno in(select Sno from Studentwhere Clno=01311)2)删除2001级计算机软件的全体学生的选课记录delete from Gradewhere Sno in(

10、select Sno from Studentwhere Clno in(select Clno from Classwhere Speciality=计算机软件 and Inyear=2001)3)学生李勇已退学,从数据库中删除有关他的记录delete from Gradewhere Sno in(select Sno from Studentwhere Sname=李勇)update Class set Number=Number-1where Clno in(select Clno from Studentwhere Sname=李勇)update Class set Monitor=c

11、ase when Monitor=(select Sno from Student where Sname=李勇)then endfrom Classwhere Clno in(select Clno from Student where Sname=李勇)delete from Studentwhere Sname=李勇4)对每个班,求学生的平均年龄,并把结果存入数据库alter table Class add Cage smallint nullupdate Class set Cage=casewhen Clno=00311 then (select AVG(Sage) from Stu

12、dent where Clno=00311)when Clno=00312 then (select AVG(Sage) from Student where Clno=00312)when Clno=01311 then (select AVG(Sage) from Student where Clno=01311)endfrom Class16.1、create view stu_01311_1as select student sno,sname,gmarkfrom student,gradewhere cno=1and clno=01311and student.sno=grade.s

13、nowith check option2、create view stu_01311_2an select *from stu_01311_1where gmark19905.select *from stu_yearwhere sno in(select snofrom stu_01311_2)第四章10.创建course表create table course ( cno char(1) primary key, cname varchar(20) not null, credit smallint check (credit in (1,2,3,4,5,6,7) ) 创建class表cr

14、eate table class ( clno char(5) primary key, speciality varchar(20) not null, inyear char(4) not null, number integer check(number1 and number14 and sage 0 and gmark100), primary key (sno,cno) ) 11.插入create trigger stu_insert on student after insert as update class set number=number+1 from class,ins

15、erted where class.clno = inserted.clno;删除create trigger stu_delete on student after delete as update class set number=number-1 from class,deleted where class.clno = deleted.clno;12.create trigger stu_update on class after update as if update(monitor) if ( select monitor from inserted ) not in ( sele

16、ct sno from student where clno = (select clno from deleted ) ) begin print there is not the new monitor in the class rollback transaction end 13.新建product表create table product ( pno char(6) primary key, pname varchar(20) not null, price decimal(7,2) ) 创建仓库表 create table warehouse ( whno char(3) prim

17、ary key, whname varchar(20) not null, whaddress varchar(20) ) 创建库存商品表create table whproduct ( whno char(3) references warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int ) 当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0create tri

18、gger tri_product on product after insert as begin declare pno char(3) select pno=pno from inserted insert into whproduct select whno,pno,0 from warehouse end 当新增商品是，自动生成改仓库所有商品的库存记录，库存数量为0create trigger tri_warehouse on warehouse after insert as begin declare whno char(6) select whno=whno from inser

19、ted insert into whproduct select whno,pno,0 from product end 14.1）用户张勇对Student表和Course表有Select权力。Grant select on student to 张勇Grant select on course to 张勇2）把对表Student的INSERT和Delete权限授予用户张三，并允许他再把此权限授予其他用户。Grant insert,delete on student to 张三with grant option 3）把查询Course表和修改属性Credit的权限授给用户李四。Grant se

20、lect,update(credit) on course to 李四 4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。Grant all privilege on student to 李勇敏 with grant option 5)删除张勇对student表的select权revoke select on student from 张勇;删除张勇对course表的select权revoke select on coursefrom 张勇;6)删除张三对student表的insert、delete权revoke insert,deleteon s

21、tudentfrom 张三cascade;10.创建一存储过程，根据学生学号查询该学生所以选修课的成绩，学号作为参数输入。create procedure proc_StuGmark stu_no char(7) as select Gmark,Cno from Grade where Sno = Stu_no执行过程：execute proc_StuGmark stu_no = 200010111.创建一存储过程，用来输入的参数：班级名，判断该班级中是否已有学生存在，若有，存储过程返回1；若没有，存储过程返回0create procedure proc_IsThereStudent Clas

22、s_clno char(5) as declare count int select count = count(*) from Student where Clno = Class_clno if count = 0 return 0 else return 1执行过程：declare retvalve int execute retvalve = proc_IsThereStudent 00311 if retvalve = 0 Print 该班级没有学生! else Print 该班级有学生!14.10)create function Sno_Gmark(stuno char(7) re

23、turns table as return (select Cno,Gmark from Grade where Sno = stuno)执行过程：select Cno,Gmark from Sno_Gmark(2000101);11)create function class_Stu(classno char(5)returns intasbegindeclare count intselect count = count(*)from Studentwhere Clno = classnoif count 0 set count = 1return (count)end执行过程：declare retvalve int execute retvalve = class_Stu 00311 if retvalve = 0 Print 该班级没有学生! else Print 该班级有学生!