数据库原理实验答案

第三部分 实验内容
实验一　数据定义和数据更新
实验目的：通过本实验能够熟练应用sql语言进行基本表和索引的定义，能够对表的结构进行修改和删除，并通过数据更新命令输入相应的数据．
实验要求：在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码，这样才能在2个学时完成实验内容。
实验内容：
１． 创建教材的学生－课程数据库中的三个表．定义表时请注意数据类型问题，Oracle的数据类型请参照本实验讲义．
２． 对学生表（student）的结构用alter命令进行修改．
３． 对以上建立的表按关键字建立索引．
４． 输入数据．数据内容参照教材．输入数据时请注意数据类型问题．
５． 对输入的数据进行修改和删除．
思考题：体会oracle数据库的数据存储方法．
实验报告要求：写出创建3个表的命令，修改表结构的命令，删除唯一性约束的命令，创建索引的命令，对每个表添加2个条记录的命令，删除数据的命令。
一、基本表操作
1.建立基本表
创建教材p69页的学生表（student）、学生选课表（SC）、课程表（course）
1）学生表：Student (Sno, Sname, Ssex, Sdept）其中学号Sno（number型 主码）；姓名Sname（char型，长度为8，非空唯一）；Ssex性别（char型，长度为2,只能为男或女）；所在系Sdept（char型）
Create table Student
(Sno number(12) constraint PK_student primary key,
Sname char(8) not null unique,
Ssex char(2),
Sage number(2),
Sdept char(20),
Constraint ck_ss check (Ssex in ('男','女')));
2）课程表：Course (Cno, Cname, Cpno, Ccredit)其中课程号Cno（number型 长度为4 主码）；Cname课程名（char型，长度为20）；Cpno先行课（number型）；Ccredit学分（nember型）；
Create table Course
(Cno number(4) constraint pk_course primary key,
Cname varchar2(20),
Cpno number(2),
Ccredit number(2));
3)?学生选课表：SC(Sno, Cno, Grade)其中学号Sno、课程号Cno为主码；成绩Grade（nember（4，1）型）。
Create table SC
(Sno number(12),
Cno number(4),
Grade number(3),
Constraint pk_SC Primary key (Sno,Cno),
Constraint fk_s Foreign key (sno ) references student(Sno),
Constraint fk_c Foreign key (cno ) references course(Cno)
);
2.修改基本表
Alter table <表名>
[Add <列定义>]|
[Modify <列定义>]|
[Drop colomn <列名>]|
[Add <表约束>]|
[Drop constraint <约束名>]
1）在Student表中加入属性Sage（number型）。
Alter table Student add Sage number not null;
2）修改某个表的属性的数据类型。
Alter table student Add s_entrance date;
Alter table student Drop column s_entrance;
Alter table student Modify sage char(3);
Alter table student Modify sage not null;
Alter table sc Add constraint ck_sc check(grade>=0 and gra

de<=100);
Alter table sc Drop constraint ck_sc;
如原来的表student中sname属性为not null default约束，现在通过alter命令修改为可以取空，具体SQL语句为 alter table student modify sname null；
3.删除基本表
1) 删除基本表Student
Drop table student cascade constraints;
2）删除基本表SC
Drop table sc;
二、索引操作
Create [Unique][Cluster] Index <索引名> On <表名>（<列名>[<次序>][,<列名>[<次序>] ]…）;
Drop Index <索引名>
1.建立索引
1）在Course表上建立关于Cname的唯一索引
Create unique index o on Course (cname asc);
Create unique index oo on sc(sno asc,cno desc);
2.删除索引
1）删除Course表上的索引
Drop index o;
2）删除SC表上的索引
Drop index oo;
（二）数据操作
一、数据更新
1.插入数据
1）向Student表中插入数据
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(200215121,'李勇','男',20,'CS');
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(200215122,'刘晨','女',19,'CS');
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(200215123,'王敏','女',18,'MA');
Insert Into Student(Sno,Sname,Ssex,Sage,Sdept)
Values(200215125,'张立进','男',22,'IS');
2）向Course表中插入数据
Insert Into Course(Cno,Cname, Ccredit)
Values(1,'数据库原理',2);
Insert Into Course(Cno,Cname, Ccredit)
Values(2,'数学',2);
Insert Into Course(Cno,Cname,Cpno, Ccredit)
Values(3,'信息系统',1,4);
Insert Into Course(Cno,Cname,Cpno, Ccredit)
Values(4,'操作系统',6,3);
Insert Into Course(Cno,Cname,Cpno,Ccredit)
Values(5,'数据结构',7,4);
Insert Into Course(Cno,Cname,Ccredit)
Values(6,'数据处理',2);
Insert Into Course(Cno,Cname,Cpno,Ccredit)
Values(7,'PASCAL语言',6,4);
3）向SC表中插入数据
Insert Into SC(Sno,Cno, Grade) Values(200215121,1,92);
Insert Into SC(Sno,Cno, Grade) Values(200215121,2,85);
Insert Into SC(Sno,Cno, Grade) Values(200215121,3,88);
Insert Into SC(Sno,Cno, Grade) Values(200215122,2,90);
Insert Into SC(Sno,Cno, Grade) Values(200215122,3,80);

