数据库实验答案分析总结.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 Student
where Sdept='IS';
2.查询选修了“数学”课的所有学生名单
select *
from Student,CourseSC
where Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cname='数学';

3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。
select Student.Sname
from Student,Course,SC
where Student.Sno=SC.Sno And Course.Cno=SC.Cno And Course.Cpno='5'

4.查询全体学生的姓名和出生年份。
select sname,2013-Student.Sage
from Student

5.查询所有姓王的学生。
select *
from Student
where Sname like '王%';
6.查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。
select Student.Sname,SC.Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=So and o='3'
order by sc.grade desc

7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select *
from student
order by sdept, sage desc
8.计算2号课程的平均成绩。
select AVG(grade)
from SC
where cno='2';
9.查询选修了2号课程的学生的最高成绩。
select MAX(grade)
from SC
where cno='2';
10.求各个课程号及相应的选课人数。
select cno,COUNT(distinct sno)
from SC
group by cno
11.查询至少选修了3门课程以上的学生序号。
select sno
from SC
group by sno
having COUNT()>=3;
12.查询“数据库”的间接先行课。
select second.cpno
from Course as first,Course as second
where first.cpno=second.Cno And first.Cname='数据库';
13.查询其他系中比信息系某一学生年龄小的学生的姓名和年龄。
select distinct first.sname,first.sage
from Student as first, Student as second
where first.Sage9、S' and first.Sdept!='IS';

14.查询出成绩最低学号最大的学生学号。
select max(sno)
from SC
where Grade=(
select min(grade)
from SC);
15.查询成绩高于学生平均成绩的记录。
select first.Grade
from SC as first, SC as second
group by first.Grade
having first.Grade> AVG(second.Grade);
16.查询至少选修了1号课程和3号课程的学生学号。
(select sno
from SC
where Cno='1')
intersect
(select sno
from SC
where Cno='3');
17.查询只选修了1号课程和3号课程的学生学号。
select sno
from SC
where Cno='1'and Sno in(
select Sno
from SC
where Cno='2'and Sno in(
select Sno
from SC
group by Sno
having COUNT(sno)=2));
18.查询没有选修1号课程的学生姓名。
select sname
from student,sc
where sc.sno not in
(select sno
from sc
where cno='1') and sc.sno=student.sno

19.查询选修了全部课程的学生姓名。
select sname
from student
where student.sno in
(
select sno
from sc as one
where not exists
(select *
from SC as two
where not exists
(select *
from SC as three
where three.Sno=one.Sno and three.Cno=two.Cno)));
20.查询至少选修了95002所选修的全部课程的学生学号。
select distinct sno
from sc as one
where not exists
(select *
from SC as two
where two.sno='95002' and not exists
(select *
from SC as three
where three.Sno=one.Sno and three.Cno=two.Cno));
21.建立信息系学生视图，并从视图中查询年龄最大的学生记录。
create view IS_student创建视图
as select *
from Student
where Sdept='IS';
select sno查询信息
from IS_student
where 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 lcon1
foreign 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 teacher
for insert
as
update xibie
set xcount=xcount+1
where xno=
(select xno
from inserted);

create trigger tri_count1 on teacher
for delete
as
update xibie
set xcount=xcount-1
where xno=(
select xno
from deleted);
2. 教授工资不得低于1500。
create trigger tri_salary on teacher
for update,insert
as
if(
select COUNT (*)
from inserted
where not exists
(select *
from inserted
where inserted.tpos='教授' and inserted.tsal<1500))=0
rollback transaction

3. 工资只能增加不能减少。
create trigger tri_salary1 on teacher
for update
as
if(
select COUNT(*)
from inserted,deleted
where deleted.tsal