Oracle数据库查询

1.列出至少有一个员工的所有部门：


SELECT  deptno ,COUNT(*) come
FROM emp 
where (select COUNT(deptno) FROM emp) >1
GROUP BY deptno;


2.列出薪金比'SMITH'多的所有员工；


第一步：查询 emp表中ename = 'SMITH'的薪资
SELECT sal  FROM emp WHERE ename = 'SMITH';
第二步查询比'SMITH'多的员工
        SELECT empno,ename,sal
FROM emp
where sal>(SELECT sal FROM emp WHERE ename = 'SMITH');


3.列出所有员工的姓名，及其直接上级的姓名


需要用到的数据表 emp e ,emp m
已知的关联字段：e.mgr = m.empno

SELECT e.ename ,m.ename
FROM emp e, emp m
         where e.mgr=m.empno(+);


4.列出受雇日期早于其直接上级的所有员工；


需要用到的数据表 emp e,emp m
已知字段 e.mgr = m.empno


SELECT   e.ename,e.hiredate,m.hiredate
FROM emp e ,emp m 
WHERE e.mgr = m.empno(+)
AND e.hiredate >m.hiredate;


5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门；


需要用到的数据表
dept : 部门名称 ,dename
emp:部门的员工信息
确定已知字段： 
部门和雇员 depy.depton = emp.deptno


第一步：查询部门名称和员工信息
SELECT  d.dname,e.empno,e.ename,e.deptno
FROM dept d,emp e
WHERE  d.deptno = e.deptno(+);


6.列出所有'CLERK'的姓名及其部门名称


需要用到的数据库
emp 姓名
dept 部门名称
确定已知的字段;
雇员和编号：emp.deptno = dept.deptno


SELECT  e.ename,d.dname,e.job
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.job = 'CLERK';


7.列出最低薪金大于1500的各种工作。


需要用到的数据库
emp 薪金


SELECT DICTINCT e.job
FROM emp e
WHERE e.sal >1500 ;


8.列出部门'SALES'工作的员工的姓名，假定不知道销售部的部门编号


 需要用到数据表
  dept ：部门的名称
  emp 表：员工的姓名
 SELECT e.ename
 FROM emp e
 WHERE e.deptno = (SELECT  d.deptno FROM  dept  d
  WHERE d.dname = 'SALES');


9.列出薪金高于公司公司平均薪金的所有员工：


SELECT e.ename ,e.sal
FROM emp e
WHERE  e.sal >(SELECT AVG(sal) FROM emp);


10.列出与‘SCOTT’从事相同工作的所有员工；


SELECT e.ename ,e.job
FROM emp e
WHERE e.job = (SELECT job FROM emp WHERE ename='SCOTT')
AND e.ename <> 'SCOTT'; 


11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金：


第一步：求出部门30的员工的薪金
SELECT e.ename,e.sal ,e.deptno FROM  emp e
WHERE  e.deptno = 30;
第二步 所有员工的姓名和薪金
       SELECT e.ename,e.sal
       FROM emp e
       WHERE e.sal IN(
        SELECT e.sal FROM  emp e
WHERE  e.deptno = 30
);


12.列出薪金高于在部门30工作中所以员工的薪金的员工姓名和薪资


SELECT  e.ename,e.sal
FROM emp e
WHERE e.sal > All(      //大于最大的，或者小于最小的
SELECT e.sal FROM emp e
WHERE e.deptno  =30
);


13.列出在每个部门工作的员工数量、平均工作个平均服务期限


确定需要的数据表 
 dept  部门名称
         emp  得出员工数量,平均服务期限


   确定已知的关联字段
    雇员与部门：dept.deptno = emp.deptno


   第一步按照部门分组得到部门中员工的数量
   SELECT d.dname ,COUNT(*)
   FROM emp e ,dept d
   WHERE e.deptno(+) = d.deptno
   GROUP BY d.dname


第二步：求平均年限 


SELECT TRUNC (AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) 
FROM emp;

第三步：得出结果：
SELECT d.dname ,COUNT(*),
TRUNC (AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)) a
   FROM emp e ,dept d
   WHERE e.deptno(+) = d.deptno
   GROUP BY d.dname


