Oracle数据库实验_NUAA

实验一：SQL定义功能、数据插入          

1．建立教学数据库的三个基本表：

S(Sno,Sname,Ssex,Sage,Sdept)   学生（学号，姓名，性别，年龄，系）

SC(Sno,Cno,Grade)              选课（学号，课程号，成绩）

C(Cno,Cname,Cpno,Ccredit)      课程（课程号，课程名，先行课，学分）

__________________________________________________________________

/2．DROP TABLE、ALTER TABLE、CREATE INDEX、DROP INDEX 及INSERT语句输入数据。

_____________________________________________________________________________
实验二：数据查询 

1. 查询选修1号课程的学生学号与姓名。

 SELECT S.SNO,S.SNAME

FROM S,SC  

WHERE S.SNO=SC.SNO AND SC.CNO='1';

2. 查询选修课程名为数据库原理的学生学号与姓名。

SELECT S.SNO,S.SNAME

FROM S,SC,C

WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND C.CNAME = ‘数据库原理’

3. 查询不选1号课程的学生学号与姓名。

SELECT SNO,SNAME FROM S WHERE NOT EXISTS(SELECT* FROM SC WHERE S.SNO = SC.SNO AND SC.CNO = ‘1’);

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

SELECT SNAME FROM S WHERE NOT EXISTS(SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.SNO = S.SNO AND SC.CNO = C.CNO));

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

SELECT SNO, AVG(GRADE) FROM SC WHERE CNO != 1 GROUP BY SNO HAVING AVG(GRADE) > 60;

6. 查询选修数据库原理成绩第2名的学生姓名。

SELECT SNAME, GRADE FROM S,SC WHERE S.SNO = SC.SNO AND CNO = 3 ORDER BY GRADE DESC;

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

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

8. 查询选课门数唯一（即与其他同学选修课程数目都不同）的学生的学号。

SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(*) = 1;

实验三：数据修改、删除

1. 把1号课程的非空成绩提高10％（建立触发器，当所更新成绩超过100分时设置成100）。

SELECT * FROM SC WHERE CNO = 1;

UPDATE SC SET GRADE = 1.1 * GRADE WHERE CNO = 1;

触发器：

2. 在SC表中删除课程名为数据库原理的成绩的元组。

DELETE FROM SC WHERE CNO = (SELECT CNO FROM C WHERE CNAME = ‘数据库原理’);

3. 在S和SC表中删除学号为201215121的所有数据（分是否使用cascade两种方式实现）。

 

实验四：视图的操作

1. 建立张姓学生的视图，属性包括学号、姓名、选修课程名和成绩。

CREATE VIEW ZHANG AS SELECT S.SNO,S.SNAME,C.CNAME,SC.GRADE FROM S,C,SC WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND S.SNAME LIKE 'zhang%';

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

建立视图

CREATE VIEW MAN_S AS SELECT S.SNO,S.SNAME,C.CNAME,SC.GRADE FROM S,SC,C WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO AND S.SGENDER = ‘F’;

查询

SELECT SNO,SNAME FROM MAN_S GROUP BY SNO,SNAME HAVING AVG(GRADE) >= 80;

3. 创建计算机系CS的学生视图，并对该视图进行增删改查操作（要求只能操作CS数据）。

实验五：库函数、授权控制

1. 计算每个学生有成绩的课程门数、平均成绩并报表输出(要求创建过程实现)。

EXEC AVG_C;

2. 给定学生学号，查询其选修成绩最高的那门课的课程名（要求创建函数实现）。

SELECT MAX_GRADE(‘1011’)FROM DUAL;

3. 将选修表中成绩为NULL的选修记录删除，成绩为55-60之间的改成60分（要求使用游标实现）。

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

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

DROP VIEW ZHANG;

DROP VIEW MAN_S;

DROP TABLE SC;

DROP TABLE S;

DROP TABLE C;