oracle中对表进行分组,Oracle第五章多表链接、第六章分组函数习题整理

--第五章 1.

1.写一个查询，显示所有员工姓名，部门编号，部门名称。

select

emp.ename,dept.deptno,dept.dname

from emp join dept on

dept.deptno=emp.deptno;

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select

emp.ename,dept.loc,emp.comm

from emp join dept on

dept.deptno=emp.deptno

where

emp.comm <> 0;

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select

emp.ename,dept.loc

from emp join dept on

dept.deptno=emp.deptno

where emp.ename like

'%A%';

练习2

1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

--工资登记表：salgrade

select

emp.empno,emp.ename,emp.sal,salgrade.grade,dept.loc

from emp join dept on

dept.deptno=emp.deptno,salgrade

order by salgrade.grade

asc;

练习3

1.查询所有工作在NEW

YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select

e.ename,e.deptno,m.ename

from emp

e

left join emp

m

on e.mgr=m.empno;

2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

select

e.ename,e.deptno,m.ename

from emp

e

left join emp

m

on e.mgr=m.empno

where e.job <>

'MANAGER'

order by e.empno;

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

select

e.empno,e.ename,d.deptno

from emp

e

full outer join dept

d

on

e.deptno=d.deptno;

练习4

•

1.创建个员工表和部门表的交叉连接

select

emp.empno,emp.ename,dept.dname from

emp CROSS JOIN

dept;

•

2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

SELECT

empno,ename,sal,deptno,loc

FROM emp

NATURAL JOIN

dept

where hiredate >

'1-5月-80';

•

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

SELECT

e.ename,d.dname,d.loc

FROM emp e

JOIN dept d USING

(deptno)

where

d.loc='CHICAGO';

•

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

SELECT

e.ename "员工姓名",dname "部门名称",e.sal "工资",s.grade

"工资级别"

FROM emp e, dept d,salgrade

s

WHERE

e.deptno=d.deptno

AND e.sal BETWEEN s.losal AND

s.hisal

--select * from

salgrade;

•

5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

SELECT e.ename,m.ename

"经理名字"

FROM emp e

left outer join emp

m

on e.mgr=m.empno;

•

6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

SELECT e.ename,m.ename

"经理名字"

FROM emp m

right outer join emp

e

on m.mgr=e.empno;

课后作业

•

1.显示员工SMITH的姓名，部门名称，直接上级名称

SELECT

e.ename,d.dname,m.ename

FROM emp e

join  dept d

on e.deptno=d.deptno

join emp m on

e.mgr=m.empno;

•

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

SELECT

e.ename "员工姓名",dname "部门名称",e.sal "工资",s.grade

"工资级别"

FROM emp e, dept d,salgrade

s

WHERE

e.deptno=d.deptno

AND e.sal BETWEEN s.losal AND

s.hisal

AND s.grade>4

•

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

方法一：

SELECT e.ename "员工姓名",m.ename

"经理姓名"

FROM emp e,emp m

WHERE

e.mgr=m.empno

AND m.ename

IN('KING','FORD');

方法二：

SELECT e.ename "员工姓名",m.ename

"经理姓名"

FROM emp e,emp m

WHERE

e.mgr=m.empno

AND m.ename ='KING' or

e.mgr=m.empno and m.ename ='FORD';

•

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

SELECT e.ename "员工姓名",

e.hiredate "参加工作时间" ,m.ename "经理姓名", m.hiredate

"经理参加工作时间"

FROM emp e,emp m

WHERE

e.mgr=m.empno

AND e.hiredate<

m.hiredate;

--第六章

练习一

1.查询部门20的员工，每个月的工资总和及平均工资。

select sum(sal) "工资总和",avg(sal)

"平均工资"

from

emp

where deptno=20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

select max(sal) "最高工资",min(sal)

"最低工资"

from emp e join dept d on

e.DEPTNO=d.DEPTNO

where d.loc =

'CHICAGO'

3.查询员工表中一共有几种岗位类型。

select distinct(job) from

emp;

--第六章练习2

1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select

d.deptno,d.dname,count(e.ename),

max(e.sal),min(e.sal),sum(sal),avg(sal)

from dept d join emp e on

e.deptno=d.deptno

group by

d.deptno,d.dname

--第六章练习2

2.查询每个部门，每个岗位的部门编号

部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select

d.deptno,d.dname,e.job,count(e.empno),

max(e.sal),min(e.sal),sum(e.sal),avg(e.sal)

from dept d join emp e on

e.deptno=d.deptno

group by

d.deptno,d.dname,e.job

3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

select

count(d.ename) "管理的人数", e.empno "经理编号",e.ename

"经理姓名"

from emp e ,emp

d

where e.empno

=d.mgr

or d.mgr is null

group by

e.empno,e.ename;

1.查询部门人数大于2的部门编号，部门名称，部门人数

select

d.deptno,d.dname,count(e.ename)

from dept d

join emp e on

d.deptno=e.deptno

group by

d.dname,d.deptno

having(count(e.ename)>2);

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

select

d.deptno,d.dname,count(e.ename),avg(e.sal)

from dept d

join emp e on

d.deptno=e.deptno

group by

d.dname,d.deptno

having(count(e.ename)>2)

order by count(e.ename)

;

--第六章课后习题

----第六章课后习题1.

•

1.查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname

,avg(e.sal)

from dept d

join emp e on

d.deptno=e.deptno

group by d.dname

having(avg(e.sal)>2500);

----第六章课后习题2

• 2.查询员 岗位中不是以

工岗位中不是以“SA”开头并且平均 资在 工 2500元以上的岗位及平均工资，并按平均工资降序排序。

select

e.job,avg(e.sal)

from emp e

where e.job not like

'SA%'

group by e.job

having(avg(e.sal)>2500)

order by avg(e.sal)

desc

----第六章课后习题3.

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

select

d.dname,min(e.sal),round(max(e.sal),0)

from dept d join emp e on

d.deptno = e.deptno

having((count(e.ename)>2))

group by d.dname

--第六章课后习题 4

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

select  e.job,

sum(e.sal)

from emp e

having(e.job

not like 'SALEMAN')

group by e.job

--第六章课后习题 5?????

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于

3000的，按最低工资由高到低排序。

select

e.empno,e.ename,min(d.sal)

from emp e left join emp d on

d.mgr = e.empno

group by

e.empno,e.ename

having(min(d.sal)>=3000)

order by min(d.sal)

desc

6.写一个查询，显示每个部门最高工资和最低工资的差额。

select

d.deptno,d.dname,max(e.SAL)-min(e.sal)

from dept d

join emp e on d.deptno

=e.deptno

group by

d.deptno,d.dname;

---------------------------------------------0623