ORACLE完整笔记及JAVA习题 下载本文

FROM emp e ,emp m WHERE e.mgr = m.empno

AND e.hiredate < m.hiredate

第六章分组函数

分组函数:是对表中的一组记录进行操作,每一组能返回一个结果,首先就是要对数据进行分组。组函数只能嵌套两层

MIN:可以用于任何数据类型 MAX:可以用于任何数据类型 AVG:只能用于数值型 SUM:只能用于数值型

COUNT:count *可以计入空值,其他分组函数均是去掉空值计算的! Groupby 后面只能由两种语句:待分组的列和组函数。 相应的分组过滤字句必须是having里面的, 基础知识没有多少,直接做习题 练习1:

1.查询部门人数大于2的部门编号,部门名称,部门人数 SELECT e.deptno,d.dname,COUNT(e.empno) FROM emp e ,dept d

WHERE e.deptno = d.deptno GROUPBY e.deptno,d.dname HAVINGCOUNT(e.empno)>2 2.查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,部门平均工资,并按照部门人数升序排列

SELECT e.deptno,d.dname,COUNT(empno),AVG(sal) FROM emp e,dept d

WHERE e.deptno = d.deptno GROUPBY e.deptno,d.dname HAVINGAVG(sal)>2000 ORDERBYCOUNT(empno)

3.查询部门20 的员工,每个月的工资和及平局工资

SELECTSUM(sal)总工资,AVG(sal)平均工资FROM emp WHERE deptno ='20' 4.查询工作在CHICAGO的员工人数,最高工资及最低工资 SELECTCOUNT(e.empno),MIN(e.sal),MAX(e.sal) FROM emp e,dept d

WHERE e.deptno = d.deptno AND d.loc ='CHICAGO'

5.查询一共有集中岗位类型(去除重复记录并计数)

SELECTCOUNT(DISTINCT job)FROM emp

6.查询每个经理所管理的人数,经理编号,经理姓名,要求包括没有经理的人员信息 SELECT m.empno,m.ename,COUNT(e.empno) FROM emp e,emp m

WHERE e.mgr = m.empno(+) GROUPBY m.empno,m.ename

--1.部门平均工资在2500以上的部门名称及平均工资

SELECTAVG(e.sal),d.dname FROM emp e ,dept d WHERE e.deptno = d.deptno GROUPBY d.deptno,d.dname HAVINGAVG(sal)>2500

--2.查询岗位不以‘SA’开头,并且平均工资在2500以上的岗位及平均工资,按照工资降序

SELECT job,AVG(sal)FROM emp WHERE job <>'SA%' GROUPBY job ORDERBYAVG(sal)DESC

--3.查询部门人数在两个人以上的部门名称,最低工资,最高工资,并对工资进行四舍五入。

SELECT d.dname,round(MIN(e.sal)),round(max(e.sal)) FROM emp e,dept d WHERE e.deptno = d.deptno GROUPBY d.deptno,d.dname HAVINGCOUNT(empno)>2

--4.查询岗位不是salsesman的,工资和大于2500的每种岗位及其工资和

SELECT job,SUM(sal)FROM emp WHERE job <>'SALESMAN' GROUPBY job HAVINGSUM(sal)>2500

--5.显示经理号码,这个经理管理员工的最低工资,没有经理的king也要显示,不包括最底工资小于3000的,按最低工资由低到高排序

SELECT m.empno,min(e.sal) FROM emp e,emp m

WHERE e.mgr = m.empno(+) GROUPBY m.empno

HAVINGMIN(e.sal)>=3000 ORDERBYmin(e.sal)ASC

--6.查询每个部门的最低工资与最高工资的差额

SELECTMAX(sal)-MIN(sal)FROM emp GROUPBY deptno

第七章子查询

子查询先于主查询执行

主查询与子查询之间的运算符可以使用:= ,>,<,IN<=,>=,<>(单行子查询), ANY,ALL(多行子查询),单行子查询:子查询的返回结果为一行一列,

多行子查询:返回结果是多行一列(包括0行,也就是0行也算多行)

多列子查询:返回的查询结果为多行多列。

子查询可以用在where,having,from 子句中。 单行子查询例题:

--1.工资最高的员工的员工姓名,工资

SELECT ename ,sal FROM emp WHERE sal =(SELECTMAX(sal)FROM emp )

--2.查询和7369相同工作的,并且工资大于7369的员工的姓名,工作,工资

SELECT ename, job,sal

FROM emp WHERE job =(SELECT job FROM emp WHERE empno =7369) AND sal >(SELECT sal FROM emp WHERE empno =7369)

--3.查询部门最低工资比20号部门最低工资高的部门编号和最低工资

SELECT deptno,MIN(sal)FROM emp GROUPBY deptno

HAVINGMIN(sal)>(SELECTMIN(sal)FROM emp WHERE deptno =20) --4.查询什么部门的人数高于各个部门的平均人数 SELECT deptno FROM emp GROUPBY deptno

HAVINGCOUNT(empno)>(SELECTavg(COUNT(empno))FROM emp GROUPBY deptno) --5.查询入职日期最早的那个员工姓名

