oracle三表关联例题螺母,福建工程学院Oracle实验二下.doc

福建工程学院信息科学与工程学院

实验报告

2013 – 2014 学年第 一 学期 任课老师： 胡文瑜

课程名称

数据库开发技术

班级

信管1102

座号

20

姓名

郭明光

实验题目

实验二、Oracle函数、组函数和数据分组统计

实验时间

2013.10.10

实验目的、内容

一、 实验目的

1． 掌握分组查询，连接查询和集合运算的使用方法。

2． 掌握子查询及内嵌视图的使用方法。

3． 掌握多列子查询和相关子查询的使用。

4． 掌握TOP-N分析方法。

掌握INSERT ALL语句和MERGE语句的使用方法。

实验设计过程

二、 实验内容和要求

第一部分：使用SQL语句完成以下查询要求。(实验用的数据表在本文档的最后面)

1. 查询使用红色零件的工程名称。(考核多表(三表)连接操作) (分别使用相关的子查询、带in谓词的子查询、带exists谓词的子查询和连接查询实现。注意体会不同实现方式的区别。)

/* 连接查询 */

select distinct jname

from p,j,spj

where spj.jno = j.jno and spj.pno=p.pno and p.color='红'

/*三层带IN谓词的嵌套查询 */

select jname

from j

where jno in (select jno

from spj

where pno in(

select pno

from p

where color='红'))

/* 带exists谓词的子查询 */

select jname

from j

where exists

(select *

from p

where exists

(select *

from spj

where j.jno=jno and p.pno=pno and p.color='红'));

/* 相关子查询 */

select jname

from j

where jno in (select jno

from spj,p

where p.pno=spj.pno and color='红'

)

2. 查询每一种零件被供应的次数，要求：

1)结果显示零件号、零件名称和被供应的次数。

2)零件表中的所有零件都要统计。

显示效果形如下：

PNO PNAME COUNT_P

--- ---------- --------------

P1 螺母 4

P2 螺栓 2

P3 螺丝刀 5

P4 螺丝刀 0

P5 凸轮 3

P6 齿轮 3

P7 把手 0

已选择7行。

select p.pno,pname,count(spj.pno) COUNT_P

from p,j,spj

where j.jno = spj.jno and p.pno = spj.pno(+)

group by p.pno,pname

order by p.pno

问题1？用GROUP BY P.PNO还是SPJ.PNO?

Answer: 用GROUP BY P.PNO

若用group by spj.pno则结果如下

问题2？GROUP BY P.PNO和GROUP BY P.PNO, PNAME结果是否一样？

Answer: GROUP BY P.PNO无法运行

问题3？用COUNT(SPJ.PNO)还是COUNT(P.PNO)

Answer: 用COUNT(SPJ.PNO)

若用COUNT(P.PNO)则结果如下

明显错误，P4和P7没被使用

问题4？如果查询结果不要求显示零件号，只要求显示零件名称和数量，以下这个SQL语句对吗？

SELECTPNAME, COUNT(SPJ.PNO)

FROM P, SPJ

WHEREP.PNO = SPJ.PNO(+)

GROUP BY PNAME;

Answer: 正确，但因为pname为螺丝刀的有分为蓝色和红色两种，所以P4没显现出来

结果如下

3. 查询student表中各系学生数占全校学生人数的百分比

显示效果形如下：

SDEPT %Student

---------- ----------

CS 33.3

FL 22.2

IS 22.2

MA 22.2

已选择4行。

INSERT INTO STUDENT VALUES(95001,'李明勇','男',20,'CS');

INSERT INTO STUDENT VALUES(95002,'刘晨','女',19,'IS');

INSERT INTO STUDENT VALUES(95003,'王名','女',18,'MA');

INSERT INTO STUDENT VALUES(95004,'张立','男',19,'CS');

INSERT INTO STUDENT VALUES(95005,'张军','男',21,'MA');

