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分：upda

2、te Gradeset Gmark='70' 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）找出所有被學

3、生選修了的課程號：select distinct 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 Stude

4、nt where Sname like '李%'（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）選

5、修了課程的學生人數;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 Sp

6、eciality='計算機軟件' 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 (s

7、elect 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 Grade where Cno in(select Cno from Cours

8、e 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 Coursewhere not exists(sele

9、ct * 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 by Clno asc ,Sage desc3)求每個課程號及相應的選課人數select Grade.Cno 課程號,COUNT(Grade.Cno) 選課

10、人數 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 S

11、no 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

12、 Sname='李勇')update 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

13、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

14、')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(ge

15、tdate()-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&

16、#39;,'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(

17、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 class add constraint monitor foreign key (mo

18、nitor) references student(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 g

19、mark<100), primary key (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

20、.clno = deleted.clno;12.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.新建

21、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

22、action on update cascade, 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 war

23、ehouse end 當新增商品是，自動生成改倉庫所有商品的庫存記錄，庫存數量為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 cours

24、e to 張勇2）把對表Student的INSERT和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)刪除張勇對s

25、tudent表的select權revoke select 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.創建一存儲過程，用來輸入的參數：班級名，判斷該班級中是否已有學生存在，若有，存儲