Oracle中查询工资相同,oracle 作业题总结

oracle 作业题总结

10级学员 赵成举课堂总结

今天课上我们讲了16个在oracle中查询的习题(见附件)，我们都知道，在学习数据库语言时，HQL(数据库查询语言)最复杂。因为只有在查询数据库的时候才会出现有N多种条件、和样式的情况，有时候在写一些复杂的查询语句的时候脑子还真转不过弯来。所以，我们才需要经常练习写一些复杂的查询语句，熟悉它们的用法。

下面重点介绍一些具有代表性的例题，和一些在oracle中不经常用，但又十分重要的方法：

1、分组函数

a)查询各个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

分组函数的基本格式为 ：

select [column_name|function..] from table_name group by column_name;

意思为：以group by 后指定的字段名来为数据库中的记录进行分组，需要注意的是select后只可以有group by 后指定的字段和一些组合函数。

2、排序函数

a) 按照入职日期由新到旧排列员工信息

select * from emp order by hiredate desc;

排序函数的基本格式为：

Select * from table_name order by column_name [asc|desc];

意思为：表中的数据要根据column_name 这一列进行排序，asc为升序、desc为降序 默认为asc，一条排序语句也可以指定多个column_name，不过需要注意的是每个column_name都需要指定[asc|desc]，此时的意思为：先按第一个column_name排序，第一个column_name相同时，再按照第二个，以此类推。。

在排序查询时常常会遇到以下问题：

b) 查询工资最高的第6-10 名员工

select * from (select rownum r,e.* from (select * from emp order by sal desc) e) where r>=6 and r<=10

在oracle 中没有limit关键字，解决这样的问题时需要用到rownum 这个关键字，rownum会按当前数据库中数据的排列位置给数据表增加一个伪列，伪列的内容就是当前的顺序。又因为它是伪列，所以我们就用到了临时表等。

C) 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第三名继续排)

select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;

在orcale中用rank()、dense_rank(),rownum()这些聚合函数来解决这种排名问题，这些函数主要用于排序并给出序号，不过它们在遇到有相同名次的时候给添加序号的形式不同：

如果有并列第二名

Rank()的形式：                1,2,2,3,4,5,

Dense_rank()的形式：   1,2,2,4,5,6

Rownum()的形式：         1,2,3,4,5,6

3、多表查询

有时候查询结果需要来自多个表的字段，这时候就需要多表连接查询了，连接查询分为内连接、外连接、和自然连接。

a)       内连接

语句格式：

Select t1.*,t2.* from table1 t1 [inner] table2 t2;

Inner 关键字可以省略

b)       外连接

右外连接

SELECT      table1.column, table2.column

FROM        table1, table2

WHERE     table1.column(+)=table2.column;

左外连接

SELECT      table1.column, table2.column

FROM        table1, table2

WHERE     table1.column= table2.column(+);

4、自连接

a) 查询员工的基本信息，附加其上级的姓名

select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;

查询像这样树状结构的数据就要用到自连接

在oracle中用select … from tablename start with 条件1 connect by 条件2 where 条件3这种格式的查询语句解决这种递归问题，其中条件1要指出树状结构的根是什么，条件2是连接条件，条件3是过滤条件。

附件：

01. 查询各个部门的平均工资

答：考察知识点：分组

select deptno,avg(sal) from emp group by deptno;

02. 显示各种职位的最低工资

答：考察知识点：分组

select job,min(sal) from emp group by job;

03. 按照入职日期由新到旧排列员工信息

答：考察知识点：排序

select * from emp order by hiredate desc;

04. 查询员工的基本信息，附加其上级的姓名

答：考察知识点：自连接

select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;

05. 显示工资比'ALLEN'高的所有员工的姓名和工资

答：考察知识点：子查询

select * from emp where sal > (select sal from emp where ename='ALLEN');

分析：当查询结果是一行一列时，可以将此结果看做一个值，参与条件比较。

06. 显示与'SCOTT'从事相同工作的员工的详细信息

答：考察知识点：子查询

select * from emp where job = (select * from emp where ename='SCOTT');

分析：同第5 题

07. 显示销售部('SALES')员工的姓名

答：考察知识点：连接查询

select ename from emp e, dept d where e.deptno = d.deptno and d.dname='SALES';

08. 显示与30 号部门'MARTIN'员工工资相同的员工的姓名和工资

答：考察知识点：子查询

select ename, sal from emp

where sal = (select sal from emp where deptno=30 and ename='MARTIN');

分析：同第5 题

09. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMAN')

答：考察知识点：子查询

select * from emp where job='SALESMAN' and sal > (select avg(sal) from emp);

10. 显示所有职员的姓名及其所在部门的名称和工资

答：考察知识点：表连接

select ename, job, dname from emp e, dept d where e.deptno = d.deptno;

11. 查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地

答：考察知识点：表连接

select empno,ename,dname,loc from emp e, dept d

where e.deptno = d.deptno and danme='RESEARCH';

12. 查询各个部门的名称和员工人数

答：考察知识点：子查询，表连接

select * from (select count(*) c, deptno from emp group by deptno) e

inner join dept d on e.deptno = d.deptno;

分析：主要思路是要将子查询结果看做一个临时表，此临时表又可以与其他表做表连接

13. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位

答：考察知识点：子查询

select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;

分析：查询结果是一行一列，可以将查询结果看做一个值，进行条件比较

14. 查询工资相同的员工的工资和姓名

答：考察知识点：子查询

select * from emp e where (select count(*) from emp where sal = e.sal group by sal) > 1;

分析：此题目类似于17 题，见17 题分析。

15. 查询工资最高的3 名员工信息

答：考察知识点：子查询，rownum

select * from (select * from emp order by sal desc) where rownum <= 3;

分析：见21 题要点一

16. 按工资进行排名,排名从1 开始,工资相同排名相同(如果两人并列第1 则没有第2 名,从第三名继续排)

答：考察知识点：子查询

select e.*, (select count(*) from emp where sal > e.sal)+1 rank from emp e order by rank;

分析：此题的要点在于理解select count(*) from emp where sal > e.sal+1 的含义，e.sal 代表当前员工， 该子查询的含义就是求比当前员工工资高的人数个数：比此员工工资高的人数个数如果为0，表示此人排名第一，比此员工工资高的人数个数如果为1，表示此人排名第二… 所以该子查询结果就表示排名。