数据库原理张红娟答案

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='7

2、0' where Sno='2001110'（5）删除学号为'2001110'的学生的成绩记录：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

3、 Cnofrom Grade;（2）找出01311班女学生的个人信息：select * from Studentwhere Clno='01311' and Ssex='女'（3）找出01311班和01312班的学生姓名、姓名、出生年份select Sname,Ssex,2014-Sage as year of birthfrom Studentwhere Clno='01311' or Clno='01312'（4）找出所有姓李的学生的个人信息select * from Student where Sname like '

4、;李%'（5）找出学生李勇所在班级的学生人数select number from student inner join class on student.clno=class.clno where sname='李勇'（6）找出课程名为操作系统的平均成绩、最高分、最低分select AVG(Gmark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分 from Gradewhere Cno in(select Cno from Coursewhere Cname='操作系统')（7）选修了课程的学生人数;select COUNT(d

5、istinct sno) 学生人数from Grade（8）选修了操作系统的学生人数;select COUNT(sno) 学生人数from course inner join gradeon o=owhere Cname='操作系统'（9）找出2000级计算机软件班的成绩为空的学生姓名select Sname 学生姓名 from (Student inner join class on student.clno=class.clno)inner join grade on student.sno=grade.snowhere Speciality='计算机软件'

6、 and inyear='2000' and gmark is null13.1)找出和李勇在同一个班级的学生信息select * from Studentwhere clno in(select Clno from Studentwhere Sname='李勇')2)找出所有与学生李勇有相同选修课程的学生信息select * from Studentwhere sno in(select sno from gradewhere cno in(select cno from gradewhere sno in (select sno from studentwh

7、ere Sname='李勇');3)找出年龄介于学生李勇和25岁之间的学生信息select * from Studentwhere Sage <25 and Sage>(select Sage from Student where Sname='李勇') 4)找出选修了课程是操作系统的学生学号和姓名select Sno 学号,Sname 姓名 from Studentwhere sno in(select sno from Grade where Cno in(select Cno from Course where cno in (select c

8、no 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 Coursewhere not exists(select * from Gradewhere Stu

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

10、e 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(select Sno from St

11、udentwhere 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='李勇')upda

12、te Class set Monitor=case 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 Clas

13、s set Cage=casewhen Clno='00311' then (select AVG(Sage) from Student 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、

14、create view stu_01311_1as select student sno,sname,gmarkfrom student,gradewhere cno=1and clno=01311and student.sno=grade.snowith check option2、create view stu_01311_2an select *from stu_01311_1where gmark<603.create view stu_year(sno,sname,years)as select sno,sname,year(getdate()-sagefrom student

15、4.select snamefrom stu_yearwhere years>19905.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&

16、#39;,'7') ) 创建class表create table class ( clno char(5) primary key, speciality varchar(20) not null, inyear char(4) not null, number integer check(number>1 and number<300), monitor char(7) ) 创建student表create table student ( sno char(7) primary key, sname varchar(20) not null, ssex char(

17、2) not null default '男' check (ssex in ('男','女'), sage smallint check (sage>14 and sage <65), clno char(5) not null foreign key(clno) references class(clno) on update cascade ) 为class添加参照完整性alter table class add constraint monitor foreign key (monitor) references studen

18、t(sno)创建grade表create table grade ( sno char(7) not null foreign key (sno) references student(sno) on update cascade on delete cascade, cno char(1) not null foreign key (cno) references course (cno) on update cascade on delete cascade, gmark decimal(4,1) check(gmark>0 and gmark<100), primary ke

19、y (sno,cno) ) 11.插入create trigger stu_insert on student after insert as update class set number=number+1 from class,inserted 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.

20、create trigger stu_update on class after update as if update(monitor) if ( select monitor from inserted ) not in ( select 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 pro

21、duct ( pno char(6) primary key, pname varchar(20) not null, price decimal(7,2) ) 创建仓库表 create table warehouse ( whno char(3) primary 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

22、, pno char(6) references product(pno) on delete cascade on update cascade, number int ) 当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0create trigger 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 当新增商品是，自动生成改仓

23、库所有商品的库存记录，库存数量为0create trigger tri_warehouse on warehouse after insert as begin declare whno char(6) select whno=whno from inserted 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的INSE

24、RT和Delete权限授予用户张三，并允许他再把此权限授予其他用户。Grant insert,delete on student to 张三with grant option 3）把查询Course表和修改属性Credit的权限授给用户李四。Grant select,update(credit) on course to 李四 4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。Grant all privilege on student to 李勇敏 with grant option 5)删除张勇对student表的select权revoke se

25、lect on student from 张勇;删除张勇对course表的select权revoke select on coursefrom 张勇;6)删除张三对student表的insert、delete权revoke insert,deleteon studentfrom 张三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 = '2000101'11.创建一存储过程，用来输入的参数：班级名，判断该班级中是否已有学生存在，若