2018/12/19 oracle-sql练习

--nvl()第一个为空则返回第二个
select nvl(comm,0) comm
from emp;

--nvl2 如果第一个数不为空，返回第二个数，否则返回第三个数
select nvl2(comm,comm,0) comm
from emp;
--nullif 两个数相等为空 不等 返回第一个数
select nullif(10,40)
from dual;

--时间格式
select to_char(sysdate,'yyyy-mm-dd day hh24:mi:ss am')
from dual;
--coalesce()返回第一个不为空的参数，参数个数不限制
select coalesce(10,20,30)
from dual;
--case关键字的使用
select ename,deptno,
(
       case deptno
         when 10 then 'asd'
           when 20 then 'dasd'
             when 30 then 'da'
               else 'no' end
) deptname
from emp;
--decode关键字的使用
select ename,deptno,
decode(deptno,
10,'销售部',
20,'技术部',
30,'管理部'
) deptname
from emp;

--hello->ello
select trim('h' from 'hello')
from dual;

--' Hello '->'Hello'
select trim(' Hello ')
from dual;
--'bllb'->'ll'
select trim('b' from 'bllb')
from dual;

--'hello  '->'hello'
select trim('hello')
from dual;
--查询员工及其经理编号，没有经理编号的显示为‘no manager’
select ename,nvl(to_char(mgr),'no manager') mgr
from emp;
--查询员工入职日期格式为 月份/年份
select to_char(hiredate,'mon/yy')
from emp;
--查询员工工资，计算税：工资<1000税率0 1000<工资<2000 税率10% 2000<工资<3000税率15% 工资>3000 税率20%
select ename,sal,
case trunc(sal/1000)
when  0 then sal*0
  when 1 then sal*0.1
    when 2 then sal*0.15
      else sal*0.2 end 税
from emp;

--查询雇员的ename和sal，sal格式化成15个字符长度，左边用$填充，列标签为SALARY
select ename,lpad(sal,15,'$') SALARY
from emp;
--多表查询，员工姓名，编号，部门名称
select e.ename,e.empno,d.dname
from emp e
left join dept d
on e.deptno=d.deptno;
select * from dept;
--查询在芝加哥工作，并且奖金不为空的员工姓名，工作地点，奖金
select e.ename 姓名,d.loc 工作地点,e.comm 奖金
from emp e
left join dept d
on e.deptno=d.deptno
where e.comm is not null and d.loc = 'CHICAGO';
--查询姓名带有‘A’的员工姓名，工作地点
select e.ename,d.loc
from emp e
left join dept d
on e.deptno=d.deptno
where e.ename like '%A%';
--外部链接，(+)能增加一个万能的行，值为空，可以和另一个表中不满足条件的行相连接
select e.ename,d.loc,d.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
order by e.deptno;
--right join可以将右边加入的表中所有行查询出来包括不符合条件的。查询结果与上例外部连接结果相同
select e.ename,d.loc,d.deptno,d.dname
from emp e right join dept d
on e.deptno=d.deptno
order by e.deptno;
--自连接，查询员工姓名和该员工的上级的姓名
select e.ename 姓名,a.ename 上级姓名
from emp e,emp a
where e.mgr=a.empno;
--inner join
select e.ename name,a.ename mgr_name
from emp e full join emp a--full join显示两张表中所有行(即使不符合条件)
on e.mgr=a.empno;

--分组和分组函数的使用
--查询部门号为10的最高工资
select max(sal) 最高工资,deptno
from emp
where deptno=10
group by deptno;
--查询部门编号为20 的平均工资和月薪总和
select avg(sal) 平均工资,sum(sal) 工资总和
from emp
where deptno=20
group by deptno;

--查询CHICAGO的工作人数，最高工资和最低工资
select count(*) 人数,max(sal) 最高工资,min(sal) 最低工资,loc 工作地点
from emp natural join dept
where loc='CHICAGO'
group by loc;

--查询员工有几种工作岗位
--可以直接用distinct关键字去除重复
select distinct job
from emp;
--也可以用group by
select job
from emp
group by job;

--查询每个部门的平均工资
select avg(sal) 平均工资,dname 部门名称
from emp natural join dept
group by dname

--查询每个部门的部门编号，部门名称，部门人数，
--最高工资，最低工资，工资总和，平均工资
select deptno,dname 部门名称,count(*) 部门人数,
max(sal) 最高工资,min(sal) 最低工资,
sum(sal) 工资总和,avg(sal) 平均工资
from emp natural join dept
group by (deptno,dname);