14.列出所有员工的姓名，部门名称和工资：


确定需要的数据表
emp 姓名,工资
dept 部门名称


已知的关系字段：
雇员和员工：
emp.depton = dept.deptno

SELECT e.ename,d.dname,e.sal
FROM emp e ,dept d
WHERE e.deptno = d.deptno;


15.列出所有部门的详细信息和部门人数；


确定需要的数据表


dept 部门信息
emp 部门人数


已知的关系字段：


雇员和员工：
emp.depton = dept.deptno

SELECT d.deptno,d.dname,d.loc,COUNT(*)
FROM dept d ,emp e
WHERE e.deptno (+)= d.deptno
GROUP BY d.deptno,d.dname,d.loc;


16.列出各个工作的最低工资；

SELECT e.job,MIN(sal)
FROM emp e
GROUP BY e.job;


查询所有有佣金的雇员：

SELECT  e.empno,e.comm
FROM emp e
WHERE e.comm not null;






17.列出各个部门的'MANAGER'经理的最低薪金


第一步：查询每个部门中的 'MANGER' 的工资；

SELECT  d.dname,e.job
FROM emp e,dept d
WHERE e.deptno = d.deptno 
AND job = 'MANAGER';


第二步：按照部门分组，求出最低薪资


//nvl处理空
SELECT  d.dname,MIN(e.sal+NVL(comm, 0))
FROM emp e,dept d
WHERE e.deptno = d.deptno 
AND job = 'MANAGER',
AND comm is not null
GROUP BY d.dname ;


18.列出所有员工的年工资，按年薪从低到高排序。

SELECT (e.sal*12 + NVL(comm,0)) ss
FROM emp e 
ORDER BY ss ;


19.创建一个用户test,要求拥有scott用户表的查询余额修改权限,dept的查询权限


20.查出某个员工的所有上级主管，并要求这些主管中的薪水超过3000（用一条SQL语句解决）



SELECT e.ename,e.sal,m.ename,m.sal
FROM emp e ,emp m
WHERE e.mgr = m.empno
AND m.sal>3000;


21.求出部门名称中，带有‘S’字符的员工的工资合计、部门人数


要求：部门工资合计要大于5000，并且按照部门的人数排序


确定要使用的数据表：
emp:员工姓名，工资统计，
dept:部门人数


已知的关系字段：
雇员和员工：emp.depton = dept.deptno


第一步 部门名称中 带有‘S’字符的员工
SELECT e.ename,e.sal,d.dname
FROM emp e ,dept
WHERE e.ename LIKE '%S%'
AND e.depton = d.deptno;


第二步发现 有重复的数数据，可以进行分组,并且结果如何：


   SELECT d.dname,COUNT(*)come,SUM(e.sal) 
FROM emp e,dept d
WHERE e.ename LIKE '%S%'
AND e.deptno = d.deptno
GROUP BY d.dname
HAVING SUM(e.sal)>5000
ORDER by come ;




22、给任职日期超过10年的人加薪10%


第一步 查询日期超过10年的人


SELECT e.ename,e.hiredate,e.sal
FROM  emp e
WHERE  (MONTHS_BETWEEN(SYSDATE,hiredate)) > 120;


第二步：得出结果：


SELECT e.ename,e.hiredate,e.sal*1.1,e.sal
FROM  emp e
WHERE  (MONTHS_BETWEEN(SYSDATE,hiredate)) > 120;




23、列出员工任职的年、月、日

SELECT  ename,TO_CHAR(hiredate,'yyyy')year,TO_CHAR(hiredate,'mm') moneh,TO_CHAR(hiredate,'dd')day
FROM emp ;


24、列出员工中薪水最低，任职日期最长的员工。


SELECT MIN(e.sal),MAX(MONTHS_BETWEEN(SYSDATE,hiredate))
FROM  emp e


25、求出职工的周薪，保留两位小数、


第一步 ：得到月数：
   SELECT e.ename,ROUND((MONTHS_BETWEEN(SYSDATE,hiredate)),2) week 
   FROM  emp e;
   第二步 ：得到天数