INSERT INTO STUDENT VALUES(95006,'王张凤','女',19,'FL');

INSERT INTO STUDENT VALUES(95007,'王敬','女',18,'IS');

INSERT INTO STUDENT VALUES(95008,'张名惠','男',19,'FL');

COMMIT;

由表可知，一共有8人，而每个系有2人

SELECT a.sdept,

(A.student_SUM / B.student_TOTAL)*100 "%student"

FROM (SELECT sdept, COUNT(sno) student_SUM

FROM student

GROUP BY sdept) A,

(SELECT COUNT(sno) student_TOTAL

FROM student) B;

4. 查询工资高于本部门平均工资的员工信息。

SELECT a.*, b.平均工资

FROM emp a, (SELECT deptno, avg(sal) 平均工资

FROM emp

GROUP BY deptno) b

WHERE a.deptno = b.deptno

AND a.sal > b.平均工资;

5. 显示与30部门任何雇员薪水及岗位相匹配的雇员的姓名、部门编号、薪水及佣金。

SELECT ename,deptno,sal,comm FROM emp

WHERE sal IN(SELECT sal FROM emp

WHERE deptno = 30)

AND job IN(SELECT job FROM emp

WHERE deptno=30)

and deptno<>30

6. 查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。

显示效果形如下：

SNAME SAGE

-------------------- ----------

张军 21

select sname,sage

from student

where sage>any

(select sage

from student

where sdept='CS')

and sdept<>'CS'

select sname,sage

from student

where sage>

(select min(sage)

from student

where sdept='CS')

and sdept<>'CS'

7. *查询至少选修了学生95002选修的全部课程的学生学号(不包括95002学生本身)。

显示效果形如下：

SNO

----------

95001

select distinct sno

from sc scx

where not exists

(select *

from sc scy

where scy.sno='95002' and

not exists

(select *

from sc scz

where scz.sno=scx.sno and

scz.cno=scy.cno

and scx.sno<>'95002'))

8. 返回雇员号最小的10个员工信息。

SELECT rownum,a.*

FROM (SELECT *

FROM EMP ORDER BY empno) a

WHERE rownum<=10

9. *查询工资第3低的员工的工号、姓名和薪水(假设SAL子段作了惟一限制。)(提示：使用from子查询语句、内嵌视图和ROWNUM伪列)

显示效果形如下：

EMPNO ENAME SAL

---------- ---------- ----------

7876 ADAMS 1100

已选择 1 行。

select * from (SELECT a.*

FROM (SELECT EMPNO,ENAME,SAL

FROM EMP ORDER BY SAL ) a

WHERE rownum<=3

order by sal desc)

where rownum=1

10. *使用MERGE语句从EMP表向EMP30表更新最新改变过的雇员的薪水，并用INSERT语句插入不在EMP30表中的雇员。

MERGE INTO emp30 m

USING (SELECT empno, ename

FROM EMP) e

ON (e.empno=m.empno)

WHEN MATCHED THEN

UPDATE SET m.ename=e.ename

WHEN NOT MATCHED THEN

INSERT(m.empno,m.ename)

VALUES (e.empno,e.ename);

第二部分：

*自己设计实例完成在CREATE, UPDATA, INSERT, DELETE语句中使用子查询的实验；

create table emp30 as select * from emp

附加题：

*显示部门的雇员比部门20的雇员更多的部门的名称。

显示效果形如下：

DNAME

--------------

SALES

已选择 1 行。

select dname

from dept,emp

where dept.deptno=emp.deptno

group by dname

having count(*)>

(select count(empno)

from emp

where deptno='20'

group by deptno

)

调试过程记录

实验结果记录以及与预期结果比较以及分析

总结以及心得体会

指导老师评阅意见

指导老师： 年 月 日

填写内容时，可把表格扩大。实验的源程序代码(要有注释)附在表后。

展开阅读全文