oracle 中自学做过的一些例子，方便自学的人，只是一些基础的题目

本文链接：https://blog.youkuaiyun.com/java_learning_zzh/article/details/9713577

/**DML语句（数据操作语言） Insert、Update、Delete、Merge*/
select * from scott.emp where comm>sal;
--oracle 中函数的用法
--小写转换为大写的函数
select upper('abcdefg') from dual;
--大写转换为小写的函数
select lower('ABCDEFG') from dual;
--
select initcap(ename) from scott.emp;
select ename from scott.emp;
--连接函数
select concat('a','b') from dual;/**注意一点，第一、concat函数只能连接两个字符串就是说concat中只能带两个参数*/
select 'a'||'b' from dual;/**这个||是看连接多个的，比如'A||B||C'*/
--字符串截取函数
select substr('abcde',length('abcde')-2) from dual;
select substr('abcde',-3,3) from dual;
--length函数
select length(ename) from scott.emp;
--replace函数，第二个参数是本来的值，第三个参数是用来替换的值
select * from scott.emp;
select replace(ename,'A','a') from scott.emp;
--Instr函数
select instr('Hello Word','or') as ename from dual;
--Lpad函数左侧填充* 同时总共的长度为第二个参数，如果第二个参数的长度都小于第一个字符串的长度，那么就相当于截取第一个字符串的第二个参数的长度
select Lpad('Smith',10,'*') from dual;
select Lpad('Smith',1,'*') from dual;
--Rpad 函数
select rpad('Smith','10','*') from dual;
--trim函数
select trim(' Smith ') from dual;
select ' Smith ' from dual;
--round 函数按照指定的精度进行舍入
select round(412,-2) from dual;
select round(412.313,-2) from dual;
--日期函数
--Months_between() ?
select sysdate from dual;
select to_date('2013/01/18 14:54:19') from dual
select * from scott.emp;
select months_between(sysdate,hiredate) from scott.emp;
--add_months 增加一个月的函数
select add_months(sysdate,1) from dual;
--next_day ?
select next_day(sysdate,'星期一') from dual;
--Last_day ?
select last_day(sysdate) from dual;
--转换函数
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'fmyyyy-mm-dd') from dual;--转换的时候用fm和不用fm的区别是比如月份小于10，那么用fm的话，前面的月份不会带0，如果不用的话就会带0
select to_char(sysdate,'yyyy-mm-dd') from dual;
--这个函数牛逼，哈哈，如果用L999,999,999的话就可以直接把钱转换为￥后面带钱的值
select to_char(sal,'L999,999,999') from scott.emp;
--返回具体的星期，这个也不错哦
select to_char(sysdate,'D')-1 from dual;
--To_number 转换为数字的函数
select to_number('13')+to_number('15') from dual;
--To_date函数
select to_date('20091010','yyyyMMdd') from dual;
--通用函数
--NVL函数相当于是怎么回事呢？就是如果数据库表中的某个字段为null,如果用nvl那么就会把空转换为0
select nvl(comm,0) from scott.emp;
--nullif函数,如果参数一和参数二的值相等，那么就返回null,否则返回参数一的值
select nullif(4,5) from dual;
select nullif('a','a') from dual;--如果是字母那么就用他们的ASCLL码进行比较
--NVL2函数这个函数很有用哦，用来求表中新水+奖金的值。nvl2(参数一，参数二，参数三)，
select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from scott.emp;
--coalesce 函数
select empno,ename,sal,comm,coalesce(sal+comm,sal,0) from scott.emp;
--case 表达式
select * from scott.emp;

select empno,ename,sal,
case deptno
     when 10 then '财务部'
     when 20 then '研发部'
     when 30 then '销售部'
     else '未知部门'
end 部门
from scott.emp;

select empno,ename,sal,
case deptno
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
   else '未知部门'
end 部门
from scott.emp;

--DECODE函数
-- 和case函数相似处理分支
select empno,ename,sal,decode(deptno,10,'财务部',20,'研发部',30,'销售部') from scott.emp;
--练习1。。。。找出每个月倒数第三天受雇的员工
select * from scott.emp;
select * from scott.emp where last_day(hiredate)-2 =hiredate;
--找出25年前雇的员工
select * from scott.emp where hiredate<=add_months(sysdate,-25*12);
--在所有的名字前面加上Dear,并且名字的首字母大写
select 'Dear'||initcap(ename) as 姓名 from scott.emp;
--找出姓名不带R这个字母的员工
select * from scott.emp where ename not like '%R%';
--显示所有员工姓名的第一个字
select substr(ename,0,1) from scott.emp;
--显示所有员工，按名字降序排列，若相同，则按照工资升序排列
select * from scott.emp order by ename asc,sal desc
--建设一个月为30天，找出所有员工的日薪，不计小数
select round(sal/30) from scott.emp;
--找到2月份受雇的员工
select * from scott.emp v where to_char(v.hiredate,'fmmm')=2
----------------------------分组函数-------------------------------------
--分组函数会省略字段的空值
select avg(comm) from scott.emp;
select sum(comm) from scott.emp;
--分组统计各部门下工资>500的员工的平均工资
select * from scott.emp;
select avg(sal),decode(deptno,10,'财务部',20,'开发部',30,'销售部') as 部门
       /*case deptno
        when 10 then '财务部'
        when 20 then '开发部'
        when 30 then '销售部'
        else '未知部门'
        end as 部门*/
