数据库实验答案分析总结.doc

实验三用SQL语句完成以下的要求（键表及插入数据的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,数据库,5,4);insert into Course values(2,数学,NULL,2);insert into Course values(3,信息系统,1,4);insert into Course values(4,操作系统,6,3);insert into Course values(5,数据结构,7,4);insert into Course values(6,数据处理,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）的所有学生信息select *from Studentwhere Sdept=IS;2.查询选修了“数学”课的所有学生名单select *from Student,Course,SCwhere Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cname=数学;3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。select Student.Snamefrom Student,Course,SCwhere Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cpno=5 4.查询全体学生的姓名和出生年份。select sname,2013-Student.Sagefrom Student5.查询所有姓王的学生。select *from Studentwhere Sname like 王%; 6.查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。select Student.Sname,SC.Gradefrom Student,Course,SCwhere Student.Sno=SC.Sno and Course.Cno=So and o=3order by sc.grade desc7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select *from studentorder by sdept, sage desc8.计算2号课程的平均成绩。select AVG(grade)from SCwhere cno=2;9.查询选修了2号课程的学生的最高成绩。select MAX(grade)from SCwhere cno=2;10.求各个课程号及相应的选课人数。select cno,COUNT(distinct sno)from SC group by cno11.查询至少选修了3门课程以上的学生序号。select snofrom SCgroup by snohaving COUNT(*)=3;12.查询“数据库”的间接先行课。select second.cpnofrom Course as first,Course as secondwhere first.cpno=second.Cno And first.Cname=数据库;13.查询其他系中比信息系某一学生年龄小的学生的姓名和年龄。select distinct first.sname,first.sagefrom Student as first, Student as secondwhere first.Sage AVG(second.Grade);16.查询至少选修了1号课程和3号课程的学生学号。(select snofrom SCwhere Cno=1)intersect(select snofrom SCwhere Cno=3);17.查询只选修了1号课程和3号课程的学生学号。select snofrom SCwhere Cno=1and Sno in(select Snofrom SCwhere Cno=2and Sno in(select Snofrom SCgroup by Snohaving COUNT(sno)=2);18.查询没有选修1号课程的学生姓名。select snamefrom student,scwhere sc.sno not in(select snofrom scwhere cno=1) and sc.sno=student.sno19.查询选修了全部课程的学生姓名。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所选修的全部课程的学生学号。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.建立信息系学生视图，并从视图中查询年龄最大的学生记录。create view IS_student创建视图as select *from Studentwhere Sdept=IS; select sno查询信息from IS_studentwhere sage in(select MAX(sage)from IS_student); 实验四实验要求：下实验课后交一份实验报告，写明本次实验所用的SQL语句，并给出执行结果。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的默认参照完整性；为cno定义名为lcno的默认参照完整性；4.用SQL语句向student表输入如下元组：(95001,李勇,男,20); (95002,刘晨,女,21);用SQL语句向course表输入如下元组：(1,数据库);(2,数学);用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,数据库);insert into course(cno,cname) values(2,数学);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.执行下列语句，并查看执行结果。如果不能正确执行给出错误原因。insert into student values(95001,张力,男,20);/错误的原因是学号已经存在，不允许插入重复的学号，违反了主键约束insert into student values(95003,李勇,男,20);/错误原因是sname属性不允许出现重复键，违反了unique key约束insert into SC values(95004,1,92);/ INSERT语句与COLUMN FOREIGN KEY 约束 lsno 冲突,因为student 表中不存在这个学号delete from student where sno=95001;/错误的原因是DELETE 语句与 COLUMN REFERENCE 约束 lsno 冲突。Restrict为默认选项，凡是被基本表所引用的主键不得删除update course set cno=3 where cno=2;/错误的原因是UPDATE 语句与 COLUMN REFERENCE 约束 lcno 冲突。破坏了参照完整性。默认的不支持连级修改.6.给student表的ssex列添加名为fm的约束，使其取值只能取男或女。alter table student add constraint fn check(ssex in(男,女);执行insert into student values(95005,张力,f,20)，查看执行结果。INSERT 语句与 COLUMN CHECK 约束 fn 冲突。Sage属性只能为男或女。该语句违反了约束。7.给student表的sage列添加约束，使其年龄不得超过20岁。查看约束是否能正确添加，并分析其原因。不能，ALTER TABLE 语句与 COLUMN CHECK 约束 fn1 冲突。有的数据大于20所以不能加上约束！8.删除约束lsno和lcno。alter table sc drop constraint lcno,lsno;9.为sc表添加在列sno上的外键约束lsno1，并定义为级联删除。执行delete from student where sno=95001;查看执行结果。alter table sc add constraint lsno1 foreign key(sno)references student on delete cascade;sc中的关于95001的信息也被删除掉了。10.为sc表添加在列cno上的外键约束lcno1，并定义为级联修改。执行update course set cno=3 where cno=2;查看执行结果。alter table sc add constraint lcon1foreign key (cno)references course on update cascade;sc中的关于课程号2的被修改为了3。实验五有如下两个表：教师(编号，姓名，性别，职称，工资，系别编号) 主码：编号系别（系别编号，系名称，人数） 主码：系别编号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);要求利用触发器完成下面的功能：1. 对教师表进行插入、删除操作时维护系别人数。create trigger tri_count on teacherfor insertas update xibieset xcount