解题步骤:
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;