Oracle数据库实验

一、进入Oracle数据库环境

sqlplus 用户名/密码

然后输入用户名和密码。

二、实验一

1. 实验内容：SQL定义功能、数据插入
2. 建立教学数据库的三个基本表：

S(Sno,Sname,Sgender,Sage,Sdept) 学生（学号，姓名，性别，年龄，系）
SC(Sno,Cno,Grade)选课（学号，课程号，成绩）
C(Cno,Cname,Cpno,Ccredit) 课程（课程号，课程名，先行课，学分）

按以下顺序分别建表

create table S(
	Sno	NUMBER(9)	primary key,
	Sname	VARCHAR(20)	not null,
	Sgender	VARCHAR(10)	default '男' check(Sgender in ('男', '女')),
	Sage	NUMBER(2),
	Sdept	VARCHAR(10)
);

create table C(
	Cno	NUMBER(9)	primary key,
	Cname	VARCHAR(10)	not null,
	Cpno	NUMBER(9),
	Ccredit	NUMBER(2),
	foreign key (Cpno) references C(Cno)
);

create table SC(
	Sno	NUMBER(9),
	Cno	NUMBER(9),
	Grade	NUMBER(3),
	foreign key (Sno) references S(Sno),
	foreign key (Cno) references C(Cno)
);

3. DROP TABLE、ALTER TABLE、CREATE INDEX、DROP INDEX 及INSERT语句输入数据

• 删除表S和他所有的约束

drop table S CASCADE CONSTRAINTS;

• 将C表的Ccredit改为NUMBER(3)

alter table C modify Ccredit NUMBER(3);

• 为表SC的Sno和Cno建立索引

create index indexname on SC(Sno, Cno);

• 删除刚才建立的索引

drop index indexname;

• 为三个表插入数据

insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630301, 'King', '男', 20, 'B');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630302, 'Ming', '女', 21, 'C');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630303, 'Ting', '男', 20, 'A');
insert into S(Sno, Sname, Sgender, Sage, Sdept) VALUES(161630304, 'Bing', '男', 22, 'C');

insert into C(Cno, Cname, Ccredit) VALUES(123456789, '概率论', 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(223456789, '计算机基础', 123456789, 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(323456789, '数理统计', 123456789, 3);
insert into C(Cno, Cname, Cpno, Ccredit) VALUES(423456789, '博弈论', 323456789, 3);

insert into SC(Sno, Cno, Grade) VALUES(161630301, 123456789, 89);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 223456789, 90);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 323456789, 80);
insert into SC(Sno, Cno, Grade) VALUES(161630301, 423456789, 77);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 123456789, 91);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 223456789, 88);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 323456789, 83);
insert into SC(Sno, Cno, Grade) VALUES(161630302, 423456789, 93);
insert into SC(Sno, Cno, Grade) VALUES(161630303, 123456789, 90);
insert into SC(Sno, Cno, Grade) VALUES(161630303, 223456789, 89);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 123456789, 87);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 323456789, 86);
insert into SC(Sno, Cno, Grade) VALUES(161630304, 423456789, 85);

三、实验二

1. 实验内容：数据查询
2. 查询选修223456789号课程的学生学号与姓名

select S.Sno, Sname from S, SC where S.Sno=SC.Sno and SC.Cno=223456789;

3. 查询选修课程名为概率论的学生学号与姓名

select S.Sno, Sname from S, SC, C where S.Sno=SC.Sno and C.Cno=SC.Cno and C.Cname='概率论';

4. 查询不选223456789号课程的学生学号与姓名

select S.Sno, Sname from S where Sno not in (select Sno from SC where Cno in (select Cno from C where Cno=223456789));

5. 查询学习全部课程学生姓名

select S.Sno, Sname from S where Sno in(select Sno from SC group by Sno having count(Cno)=4);

6. 查询所有学生除了123456789选修号课程外所有成绩均及格的学生的学号和平均成绩，其结果按平均成绩的降序排列

create view SSC(Sno, Cno, Grade) as select S.Sno, Cno, Grade from S, SC where S.Sno=SC.Sno and Cno!=123456789;
select Sno, avg(Grade) as aver from SSC where (select min(Grade) from SSC SSSC where SSC.Sno=SSSC.Sno)>=60 group by Sno order by aver desc;

7. 查询选修数理统计成绩第2名的学生姓名

select Sname from S where Sno in (select Sno from SC where Cno in (select Cno from C where Cname='数理统计') and Grade in (select max(Grade) from SC where Grade not in (select max(Grade) from SC where Cno in (select Cno from C where Cname='数理统计')) and Cno in (select Cno from C where Cname='数理统计')));

8. 查询所有3个学分课程中有3门以上（含3门）课程获80分以上（含80分）的学生的姓名

create view select1(Sno, Cno) as select Sno, C.Cno from C, SC where C.Cno=SC.Cno and Ccredit=3 and Grade>=80;
select Sname from S where Sno in (select Sno from select1 group by Sno having count(Cno)>=3); 

9. 查询选课门数唯一的学生的学号

create view select2(Sno, Ccount) as select Sno, count(*) from SC group by Sno;
select Sno from SC group by Sno having count(*)<>all (select Ccount from select2 where select2.Sno!=SC.Sno);

四、实验三

1. 实验内容：数据修改、删除
2. 把223456789号课程的非空成绩提高10％

update SC set Grade=Grade*1.1 where Grade is not null and Cno=223456789; 

3. 在SC表中删除课程名为计算机基础的成绩的元组

delete from SC where Cno in (select Cno from C where Cname='计算机基础');

4. 在S和SC表中删除学号为161630303的所有数据

下面的SYS_C0010950是我的约束名，你们的可能不太一样，直接运行delete语句会报错，错误中提示你约束名

alter table SC disable constraint SYS_C0010950 cascade;
delete from S where Sno=161630303;
delete from SC where Sno=161630303;
alter table SC enable constraint SYS_C0010950;

五、实验四

1. 实验内容：视图的操作
2. 建立男学生的视图，属性包括学号、姓名、选修课程名和成绩

create view male_stu(Sno, Sname, Cname, Grade) as select S.Sno, Sname, Cname, SC.Grade from S,C,SC where S.Sno=SC.Sno and C.Cno=SC.Cno and S.Sgender='男'；

3. 在男学生视图中查询平均成绩大于85分的学生学号与姓名

select Sno, Sname from male_stu group by Sno, Sname having avg(Grade)>85;

六、实验五

1. 实验内容：库函数，授权控制
2. 计算每个学生有成绩的课程门数、平均成绩

select Sno, count(*) as course_num, avg(Grade) as grade_avg from SC where Grade is not null group by Sno;

3. 使用GRANT语句，把对基本表S、SC、C的使用权限授给其它用户

grant all on SC to public;
grant all on S to public;
grant all on C to public;

4. 实验完成后，撤消建立的基本表和视图

drop table S CASCADE CONSTRAINTS;
drop table SC CASCADE CONSTRAINTS;
drop table C CASCADE CONSTRAINTS;
drop view select1 CASCADE CONSTRAINTS;
drop view select2 CASCADE CONSTRAINTS;
drop view male_stu CASCADE CONSTRAINTS;
drop view SSC CASCADE CONSTRAINTS;