Oracle基础练习

╰半夏微凉° 2022-09-26 02:51 156阅读 0赞

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;