数据库原理张红娟答案

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=70where Sno=2001110;（5 ）删除学号为 20

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

3、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 李 %;感谢下载载精品（5 ）找出学生李勇所在班级的学生人数select numberfrom student inner join classon student.clno=class.clnowhere

4、sname=李勇 （6 ）找出课程名为操作系统的平均成绩、最高分、最低分select 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=操作系统 感谢下载载精

5、品（9 ）找出 2000 级计算机软件班的成绩为空的学生姓名select Sname学生姓名from(Student inner join class on student.clno=class.clno)inner join grade on student.sno=grade.snowhere Speciality=计算机软件 and inyear=2000and gmark is null13.1) 找出和李勇在同一个班级的学生信息select *from Studentwhere clno in(select Clno from Studentwhere Sname=李勇 )2) 找出

6、所有与学生李勇有相同选修课程的学生信息select * from Student感谢下载载精品where 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 S

7、no学号 ,Sname姓名from Studentwhere sno in(select sno from Gradewhere Cno in(select Cno from Coursewhere cno in (select cno from coursewhere Cname=操作系统 );感谢下载载精品5) 找出没有选修 1 号课程的所有学生姓名select Sname姓名from Studentwhere not exists(select * from Gradewhere Student.Sno=Grade.Sno and Cno=1)6) 找出选修了全部课程的学生姓名selec

8、t Sname姓名from Studentwhere not exists(select *from Coursewhere not exists(select * from Gradewhere Student.Sno=Grade.Snoand Go=o)14.1) 查询选修了 3 号课程的学生学号及成绩 ,并按成绩的降序排列select Sno学号 ,Gmark成绩from Gradewhere Cno=3感谢下载载精品order by Gmark desc2) 查询全体学生信息 ,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列select * from Studentorder

9、 by Clno asc ,Sage desc3) 求每个课程号及相应的选课人数select Grade.Cno课程号 ,COUNT(Grade.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 fr

10、om Studentwhere Clno=01311)2) 删除 2001 级计算机软件的全体学生的选课记录delete from Gradewhere Sno in(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=

11、Number-1感谢下载载精品where Clno in(select Clno from Studentwhere Sname=李勇 )update Class set Monitor=casewhen 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 C

12、lass add Cage smallint nullupdate Class 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)end感谢下载载精品from Class16.1 、create view st

13、u_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 gmark19905.select *from stu_yearwhere sno in(select snofrom stu_01311_2)第四章10.创建 course表create table course(cno ch

14、ar(1) primary key,cname varchar(20) not null,credit smallint check (credit in (1,2,3,4,5,6,7)创建 class 表感谢下载载精品create 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

15、.插入create trigger stu_inserton studentafter insertasupdate classset number=number+1from class,insertedwhere class.clno = inserted.clno;删除create trigger stu_delete感谢下载载精品on studentafter deleteasupdate classset number=number-1from class,deletedwhere class.clno = deleted.clno;12.create trigger stu_upda

16、teon classafter updateasif update(monitor)if( select monitor from inserted ) not in( select sno from studentwhere clno = (select clno from deleted ) )beginprint there is not the new monitor in the classrollback transaction感谢下载载精品end13.新建 product表create table product( pno char(6) primary key,pname va

17、rchar(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,pno char(6) references product(pno) on delete c

18、ascade on update cascade,number int )当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0create trigger tri_product感谢下载载精品on productafter insertasbegindeclare pno char(3)select pno=pno from insertedinsert into whproductselect whno,pno,0from warehouseend当新增商品是，自动生成改仓库所有商品的库存记录，库存数量为0create trigger tri_warehouseon warehou

19、seafter insertasbegindeclare whno char(6)select whno=whno from insertedinsert into whproductselect whno,pno,0from productend感谢下载载精品14.1 ）用户张勇对Student表和 Course表有 Select权力。Grant select on student to张勇Grant select on course to张勇2 ）把对表Student的 INSERT 和 Delete权限授予用户张三，并允许他再把此权限授予其他用户。Grant insert,delete

20、on student to张三with grant option3 ）把查询Course表和修改属性Credit的权限授给用户李四。Grant select,update(credit) on course to李四4 ）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。Grant all privilege on student to李勇敏with grant option5) 删除张勇对student表的 select权revoke selecton studentfrom张勇 ;感谢下载载精品删除张勇对course表的 select 权revoke

21、 selecton coursefrom张勇 ;6) 删除张三对student表的 insert 、 delete权revoke insert,deleteon studentfrom张三cascade;10. 创建一存储过程，根据学生学号查询该学生所以选修课的成绩，学号作为参数输入。create procedure proc_StuGmarkstu_no char(7)asselect Gmark,Cnofrom Gradewhere Sno = Stu_no执行过程： execute proc_StuGmark stu_no = 2000101感谢下载载精品11. 创建一存储过程，用来输入的参数：班级名，判断该班级中是否已有学生存在，若有，存储过程返回 1 ；若没有，存储过程返回 0create procedure proc_IsThereStudent Class_clno char(5