大数据库原理张红娟问题详解

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'的学生的成绩记录：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='01311' and Ssex='女';（3）找出01311班和01312班的学生姓名、姓名、出生年份select Sname,Ssex,2014-Sage as [year of birth]from Studentwhere Clno='01311' or Clno='01312';（4）找出所有姓李的学生的个人信息select * from Student where Sname like '李%';（5）找出学生李勇所在班级的学生人数select numberfrom student inner join classon student.clno=class.clnowhere 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='操作系统'（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='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 studentwhere 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 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)找出选修了全部课程的学生姓名select Sname 姓名 from Studentwhere not exists(select * from Coursewhere not exists(select * from Gradewhere Student.Sno=Grade.Snoand o=o))14.1)查询选修了3号课程的学生学号及成绩,并按成绩的降序排列select Sno 学号,Gmark 成绩 from Gradewhere Cno='3'order by Gmark desc2)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列select * from Studentorder by Clno asc ,Sage desc3)求每个课程号及相应的选课人数select o 课程号,COUNT(o) 选课人数from Grade join Course on o=ogroup by o4)查询选修了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 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=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 Class 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') endfrom Class16.1、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 student4.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','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(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 classadd constraint monitor foreign key (monitor) references student(sno)创建grade表create table grade(snochar(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 ondelete cascade,gmark decimal(4,1) check(gmark>0 and gmark<100),primary key (sno,cno))11.插入create trigger stu_inserton studentafter insertasupdate classset number=number+1from class,insertedwhere class.clno = inserted.clno;删除create trigger stu_deleteon studentafter deleteasupdate classset number=number-1from class,deletedwhere class.clno = deleted.clno;12.create trigger stu_updateon 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 class'rollback transactionend13.新建product表create table product( 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, pno char(6) references product(pno) on delete cascade on update cascade, number int )当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0create trigger tri_producton productafter insertasbegindeclare @pno char(3)select @pno=pno from insertedinsert into whproductselect whno,@pno,0from warehouseend当新增商品是，自动生成改仓库所有商品的库存记录，库存数量为0create trigger tri_warehouseon warehouseafter insertasbegindeclare @whno char(6)select @whno=whno from insertedinsert into whproductselect @whno,pno,0from productend14.1）用户张勇对Student表和Course表有Select权力。