數(shù)據(jù)庫實驗答案分析總結(jié).doc

實驗三用SQL語句完成以下的要求（鍵表及插入數(shù)據(jù)的SQL語句見下面）：create table student(Sno char(5) primary key,Sname char(10),Ssex char(2),Sage int,Sdept char(2);create table Course(Cno char(1) primary key,Cname char(20),Cpno char(1),Ccredit int);create table SC(Sno char(5),Cno char(1),Grade int,primary key (sno,cno);insert into student values(95001,李勇,男,20,CS);insert into student values(95002,劉晨,女,21,IS);insert into student values(95003,王敏,女,18,MA);insert into student values(95004,張力,男,19,IS);insert into Course values(1,數(shù)據(jù)庫,5,4);insert into Course values(2,數(shù)學(xué),NULL,2);insert into Course values(3,信息系統(tǒng),1,4);insert into Course values(4,操作系統(tǒng),6,3);insert into Course values(5,數(shù)據(jù)結(jié)構(gòu),7,4);insert into Course values(6,數(shù)據(jù)處理,NULL,2);insert into Course values(7,PASCAL語言,6,4);insert into SC values(95001,1,92);insert into SC values(95001,2,85);insert into SC values(95001,3,88);insert into SC values(95002,2,90);insert into SC values(95003,3,85);1.查詢信息系（IS）的所有學(xué)生信息select *from Studentwhere Sdept=IS;2.查詢選修了“數(shù)學(xué)”課的所有學(xué)生名單select *from Student,Course,SCwhere Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cname=數(shù)學(xué);3.查詢至少選修了一門其直接先行課為5號課程的學(xué)生的姓名。select Student.Snamefrom Student,Course,SCwhere Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cpno=5 4.查詢?nèi)w學(xué)生的姓名和出生年份。select sname,2013-Student.Sagefrom Student5.查詢所有姓王的學(xué)生。select *from Studentwhere Sname like 王%; 6.查詢選修了3號課程的學(xué)生姓名及成績，并按成績降序排序。select Student.Sname,SC.Gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=So and o=3order by sc.grade desc7.查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。select *from studentorder by sdept, sage desc8.計算2號課程的平均成績。select AVG(grade)from SCwhere cno=2;9.查詢選修了2號課程的學(xué)生的最高成績。select MAX(grade)from SCwhere cno=2;10.求各個課程號及相應(yīng)的選課人數(shù)。select cno,COUNT(distinct sno)from SC group by cno11.查詢至少選修了3門課程以上的學(xué)生序號。select snofrom SCgroup by snohaving COUNT(*)=3;12.查詢“數(shù)據(jù)庫”的間接先行課。select second.cpnofrom Course as first,Course as secondwhere first.cpno=second.Cno And first.Cname=數(shù)據(jù)庫;13.查詢其他系中比信息系某一學(xué)生年齡小的學(xué)生的姓名和年齡。select distinct first.sname,first.sagefrom Student as first, Student as secondwhere first.Sage AVG(second.Grade);16.查詢至少選修了1號課程和3號課程的學(xué)生學(xué)號。(select snofrom SCwhere Cno=1)intersect(select snofrom SCwhere Cno=3);17.查詢只選修了1號課程和3號課程的學(xué)生學(xué)號。select snofrom SCwhere Cno=1and Sno in(select Snofrom SCwhere Cno=2and Sno in(select Snofrom SCgroup by Snohaving COUNT(sno)=2);18.查詢沒有選修1號課程的學(xué)生姓名。select snamefrom student,scwhere sc.sno not in(select snofrom scwhere cno=1) and sc.sno=student.sno19.查詢選修了全部課程的學(xué)生姓名。select snamefrom studentwhere student.sno in (select snofrom sc as onewhere not exists(select *from SC as twowhere not exists(select *from SC as threewhere three.Sno=one.Sno and three.Cno=two.Cno);20.查詢至少選修了95002所選修的全部課程的學(xué)生學(xué)號。select distinct snofrom sc as onewhere not exists(select *from SC as twowhere two.sno=95002 and not exists(select *from SC as threewhere three.Sno=one.Sno and three.Cno=two.Cno);21.建立信息系學(xué)生視圖，并從視圖中查詢年齡最大的學(xué)生記錄。create view IS_student創(chuàng)建視圖as select *from Studentwhere Sdept=IS; select sno查詢信息from IS_studentwhere sage in(select MAX(sage)from IS_student); 實驗四實驗要求：下實驗課后交一份實驗報告，寫明本次實驗所用的SQL語句，并給出執(zhí)行結(jié)果。1.用SQL語句定義表student(sno,sname,ssex,sage)，并加入如下約束：主鍵：sno；sname有唯一約束；sname,ssex,sage都不允許空；2.用SQL語句定義表course(cno,cname)，并加入如下約束：主鍵：cno；cname不允許空；3.用SQL語句定義表sc(sno,cno,cj)，并加入如下約束：主鍵：sno,cno；為sno定義名為lsno的默認(rèn)參照完整性；為cno定義名為lcno的默認(rèn)參照完整性；4.用SQL語句向student表輸入如下元組：(95001,李勇,男,20); (95002,劉晨,女,21);用SQL語句向course表輸入如下元組：(1,數(shù)據(jù)庫);(2,數(shù)學(xué));用SQL語句向sc表輸入如下元組：(95001,1,92);(95001,2,85); (95002,2,90);create table student(sno char(5) primary key,sname char(10) unique not null,ssex char(2) not null,sage int not null);create table course(cno char(1) primary key,cname char(20)not null);create table sc(sno char(5),cno char(1),cj int,primary key (sno,cno),constraint lsno foreign key (sno)references student(sno),constraint lcno foreign key (cno)references course(cno);insert into student(sno,sname,ssex,sage)values(95001,李勇,男,20);insert into student(sno,sname,ssex,sage)values(95002,劉晨,女,21);insert into course(cno,cname) values(1,數(shù)據(jù)庫);insert into course(cno,cname) values(2,數(shù)學(xué));insert into sc(sno,cno,cj)values(95001,1,92);insert into sc(sno,cno,cj)values(95001,2,85);insert into sc(sno,cno,cj)values(95002,2,90);5.執(zhí)行下列語句，并查看執(zhí)行結(jié)果。如果不能正確執(zhí)行給出錯誤原因。insert into student values(95001,張力,男,20);/錯誤的原因是學(xué)號已經(jīng)存在，不允許插入重復(fù)的學(xué)號，違反了主鍵約束insert into student values(95003,李勇,男,20);/錯誤原因是sname屬性不允許出現(xiàn)重復(fù)鍵，違反了unique key約束insert into SC values(95004,1,92);/ INSERT語句與COLUMN FOREIGN KEY 約束 lsno 沖突,因為student 表中不存在這個學(xué)號delete from student where sno=95001;/錯誤的原因是DELETE 語句與 COLUMN REFERENCE 約束 lsno 沖突。Restrict為默認(rèn)選項，凡是被基本表所引用的主鍵不得刪除update course set cno=3 where cno=2;/錯誤的原因是UPDATE 語句與 COLUMN REFERENCE 約束 lcno 沖突。破壞了參照完整性。默認(rèn)的不支持連級修改.6.給student表的ssex列添加名為fm的約束，使其取值只能取男或女。alter table student add constraint fn check(ssex in(男,女);執(zhí)行insert into student values(95005,張力,f,20)，查看執(zhí)行結(jié)果。INSERT 語句與 COLUMN CHECK 約束 fn 沖突。Sage屬性只能為男或女。該語句違反了約束。7.給student表的sage列添加約束，使其年齡不得超過20歲。查看約束是否能正確添加，并分析其原因。不能，ALTER TABLE 語句與 COLUMN CHECK 約束 fn1 沖突。有的數(shù)據(jù)大于20所以不能加上約束！8.刪除約束lsno和lcno。alter table sc drop constraint lcno,lsno;9.為sc表添加在列sno上的外鍵約束lsno1，并定義為級聯(lián)刪除。執(zhí)行delete from student where sno=95001;查看執(zhí)行結(jié)果。alter table sc add constraint lsno1 foreign key(sno)references student on delete cascade;sc中的關(guān)于95001的信息也被刪除掉了。10.為sc表添加在列cno上的外鍵約束lcno1，并定義為級聯(lián)修改。執(zhí)行update course set cno=3 where cno=2;查看執(zhí)行結(jié)果。alter table sc add constraint lcon1foreign key (cno)references course on update cascade;sc中的關(guān)于課程號2的被修改為了3。實驗五有如下兩個表：教師(編號，姓名，性別，職稱，工資，系別編號) 主碼：編號系別（系別編號，系名稱，人數(shù)） 主碼：系別編號create table teacher(tno char(5)primary key,tname char(10),tsex char(2),tpos char(10),tsal int,xno char(4);create table xibie(xno char(4)primary key,xname char(2),xcount int);insert into xibie(xno,xname,xcount)values(1001,CS,0);insert into xibie(xno,xname,xcount)values(1002,IS,0);insert into xibie(xno,xname,xcount)values(1003,NE,0);要求利用觸發(fā)器完成下面的功能：1. 對教師表進(jìn)行插入、刪除操作時維護(hù)系別人數(shù)。create trigger tri_count on teacherfor insertas update xibieset xcount