内部员工Oracle基础练习
1. 计算100被9除的余数（取模计算）
答：select mod(100,9) from dual
2. 读取系统时间，并转换成一定的格式（yyyy/mm/dd hh:mm:ss）
格式:年度(4位数字)+'/'+月份(2位数字)+'/'+日期(2位数字)+’ ’+小时:分:秒
答：select to_char(systimestamp,'yyyy/mon/dd hh24:mi:ss') from dual
3. 如何用一条SQL，取得本月第一天、最后一天，当天日期（没时间）、之后第3 天日期
Select
Extract(DAY FROM Add_months(last_day(sysdate)+1,-1)) --本月第一天
,last_day(sysdate) --最后一天
,sysdate --当天日期（没时间）
,sysdate+3 --之后第3 天日期
from dual
4. 用一SQL显示所有职工的佣金内容数据,格式如下：
如有佣金值，则显示“佣金:”+佣金值；没有，则显示“佣金:无”
SELECT ENAME,nvl2(comm,'佣金：'||comm,'佣金：无') comm FROM EMP;
5. 查询一下单位中所有的人数，最高工资，最低工资，平均工资，最高分红，最低分红
select count(1),max(sal),min(sal),avg(sal),max(comm),min(comm) from emp;
6. 查询每个部门中担任相同职务的人数
select deptno,job,count(job) from emp group by deptno,job
7. 查询每个部门中职工（CLERK）的人数
select deptno,job,count(job) from emp group by deptno,job having job='CLERK'
8. 查询不同年度参加工作的人的人数和平均工资
SELECT COUNT(1),AVG(SAL),EXTRACT(YEAR FROM HIREDATE) FROM EMP
GROUP BY EXTRACT(YEAR FROM HIREDATE)
9. 查询每个经理的直接下属人数
SELECT DEPTNO,COUNT(1) FROM EMP WHERE MGR IN
(SELECT EMPNO FROM EMP WHERE JOB='MANAGER') GROUP BY DEPTNO
10. 查询每个部门的平均工资,以及该部门的最高工资和平均工资的差
SELECT DEPTNO,AVG(SAL),MAX(SAL)-AVG(SAL) FROM EMP GROUP BY DEPTNO
11. 查询人数超过3个人的职务,按照人数排序
SELECT * FROM (SELECT JOB,COUNT(JOB) J FROM EMP GROUP BY JOB ORDER BY J)
WHERE J>3
12. 查询人数超过3人的部门,按部门排序
SELECT * FROM (SELECT DEPTNO,COUNT(1) J FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO) WHERE J>3
13. 查询有超过两个“职员”的部门
SELECT * FROM (SELECT DEPTNO,COUNT(1) J FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO) WHERE J>2
14. 按照不同工资1000以下，1000~2000，2000~3000，3000以上分等，每等级有多少个人?
SELECT SAL_GRADE,COUNT(1) FROM(
SELECT CASE
WHEN SAL>=3000 THEN 1
WHEN SAL>=2000 AND SAL<3000 THEN 2
WHEN SAL>=1000 AND SAL<2000 THEN 3
ELSE 4
END SAL_GRADE
FROM EMP)GROUP BY SAL_GRADE
15. 查询所有职工的姓名，以及他们直接领导的姓名（如果有的话）
SELECT A.ENAME,B.ENAME FROM EMP A,EMP B WHERE A.MGR=B.EMPNO
16. 查询员工为“SMITH”的所有上级
set serveroutput on;
DECLARE
mgrno emp.empno%TYPE;
TYPE empno_type IS TABLE OF emp.empno%TYPE
INDEX BY BINARY_INTEGER;
empnos empno_type;
indx int:=0;
BEGIN
SELECT mgr
INTO mgrno
FROM emp
WHERE ename = 'SMITH';
COMMIT;
WHILE (mgrno IS NOT NULL)
LOOP
EXECUTE IMMEDIATE 'SELECT MGR FROM EMP WHERE EMPNO=(' || mgrno || ')'
INTO mgrno;
empnos (indx) := mgrno;
EXIT WHEN mgrno IS NULL;
indx:=indx+1;
END LOOP;
FOR i IN empnos.FIRST .. empnos.LAST
LOOP
DBMS_OUTPUT.put_line (empnos (i));
END LOOP;
END;
17. 查询员工为“JONES”的所有下级
select empno,ename,job,mgr,deptno,level
,sys_connect_by_path(ename,'\') as path
,connect_by_root(ename) as top_manager
from emp
start with ename='JONES'
CONNECT BY PRIOR EMPNO=MGR;
18. 查询每个经理（MANAGER）的所有下级
select empno,ename,job,mgr,deptno,level,
sys_connect_by_path(ename,'\') as path
,connect_by_root(ename) as top_manager
from emp
start with job='MANAGER'
CONNECT BY PRIOR EMPNO=MGR;
19. 假设，每个人的工资每满1000元，要多扣除10%的税费，不足一千元的扣除5%的税费查看每个职工扣多少钱？
例如：2320元要扣除 2000 * 20% + 320 * 5% = 416
select sal,floor(sal/1000)*0.1*1000+(sal-floor(sal/1000)*1000)*0.05 from emp
20. 计算职工津贴，
算法为：参加工作每满一年津贴加15元，不足一年的如果超过4个月，则加10元
select floor(months_between(sysdate,hiredate)/12)*15+
case when floor(mod(months_between(sysdate,hiredate),12))>4 then 10 else 0 end from emp;