--查询部门人数大于2的部门编号，部门名称，部门人数
select deptno 部门编号,dname 部门名称,count(*) 部门人数
from emp natural join dept
group by deptno,dname
having count(*)>2;

--查询部门平均工资大于2000，且人数大于2的
--部门编号，部门名称，部门人数，部门平均工资，
--并按照部门人数升序排序
select avg(sal) 平均工资,count(*) 人数，deptno
,dname
from emp natural join dept
group by deptno,dname
having count(*)>2 and avg(sal)>2000;
--查询平均工资的最大值
select max(avg(sal))
from emp
group by deptno;

--查询部门平均工资在2500元以上的部门名称及平均工资。
select avg(sal),dname
from emp natural join dept
group by dname
having avg(sal)>2500;
--查询岗位名称不是以'SA'开头并且平均工资大于2500的岗位名称和平均工资，按照平均工资降序排序
select avg(sal),job
from emp
where job not like 'SA%'
group by job
having avg(sal)>2500
order by avg(sal) desc;
--查询部门人数在2人以上的部门名称、最高工资、最低工资
--并对求得的工资进行四舍五入到整数
select dname,round(max(sal),0),round(min(sal),0)
from emp natural join dept
group by dname
having count(*)>2;
--查询岗位不为SALESMAN，工资和大于等于2500的
--岗位及每种岗位的工资和
select job,sum(sal)
from emp
where job <> 'SALESMAN'
group by job
having sum(sal)>=2500;
--显示经理号码和经理姓名，这个经理所管理
--的员工的最低工资，没有经理的king也要显示
--不包括最低工资小于3000的，按最低工资降序排序
select e.ename 经理,e.empno 经理编号,min(ee.sal) 所管理员工的最低工资
from emp e left join emp ee
on e.empno=ee.mgr
group by e.ename,e.empno
having min(ee.sal)>=3000
order by min(ee.sal) desc;

--写一个查询，显示每个部门最高工资和最低工资的差额
select e.deptno,dname,max(sal)-min(sal) 最高最低差额
from emp e join dept  d on e.deptno=d.deptno
group by e.deptno,dname;

--查询入职日期比10部门任意一个员工晚的员工姓名
--、入职日期，不包括10部门员工
--写法一
select ename,hiredate,deptno
from emp
where hiredate> any(select hiredate
                    from emp
                    where deptno=10
                    )and
deptno <> 10;
--写法二 查询出入职日期晚于10部门的任意一个员工的入职日期，那么只需求出10部门中
--员工最早（小）的入职日期，并且比最小日期大，那么就是比任意10部门的员工入职日期晚（大）。
select ename,hiredate,deptno
from emp
where hiredate>(select min(hiredate)
                from emp
                where deptno=10
                group by deptno) and
deptno <> 10;

--查询入职日期比10部门所有员工晚的员工姓名
--入职日期，不包括10部门员工
--写法一
select ename,hiredate,deptno
from emp
where hiredate >all(select hiredate
                    from emp
                    where deptno=10)and
deptno <> 10;
--写法二  大于10部门中入职日期最晚的员工的入职日期，那么就比所有10部门的员工入职日期大（晚）
select ename,hiredate,deptno
from emp
where hiredate > (select max(hiredate)
                  from emp
                  where deptno=10
                  group by deptno)and
deptno <> 10;      

--查询职位和10部门任意员工职位相同的员工姓名，职位，不包括10部门的员工
--写法一
select ename,job,deptno
from emp
where job in (select job
              from emp
              where deptno=10)and
deptno <> 10;
--写法二 in(..) 和 = any(..)效果相同
select ename,job,deptno
from emp
where job = any(select job
                from emp
                where deptno=10)and
deptno <> 10;
--查询职位及经理和10部门任意一个员工职位
--及经理相同的员工姓名，职位，不包括10部门员工
select ename,job,mgr
from emp
where (job,mgr) in (select job,mgr
                    from emp
                    where deptno=10)and
deptno <> 10;

--查询职位及经理和10部门任意一个员工职位
--或者经理相同的员工姓名，职位，不包括10部门员工
select ename,job,mgr
from emp
where job in (select job
              from emp
              where deptno=10)
       or
       mgr in(select mgr
              from emp
              where deptno=10)
       and
       deptno <> 10;