from scott.emp where sal>500 group by deptno

--统计各部门下平均工资大于500的部门
select avg(sal) from scott.emp group by deptno having avg(sal)>500;
--算出部门30中得到最多奖金的员工奖金
select max(comm) from scott.emp v where v.deptno=30;
--算出部门30中得到最多奖金的员工姓名
select ename from scott.emp where comm=(select max(comm) from scott.emp v where v.deptno=30);
--算出每个职位的员工数和最低工资
select job,count(*),min(sal) from scott.emp group by job;
select * from scott.emp;
--算出每个部门，每个职位的平均工资和平均奖金（平均值包过没有奖金），如果平均奖金大于300，显示'奖金不错',如果平均奖金100到300，显示’奖金一般‘
--如果平均奖金小于100，显示’基本没有奖金‘ 按照部门编号降序，平均工资降序排列
select avg(sal),avg(nvl(comm,0)),
case
when avg(nvl(comm,0))>300 then '奖金不错'
when avg(nvl(comm,0))>100 and avg(nvl(comm,0))<300 then '奖金一般'
when avg(nvl(comm,0))<100 then '基本没有奖金'
end 奖金状况
from scott.emp group by deptno order by deptno desc,avg(sal) desc;

---列出员工表中每个部门的员工数和部门no
select deptno,count(*) from scott.emp group by deptno
--算出工资大于自己部门平均工资的员工信息
select * from scott.emp order by deptno;
select * from scott.emp v ,
(select deptno, avg(sal) as ssal from scott.emp group by deptno) v1
where v.deptno=v1.deptno and v.sal>v1.ssal
--分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资（包过奖金）
select * from scott.emp order by deptno;
select deptno,job,avg(nvl(comm,0)) as 平均奖金,sum(nvl(sal,0)+nvl(comm,0)) as 总工资 from scott.emp group by deptno,job;
---------------------------分组函数--------------------------------------

--------------------------多表查询---------------------------------------
select * from emp;
---分页查询
select * from (select rownum no,e.* from (select * from emp order by sal desc )e where rownum<=5) where no>=3;
--对于 in 和exists的区别
如果子查询得出的结果集记录较少，主查询中的表较大而且有索引时应该用in ,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

--列出员工表中每个部门的员工数，和部门no
select DEPTNO,count(*) 每个部门的人数 from emp group by DEPTNO
--列出员工表中每个部门的员工数(员工数必须大于3)和部门名称
select d.*, ee.cou from dept d,
(select emp.deptno,count(*) cou from emp group by emp.deptno having count(*) >3) ee where d.deptno=ee.deptno
--找出工资比JONES多的员工
select * from emp p where p.sal>(select sal from emp where ename='JONES');
--列出所有员工的姓名和他上级的姓名
select e1.ename as Lowers,e2.ename as Uppers from emp e1,emp e2 where e1.mgr=e2.empno;
--以职位分组，找出平均工资最高的两种职位
select * from (select * from (select avg(sal) as avgsal,job from emp group by job) ee order by ee.avgsal desc) where rownum<=2;
--查找出不在部门20，并且比部门20中任何一个人工资都高的员工姓名、部门名称
select ep1.ename, dept1.dname
from emp ep1, dept dept1
where ep1.deptno = dept1.deptno
and ep1.deptno <> '20'
and sal > (select max(sal) from emp where deptno = '20')
--得到平均工资大于2000的工作职种
select job, avg(sal) from emp v group by job having avg(sal)>2000
--分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500；
select * from (
select ee.deptno,avg(sal) as ssal from (
select deptno,sal from emp group by deptno,sal having sal>2000) ee group by ee.deptno) sss where sss.ssal>2500;
1 30 2850
2 20 2987.5
3 10 3725

select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500;

--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select *
from dept tt
where tt.deptno = (select ee.deptno
                      from (select deptno, sum(sal) as ssal
                              from emp
                             group by deptno
                             order by ssal asc) ee
                     where rownum < 2);
--分部门得到平均工资等级为2级（等级表）的部门编号
select deptno,avg(sal) from emp group by deptno;
select * from scott.salgrade
--查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置
select * from dept;
select p1.ename, d1.dname, d1.loc
from emp p1, dept d1
where p1.deptno = d1.deptno
   and p1.empno in (select ee2.empno
                      from (select rownum no, ee. *
                              from (select *
                                      from emp
                                     where emp.deptno = 10
                                        or emp.deptno = 20
                                     order by sal desc) ee) ee2
                     where ee2.no >= 3
                       and ee2.no <= 5);

select emp.ename, dept.dname, dept.loc
from emp,
       dept,
       (select rownum no, new.*
          from (select *
                  from emp
                 where emp.deptno = 10
                    or emp.deptno = 20
                 order by emp.sal desc) new) e
where emp.deptno = dept.deptno
   and e.no >= 3
   and e.no <= 5
   and e.empno = emp.empno;

--查找出收入(工资加上奖金)，下级比自己上级还高的员工编号，员工名字，员工收入
select e.ename, e.empno, (e.sal + nvl(e.comm, 0))
from emp e, emp m
where e.mgr = m.empno
   and (e.sal + nvl(e.comm, 0)) > (m.sal + nvl(m.comm, 0));
--查找出工资等级不为4级的员工的员工的名字，部门名字，部门位置