oracle表连接查询逗号隔开_Oracle表连接与子查询示例

--把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的， 要求分组后的平均薪水

>1500， 按照部门编号倒序排列

--求每个人的名字和他的经理人的名字，用外连接把KING也取出来

--哪些人的薪水在部门的平均薪水之上

--求部门中哪些人的薪水最高

--求部门平均薪水的等级

--求部门平均的薪水等级

--雇员中有哪些人是经理人

--不准用组函数,求薪水的最高值(面试题)

--求平均薪水最高的部门的部门编号

--求平均薪水最高的部门的部门名称

--求平均薪水的等级最低的部门的部门名称

--求部门经理人中平均薪水最低的部门名称 (思考题)

--求比普通员工的最高薪水还要高的经理人名称

--求薪水最高的前5名雇员

--求薪水最高的第6到第10名雇员(重点掌握)

--练习: 求最后入职的5名员工

--求每个部门中薪水最高的前两名雇员

--面试题: 比较效率

--求分段显示薪水的个数

如：

scale  total

<800  0

800-1000 2

1001-2000 3

2001-5000 6

>5000  8

或者显示成为

800-1000 1001-2000 2000-5000

2  3  6

或者显示成为

DEPTNO   800-2000  2001-5000

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

30          5          1

20          2          3

10          1          2

--

========================第一份答案=======================

--把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是'A'的，

要求分组后的平均薪水>1500， 按照部门编号倒序排列

selecte1.ename, e2.ename

fromemp e1leftjoinemp e2on(e1.mgr = e2.empno)

--哪些人的薪水在部门的平均薪水之上

selectename, sal

fromempjoin

(selectdeptno,max(sal) max_sal

fromemp

groupbydeptno) t

on(emp.deptno = t.deptnoandemp.sal = t.max_sal)

--求部门平均薪水的等级

selectdeptno, avg_sal, grade

fromsalgrade sjoin

(selectdeptno,avg(sal) avg_sal

fromemp

groupbydeptno) t

on(t.avg_salbetweens.losalands.hisal) sql 代码

--求部门平均的薪水等级

selectenamefromempwhereempnoin(selectdistinctmgrfromemp)

--不准用组函数,求薪水的最高值(面试题)

sql 代码

selectenamefromempwhereempnonotin

(selectdistincte1.empnofromemp e1joinemp e2on(e1.sal 

排序，取第一个

--求平均薪水最高的部门的部门编号

selectdeptnofrom

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t

whereavg_sal =

(selectmax(avg_sal)from

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t)

selectmax(avg(sal))fromempgroupbydeptno －－组函数嵌套

--求平均薪水最高的部门的部门名称

selectdnamefromdeptwheredeptno =

(selectdeptnofrom

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t

whereavg_sal =

(selectmax(avg_sal)from

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t)

)

--求平均薪水的等级最低的部门的部门名称

selectdept.deptno,dnamefrom

(

selectdeptno, avg_sal, gradefromsalgrade sjoin

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t

on(t.avg_salbetweens.losalands.hisal)

) t1joindepton

(t1.deptno = dept.deptno)

wheregrade =

(selectmin(grade)from

(

selectdeptno, avg_sal, gradefromsalgrade sjoin

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t

on(t.avg_salbetweens.losalands.hisal)

)

)

reateviewv$_dept_infoas

selectdeptno, avg_sal, gradefromsalgrade sjoin

(selectdeptno,avg(sal) avg_salfromempgroupbydeptno) t

on(t.avg_salbetweens.losalands.hisal)

elect deptnofromv$_dept_infowheregrade = (selectmin(grade)fromv$_dept_info);

--求部门经理人中平均薪水最低的部门名称 (思考题)

//....

--求比普通员工的最高薪水还要高的经理人名称

sql 代码

selectempno, ename, salfromempwheresal >

(

selectmax(sal)from

(selectempno,ename, sal

fromempwhereempnonotin(selectdistinctnvl(mgr, -1)fromemp ))

)andempnoin(selectdistinctmgrfromemp)

selectdistinctmgrfromempwheremgrisnotnull

--求薪水最高的前5名雇员

--求薪水最高的第6到第10名雇员(重点掌握)

selectename, salfrom

(

selectename, sal, rownum rfrom

(

selectename, salfromemporderbysaldesc

)

whererownum <=10

)

wherer > 5andr <= 10

--求每个部门中薪水最高的前两名雇员

selectdeptno, ename, salfromemporderbydeptno, saldesc

selectdeptno, ename, sal , rownum rfrom

(selectdeptno, ename, salfromemporderbydeptno, saldesc);

createviewvasselectdeptno, ename, sal , rownum rfrom

(selectdeptno, ename, salfromemporderbydeptno, saldesc);

selectdeptno,min(r) min_rfromvgroupbydeptno;

selectv.deptno, ename, sal , rfromvjoin

(

selectdeptno,min(r) min_rfromvgroupbydeptno

) t

on(v.deptno = t.deptnoandv.r >= t.min_randv.r <= t.min_r + 1)

orderbyv.deptno, saldesc;

--练习: 求最后入职的5名员工

selectename, hiredatefrom

(

selectename ,hiredatefromemporderbyhiredatedesc

)

whererownum <= 5

--面试题: 比较效率

select*fromempwheredeptno = 10andenamelike'%A%';--这种是最高的

select*fromempwhereenamelike'%A%'anddeptno = 10;

--求分段显示薪水的个数

如：

scale      total

<800  0

801-1000 2

1001-2000 3

2001-5000 6

>5000  8

select'<800'asscale ,count(*)astotalfromempwheresal 

union

select'800-1000'asscale ,count(*)astotalfromempwheresalbetween800and1000

union

select'2000-5000'asscale ,count(*)astotalfromempwheresalbetween2000and5000;

...

或者显示成为

800-1000 1001-2000 2000-5000

2  3  6

sql 代码

select*from

(selectcount(*)as"800-1000"fromempwheresalbetween800and1000 )

,

(selectcount(*)as"1001-2000"fromempwheresalbetween1001and2000 );

...

或者显示成为

DEPTNO   800-2000  2001-5000

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

30          5          1

20          2          3

10          1          2

sql 代码

selectt1.deptno,"800-2000","2001-5000"from

(

selectdeptno ,count(*)as"800-2000"fromempwheresalbetween800and2000

groupbydeptno

) t1

join

(

selectdeptno ,count(*)as"2001-5000"fromempwheresalbetween2001and5000

groupbydeptno

) t2

on

t1.deptno = t2.deptno

selectename, sal

from

(selectename, salfromemporderbysaldesc)

whererownum <=5;

sql 代码

selectdeptno,avg(grade)from

(selectdeptno, sal , grade

fromemp ejoinsalgrade son

(e.salbetweens.losalands.hisal)

) t

groupbydeptno

--雇员中有哪些人是经理人

sql 代码

selectename, sal

fromempjoin

(selectdeptno,avg(sal) avg_sal

fromemp

groupbydeptno) t

on(emp.deptno = t.deptnoandemp.sal > t.avg_sal)

--求部门中哪些人的薪水最高

sql 代码

selectmax(sal), deptnofromemp

whereenamenotlike'_A%'

groupbydeptno

havingavg(sal) > 1500

orderbydeptnodesc;

--求每个人的名字和他的经理人的名字，用外连接把KING也取出来

sql 代码