1、实验三用 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 i
2、nto 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 Co
3、urse 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 valu
4、es(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
5、.Cno And Course.Cpno=54.查询全体学生的姓名和出生年份。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.查询全体学生情况，
6、查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。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
7、 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.查询至少选修了
8、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
9、.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 所选修的
10、全部课程的学生学号。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查询信息fro
11、m 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,
12、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 cha
13、r(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 stude
14、nt(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(s
15、no,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 冲突,因为 st
16、udent 表中不存在这个学号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
17、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 dr
18、op 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
19、;查看执行结果。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
20、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
21、 update xibieset xcount=xcount+1where xno=(select xnofrom inserted);create trigger tri_count1 on teacherfor deleteasupdate xibieset xcount=xcount-1where xno=(select xnofrom deleted);2. 教授工资不得低于 1500。create trigger tri_salary on teacherfor update,insertas if(select COUNT (*)from insertedwhere not exi
22、sts (select *from inserted where inserted.tpos=教授 and inserted.tsal1500)=0rollback transaction3. 工资只能增加不能减少。create trigger tri_salary1 on teacherfor updateasif(select COUNT(*)from inserted,deletedwhere deleted.tsalinserted.tsal)=0rollback transaction4.删除系别时，用触发器实现级联删除。实验六综合实验：任选一开发工具设计一个 C/S 结构的“学生管理系统” ，全面熟悉与领会本门课程所学习的内容，从实际系统开发中领会数据库完整性的意义和数据库设计理论的意义与设计过程，掌握 SQL 嵌入式使用方法，领会嵌入式使用的灵活性。
