【oracle】oracle查询习题总结

解题步骤：
    1.要查询哪些字段、表
    2.连接条件

 

1.查询所有员工的年工资、所在部门的名称，按年薪从低往高排序。

(1).要查询哪些字段、表:

    sal*12 + nvl(comm ,0)  年工资,  dname  部门名称
    emp e, dept d

&nvl使null变为0；
(2).连接条件:    
    e.deptno = d.deptno
(3)答案：

 

    select sal*12 + nvl(comm ,0)  年工资,  dname  部门名称
    from emp e, dept d where  e.deptno = d.deptno order by 年工资 asc ;

 

2.查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年工资降序。

(1)要查询哪些字段、表

    e.empno  ,e.ename , b.empno   ,b.ename

    emp e，emp b 

(2)连接条件
    e.mgr = b.empno 

KING：大老板 没有上级领导

(3)答案

select e.empno,e.ename,b.empno,b.ename
from emp e，emp b 
where e.mgr = b.empno(+)
order by b.sal*12+nvl(b.comm,0) desc ;

&&
误区： 阅读理解！ 
正确做法：翻译  文字->SQL

 

&&&

筛选组用having，筛选行用where；

3.查询非销售人员的 ：工作名称，以及从事同一工作员工的月工资之和，要求月工资之和大于5000，输出结果按月工资之和降序排列。

(1)要查询哪些字段、表
select job ,sum(sal)
from emp
where job <> 'SALESMAN'
    group by job
    having sum(sal)>5000
    order by  sum(sal) desc ;

salesmane: 10  ->8000
dev:  8  -->7000
test :6  -->4000

(2)连接条件
错误范例：
select count(*),avg(sal)
from emp
group by comm ;

(3)重新读题：

select count(*),avg(sal)
from emp  where comm is not null and comm > 0 
union 
select count(*),avg(sal)
from emp  where  comm is  null or comm = 0;

 

4.查询所有领取奖金和不领取奖金的员工人数、平均工资。.

&&要留意奖金为0和奖金为null的情况。

(1)错误范例：select count(*) ,avg(sal) from emp group by comm;

(2)答案：

select count(*),avg(sal)
from emp where comm is not null and comm > 0
union
select count(*),avg(sal)
from emp where comm is null or comm = 0;

 

5.查询每种工作的最低工资，以及领取该工资的员工姓名。

（oracle12课时42min详细讲解）

&&select后的字段，如果不在聚合函数中，就必须出现在 group by 后面。也就是说只要使用了group by，在没有聚合函数情况下，select后面的字段要和group by后面的字段保持一致。
错误：select min(sal) , ename from emp group by job;
语法和逻辑 混乱：
语法：group by ename
逻辑： group by job

解决方案：
    概念:两张表 连接条件：
1.一般推荐做法： 外键=主键，    emp.depnto = dept.deptno
2.全值连接（全部的普通字段）:如果两张表中 的 大部分字段值一直，则可以认为 是一条数据。

答案：

select t.minsal, t.job ,e.ename 
from emp e,
(select min(sal) minsal ,job from emp group by job) t
where t.minsal = e.sal and t.job = e.job ; 

6.查询出工资不超过2500的人数最多的部门名称。

各组(各个部门)  中员工人数最大
select max(count(*))    from emp where sal <=2500 group by deptno ;

select  d.deptno, d.dname from dept  d ,emp e
where d.deptno = e.deptno
and e.sal<=2500
group by d.deptno , d.dname
having count(*) = (select max(count(*)) from emp where sal <=2500 group by deptno) ;

 

7.查询出管理 员工人数最多的人的名字和他管理的人的名字。

1.查询：最多是多少

(1)先根据mgr分组：根据领导分组

select count(*) from emp group by mgr;

(2)再求管理人数最多的：

select max(cn) from(

select count(*) from emp group by mgr;

)

2.查询：谁管的人数最多

(1)select mgr from emp group by mgr having count(*) = 人数最多;

(2)人数最多 = select count(*) from emp group by mgr;

(3)

select mgr from emp 
group by mgr 
having count(*) = (select max(cn) from (select count(*) cn from emp group by mgr));

3.查询： 

select b.ename,e.ename from  emp e
inner join emp b
on e.mgr = b.empno
where e.mgr=(select mgr from emp group by mgr having count(MGR)=
(select max(cn) from(
select count(MGR) cn from emp group by mgr
)));

8.统计各个年份的入职人数、以及总入职人数。

1.类型转换

hiredate:年
    date->varchar  (类型转换)

to_char(hiredate,'yyyy')->1980

2.根据年份分情况讨论

各个年份：选择分支
        case（java）
        decode (sql)

格式：

decode(条件,值1,返回值1,值2,返回值2,...,else)

例：

1980年入职人数
decode(to_char(hiredate,'yyyy'),1980,1,0)

1981年入职人数
decode(to_char(hiredate,'yyyy'),1981,1,0)

1987年入职人数
decode(to_char(hiredate,'yyyy'),1987,1,0)

->sum

思路：
1980 ：14条数据：  1     0
 

select count(*) 总人数, 
sum( decode(to_char(hiredate,'yyyy'),1980,1,0) ) "1980",
sum( decode(to_char(hiredate,'yyyy'),1981,1,0) ) "1981",
sum( decode(to_char(hiredate,'yyyy'),1987,1,0) ) "1987"
from emp;