数据库实验答案分析总结.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,Course,SC
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.Sage<second.Sage and second.Sdept='IS' 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<inserted.tsal)=0
rollback transaction
4.删除系别时，用触发器实现级联删除。

实验六
综合实验：任选一开发工具设计一个C/S结构的“学生管理系统”，全面熟悉与领会本门课程所学习的内容，从实际系统开发中领会数据库完整性的意义和数据库设计理论的意义与设计过程，掌握SQL嵌入式使用方法，领会嵌入式使用的灵活性。