------------------------------------------------------------------------------------
1.范例： 要求查询出每一位雇员到今天为止雇佣的年限；
第一步：年的计算比较简单：通过MONTHS_BETWEEN()来计算月，月除以12就是年
SECLECT  e.ename, e.hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)year
            FROM emp e
        第二步：月计算,对年求模就是月份


        SELECT e.ename,e.hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)year,
        TRUNC(MOD (MONTHS_BETWEEN(SYSDATE,hiredate),12) ) months
        FROM emp e;


        第三步：天数计算
        日期  - 数字 = 日期   ADD_MONTHS（日期，数字）做干月之后的日期：
        日期  - 日期 = 数字


        TRUNC( SYSDATE - ADD_MONTHS(hiredate,MONTHS_BETWEEN(hiredate,SYSDATE))) day




------------------------------------------------------------------------------------------
2.范例： 要求查询出每一位雇员到今天为止雇佣的年限；
第一步: 计算年 TRONC() --  截取小数，不进位
SELECT ename, hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year
FROM emp ;
第二步：计算月 MOD()求模：
SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) monse
FROM emp;
第三步：计算天：
日期  - 数字 = 日期
日期 - 日期 = 数字


TRUNC(SYSDATE - ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate)))day


SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) monse,
   TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate)))day


FROM emp;
--------------------------------------------------------------------------------------


1.列出与‘SCOTT’从事相同工作的所有员工及部门名称，部门人数，平均工资；


确定需要的数据库
emp： 所有的员工和统计人数和工资
dept：部门名称
确定已知字段 
雇员和部门   emp.deptno = emp.deptno 


第一步:找到和‘SCOTT ’ 从事相同工作的人
SELECT  e.ename,e.job,e.sal
FROM emp e 
WHERE  e.job = (SELECT job FROM emp WHERE  ename = 'SCOTT')
AND e.ename <> 'SCOTT';


第二步：部门名称，部门人数，平均工资


SELECT d.dname,COUNT(*),AVG(e.sal)
FROM emp e ,dept d
WHERE  e.job = (SELECT job FROM emp WHERE  ename = 'SCOTT')
AND e.ename <> 'SCOTT'
AND e.deptno = d.deptno
GROUNP BY d.ename ;
-----------------------------------------------------------------------------------------
范例2 ：列出薪金高于在部门30工作的所有员工的薪金，以及高于公司平均工资的员工姓名和薪金、部门名称
部门平均工资，个人的工资等级。


确定要使用的数据表：
emp : 员工的姓名和薪金
dept : 部门名称
salgrade : 工资等级


已知的关联字段：
雇员与部门： emp.deptno = dept.deptno;
雇员与工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal;


第一步：查询出所有在30部门工作的员工工资，
SELECT  sal  FROM  emp WHERE  deptno = 30;


第二步：查询所有大于部门30的员工的工资
SELECT sal FROM emp 
WHERE sal >ALL(
SELECT sal FROM emp WHERE deptno= 30;
)


第三步：查询所有高于公司平均工资的员工姓名和薪金
SELECT ename ,sal FROM emp 
WHERE sal >ALL ( 
SELECT  sal FROM emp  WHERE depeno = 30
)AND sal > (SELECT AVG(sal) FROM emp );


第四步：查询部门名称和部门的平均工资
SELECT e.ename,e.sal,temp.ag,d.dname
FROM emp e ,(
SELECT deptno,trunc(AVG(sal)) ag
FROM emp 
GROUP BY deptno
) temp,dept d
WHERE e.sal >ALL(
SELECT sal FROM emp WHERE deptno = 30
  )AND e.sal > (SELECT AVG(sal)FROM emp)
  AND d.deptno = e.deptno
  AND temp.deptno = e.deptno ;
第五步: 个 人的工资等级


SELECT e.ename,e.sal,temp.ag,d.dname
FROM emp e ,(
SELECT deptno,trunc(AVG(sal)) ag
FROM emp 
GROUP BY deptno
) temp,dept d,salgrade s
WHERE e.sal >ALL(
SELECT sal FROM emp WHERE deptno = 30
  )AND e.sal > (SELECT AVG(sal)FROM emp)
   AND d.deptno = e.deptno
   AND temp.deptno = e.deptno 
   AND e.sal BETWEEN s.losal AND s.hisal;
---------------------------------------------------------------------------------