Insert Into SC(Sno,Cno, Grade) Values(200215121,4,92);
Insert Into SC(Sno,Cno, Grade) Values(200215121,5,85);
Insert Into SC(Sno,Cno, Grade) Values(200215121,6,88);
Insert Into SC(Sno,Cno, Grade) Values(200215123,2,90);
Insert Into SC(Sno,Cno, Grade) Values(200215123,3,80);

Insert Into SC(Sno,Cno, Grade) Values(200215121,7,92);
Insert Into SC(Sno,Cno, Grade) Values(200215121,4,92);
Insert Into SC(Sno,Cno, Grade) Values(200215123,1,85);
Insert Into SC(Sno,Cno, Grade) Values(200215123,4,88);
Insert Into SC(Sno,Cno, Grade) Values(200215122,1,90);
Insert Into SC(Sno,Cno, Grade) Values(200215122,5,80);


2.修改数据
Update <表名>
Set <列名1>＝<值1>，<列名2>＝<值2>，…
Where <条件>

Update Student Set Ssex='女', sage=23 Where sname='李勇';
4. 删除数据
Delete From <表名>Where <条件>

Update Student Set sage=sage+1;
Delete from Course Where cno= '1';

实验二 数据查询
实验目的：通过本实验能够应用sql语言进行基本表查询，并实现对视图的定义和查询．
实验要求：在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码　．
实验内容
1．对实验一建立的学生-课程数据库中的表进行单表查询
1）查询全体学生的详细记录。
select * from student;
2）查询全体学生的姓名及其出生年份。
select sname, 2007-sage sbirth from student;
3）查询年龄不在20至23岁之间的学生姓名、系别和年龄。
select sname,sdept,sage from student where sage not between 20 and 23;
4）查询所有姓刘的学生的姓名、学号和性别。
select sname,sno,ssex from student where sname like '刘%';
5）查询学习1号课程的学生的最高分数。
select max(grade) from sc where cno=2;
6）查名字中第二个字为“阳”字的学生的姓名和学号。
select sname, sno from student where sname like '_敏%';
7）查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
select sno,grade from sc where cno='3' order by grade desc;
8）查询各个课程号与相应的选课人数。
select cno, count(sno) from sc group by cno;
2． 连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。
1）查询每个学生的情况以及他所选修的课程。
select * from student, sc where student.sno=sc.sno;
2）求学生的学号、姓名、选修的课程名及成绩。
select student.sno, sname,cname,grade from student,course,sc where student.sno=sc.sno and https://www.360docs.net/doc/6f16511970.html,o=https://www.360docs.net/doc/6f16511970.html,o;
3）求选修课程号为'1'且成绩在90分以上的学生学号、姓名和成绩。
select student.sno,sname,grade from student,sc where student.sno=sc.sno and cno='1' and grade>90;
4）查询每一门课程的间接先行课。
select https://www.360docs.net/doc/6f16511970.html,ame, https://www.360docs.net/doc/6f16511970.html,ame from course, course course1 where course.cpno=https://www.360docs.net/doc/6f16511970.html,o;
SELECT https://www.360docs.net/doc/6f16511970.html,o,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=https://www.360docs.net/doc/6f16511970.html,o;
5)查询与'刘晨'在同一个系学习的学生。
select s1.* from student s1,student s2 where s1.sdept=s2.sdept and s2.sname='刘晨';
查询选修了课程名为'信息系统'的学生学号和姓名。
select student.sno,sname from student,sc,course where student.sno=sc.sno and https://www.360docs.net/doc/6f16511970.html,o=https://www.360docs.net/doc/6f16511970.html,o and https://www.360docs.net/doc/6f16511970.html,ame='信息系统';
Select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'));

3．嵌套查询。 对实验一建立的学生-课程数据库中三个表进行嵌套查询。
1) 求1课程的成绩高于刘晨的学生学号和成绩。
Select student.sno,sname,grade from student,sc where grade>all(select grade from sc where sno=(select sno from student where sname='刘晨' ))and cno='1'and
student.sno=sc.sno;
2)