SELECT ename FROM emp WHERE hiredate =(SELECTMIN(hiredate)FROM emp)

--6.查询工资比smith高,并且工作地点在chicago的员工姓名,工资,部门名称

SELECT e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno AND d.loc ='CHICAGO'AND e.sal >

(SELECT sal FROM emp WHERE ename ='SMITH')

--7.查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期

SELECT ename,min(hiredate)FROM emp WHERE hiredate <(SELECTmin(hiredate)FROM emp WHERE deptno =20)

--8.查询部门人数大于各部门平均人数的部门名称,部门编号,部门人数 SELECT d.dname,e.deptno,COUNT(e.empno)FROM emp e,dept d WHERE e.deptno(+)= d.deptno GROUPBY d.dname,e.deptno HAVINGCOUNT(empno)>

(SELECTAVG(COUNT(empno))FROM emp GROUPBY deptno)

一下练习中演示了多行子查询中多行运算符(IN 、ANY、 ALL)的应用。 --1.查询是经理的员工姓名,工资

SELECT ename,sal FROM emp WHERE empno IN(SELECT mgr FROM emp)

--2.查询部门编号不为10,且工资比10 部门中任意一个员工工资高的员工编号,姓名,职位,工资

SELECT empno,ename,job,sal FROM emp

WHERE deptno <>10AND sal >ANY(SELECT sal FROM emp WHERE deptno =10)

--3.查询部门编号不为10,且工资比10 部门中任意一个员工工资低的员工编号,姓名,职位,工资

SELECT empno,ename,job,sal FROM emp

WHERE deptno <>10AND sal

--4.查询部门编号不为10,且工资比10 部门中所有员工工资低的员工编号,姓名,职位,工资

SELECT empno,ename,job,sal FROM emp

WHERE deptno <>10AND sal

--5.查询部门编号不为10,且工资比10 部门中所有员工工资高的员工编号,姓名,职位,工资

SELECT empno,ename,job,sal FROM emp

WHERE deptno <>10AND sal >ALL(SELECT sal FROM emp WHERE deptno =10)

多列子查询:通过一下的例子可以看出,多列子查询其实就是子查询中出现了多个列! --1.查询职位和部门与smith完全相同的人员姓名,

SELECT ename FROM emp WHERE(job,deptno)IN(SELECT job,deptno FROM emp WHERE ename ='SMITH')

AND ename <>'SMITH'

--2.查询和1981年入职的任意一个员工的部门和职位完全相同,员工姓名,部门,职位,入职日期,不包括1981年入职的员工

SELECT ename,deptno,job,hiredate FROM emp WHERE(deptno,job)

IN(SELECT deptno,job FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981) ANDSUBSTR(hiredate,-2)<>81

或substr(hiredate,-2)=81或to_char(hiredate,'YYYY')='1981'

--3.查询和1981年入职的任意一个员工的部门或职位相同,员工姓名,部门,职位,入职日期,不包括1981年入职的员工

SELECT ename,deptno,job,hiredate FROM emp WHERE( deptno

IN(SELECT deptno FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981)

OR job IN(SELECT job FROM emp WHEREEXTRACT(YEARFROM hiredate)=1981)) ANDEXTRACT(YEARFROM hiredate)<>1981

注意:最后一题不是多列子查询,说白了,多列子查询只能解决“列和”的问题,不能解决“列或”的问题。

子查询中的空值问题: 查询不是经理的员工姓名

SELECT ename FROM emp WHERE empno NOTIN(SELECT mgr FROM emp ) 翻译为:

SELECT ename FROM emp WHERE

(empno <>7839AND empno <>7782AND empno <>NULLAND empno <>7698AND empno <>7902AND empno <>7566)

例子中可以看出,子查询中有了一个空值,整个查询结果就是faulse,一个也查不出来 所以以上查询应该这样写:

SELECT ename FROM emp WHERE empno NOTIN(SELECT mgr FROM emp where mgr is not null)

From 子句中应用子查询:这一类查询中很明显“自己部门”既没有信息,又是模糊的, 自己部门+平均工资,可以构成一个表。

--1.查询比自己部门平均工资高的员工姓名,工资,部门编号,部门平均工资。 SELECT e.ename,e.sal,e.deptno,tavg.av

FROM emp e,(SELECT deptno,AVG(sal) av FROM emp GROUPBY deptno) tavg WHERE e.deptno = tavg.deptno AND e.sal > tavg.av

关于伪列ROWNUM:

1.ROWNUM是一个伪列,它与查询相对应,如果给每个查询都建立一个ROWNUM ,这些伪列之间互不影响。

2.select * from emp where rownum > 2查不出任何数据,因为rownum跟就记录生成而生成,roenum只能用<,<=用,不能用>!!!!

3.rownum和orderby连用的时候,rownum其实是排完序之后的!可见order by最后执行啊!!

--1.查询入职日期最早的前5名员工姓名,入职日期。

SELECTROWNUM,ename ,hiredate FROM(SELECT ename,hiredate FROM emp ORDERBY hiredate ASC)WHEREROWNUM<=5