求选修了信息系统的学号和姓名。
Select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'));
3) 求没有选修信息系统课程的学生姓名；
Select sno,sname from student where sno not in (select sno from sc where cno in (select cno from course where cname='信息系统'));
4) 查询选修了全部课程的学生姓名。(查询这样的学生，没有一门课程是他不选修的)
Select sname from student where not exists(select * from course where not exists(select * from SC where sno=student.sno and cno=https://www.360docs.net/doc/6f16511970.html,o));
所有学生都选修的课程名
Select cname from course where not exists(select * from student where not exists(select * from sc where sno=student.sno and cno=https://www.360docs.net/doc/6f16511970.html,o));
5) 查询选修了学号为“200215122”的学生所选修全部课程的学生学号和姓名。（不存在这样的课程y，学生95002选修了y，而学生x没有选修）
Select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='200215122'and not exists(select * from sc scz where scz.sno=scx.sno and https://www.360docs.net/doc/6f16511970.html,o=https://www.360docs.net/doc/6f16511970.html,o));
Select student.sno,sname from student where sno in (Select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='0215122'and not exists(select * from sc scz where scz.sno=scx.sno and https://www.360docs.net/doc/6f16511970.html,o=https://www.360docs.net/doc/6f16511970.html,o)));
6) 求选修课程超过3门的学生的学号和姓名。
Select student.sno,sname from student where exists(select sno,count(cno) from sc group by sno having count(cno)>2 and student.sno=sc.sno);
外连接例子：
列出每个学生的基本情况及其选课情况。
Select student.sno,sname,sage,ssex,sdept,cno,grade from student,sc where student.sno=sc.sno(+);
4．建立视图，并对视图执行查询操作．
1) 建立信息系学生的视图；
Create view is_student as select sno,sname,sage,sdept from student where sdept='IS';
2) 建立信息系选修了1号课程的学生的视图；
Create view is_s1 (sno, sname, grade) as select student.sno,sname,grade from student,sc where sc.sno=student.sno and sdept='IS'and https://www.360docs.net/doc/6f16511970.html,o='1';
3) 将学生的学号及其平均成绩定义为一个视图；
Create view s_c (sno,gavg) as select sno,avg(grade) from sc group by sno ;
4) 将Student表中所有女生记录定义为一个视图；
Create view f_student(f_sno,name,sex,age,dept) as select * from student where ssex='女';
思考题：视图的消解过程。
实验报告要求：每大题选择最后2小题，写出相应的命令
实验三 oracle的安全性和完整性控制
实验目的：通过本实验能够熟练应用sql语言进行用户权限的授予和回收，并能定义实体完整性，参照完整性及用户定义的完整性的定义，并体会oracle数据库系统在安全性和完整性保护方面的特性．
实验要求：在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码。
实

验内容：　1．两个用户相互配合完成以下实验内容。
1) 用户std01把查询Student表权限授给用户std02，std02执行相应的查询。
Grant select on student to std02;
connect std02/std02
a) 查询std01用户的全体学生的详细记录；
select * from std01.student;
b) 查询std01用户的年龄不在20至23岁之间的学生姓名、系别和年龄；
select sname,sdept,sage from std01.student where sage not between 20 and 23;
c) 查询std01用户的所有姓刘的学生的姓名、学号和性别；
d) 查询std01用户的名字中第二字为“阳”字的学生的姓名和学号。
2) std01把对Student表和Course表的全部权限授予用户std02 ，std02修改std01的数据。
Grant all privileges on course to std02;
Grant all privileges on student to std02;
3) std01把对表SC的查询权限授予所有用户。
Grant select on student to public;
a) 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列；
b) 查询各个课程号与相应的选课人数。
4) 用户std01把Student表和修改学生学号的权限授给用户 std02。
5) 从用户std02把授予出的的权限一一收回。

2．以下操作每个同学独立完成
1） 建立部门表DEPT，要求部门名称Dname列值取值唯一，部门编号Depno列为主码。
Create table DEPT
(Deptno char(7) constraint PK_dept primary key,
dname char(20) unique
);
2） 建立学生登记表student，要求学号在900000至999999之间，年龄<29,，性别只能是'男'或'女'，姓名非空。
Create table S
(Sno number constraint PK_s check (sno between 900000 and 999999),
Sname char(20) not null,
Ssex char(2),
Sage number,
Constraint ck_sex check (Ssex in ('男','女')),
Constraint ck_sage check (Sage<29)
);
3） 建立职工表EMP，要求每个职工的应发工资不得超过3000元。应发工资实际上就是实际发工资列Sal与扣除项Deduct之和。
Create table EMP
(a1 number,
Sal number,
Deduct number,
constraint PK_s9 check (a1<3000 )
);



4) 修改学生成绩数据库的3个表的定义，分别为每个表建立主码和外码，并输入数据，验证实体完整性和参照完整性；
5）对上述新建立和修改定义的表，每个表输入3条数据，其中1条数据符合完整性约束，2条违反约束条件的，验证和体会Oracle完整性。
实验报告要求：写出每题相应的命令