表查询基础练习题

–1. 列出至少有两个员工的部门。

SELECT
      A.DEPTNO
FROM EMP A 
GROUP BY A.DEPTNO
HAVING COUNT(A.EMPNO)>=2;

–2. 查询工资大于或者等于2000 的员工信息

SELECT
      A.EMPNO,
      A.ENAME,
      A.JOB,
      A.MGR,
      A.HIREDATE,
      A.SAL,
      A.COMM,
      A.DEPTNO
FROM  EMP A 
WHERE A.SAL>=2000;

–3. 查询至少有两个员工的工资不低于2000的部门编号

SELECT
      A.DEPTNO
FROM EMP A 
WHERE A.SAL>=2000 
GROUP BY A.DEPTNO
HAVING COUNT(A.EMPNO)>=2; 

–4. 列出薪金比“SMITH”多的所有员工。

SELECT
      B.ENAME
FROM  EMP B
WHERE B.SAL>(SELECT
                   A.SAL
              FROM EMP A 
              WHERE A.ENAME='SMITH'
              );

–5. 查询工资 与 SCOTT 相同的员工信息

SELECT
      B.EMPNO,
      B.ENAME,
      B.JOB,
      B.MGR,
      B.HIREDATE,
      B.SAL,
      B.COMM,
      B.DEPTNO
FROM EMP B
WHERE B.SAL=(SELECT
                  A.SAL
             FROM EMP A
             WHERE A.ENAME='SCOTT'
            );

–6. 查询工资 与 SCOTT 相同的员工信息,并且不返回SCOTT自己的信息

SELECT
      B.EMPNO,
      B.ENAME,
      B.JOB,
      B.MGR,
      B.HIREDATE,
      B.SAL,
      B.COMM,
      B.DEPTNO
FROM EMP B
WHERE B.SAL=(SELECT
                  A.SAL
             FROM EMP A
             WHERE A.ENAME='SCOTT'
            )
       and b.ename <>'SCOTT';

–7．列出所有员工的姓名及其直接上级的姓名。

SELECT
      B.ENAME
     ,A.ENAME 
FROM EMP A
INNER JOIN EMP B
        ON A.EMPNO=B.MGR;

–8．列出受雇日期早于其直接上级的所有员工。

SELECT
      B.ENAME
FROM EMP A
INNER JOIN EMP B
        ON A.EMPNO=B.MGR
       AND A.HIREDATE>B.HIREDATE ; 

–9．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT
     A.DNAME
    ,B.EMPNO,
     B.ENAME,
     B.JOB,
     B.MGR,
     B.HIREDATE,
     B.SAL,
     B.COMM,
     B.DEPTNO 
FROM DEPT A
LEFT JOIN EMP B
       ON A.DEPTNO=B.DEPTNO;

–10.列出所有“CLERK”（办事员）的姓名及其部门名称。

SELECT
       A.ENAME
      ,B.DNAME 
FROM EMP A
INNER JOIN DEPT B
        ON A.DEPTNO=B.DEPTNO
WHERE A.JOB='CLERK';

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

SELECT
       A.JOB
FROM EMP A
GROUP BY A.JOB
HAVING MIN(A.SAL)>1500;

–12.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

SELECT
      A.ENAME
FROM EMP A
WHERE A.DEPTNO=(SELECT
                      B.DEPTNO
                FROM DEPT B
                WHERE B.DNAME='SALES'
                );
 ---------------------------------------
 SELECT
       A.ENAME
 FROM EMP A
 INNER JOIN DEPT B
         ON A.DEPTNO=B.DEPTNO
        AND B.DNAME='SALES' ;
------------------------------------- WITH AS 临时表
WITH ABC AS (SELECT
                  B.DEPTNO
            FROM DEPT B
            WHERE B.DNAME='SALES'
              )
    SELECT
          A.ENAME
    FROM EMP A
    INNER JOIN ABC B
            ON A.DEPTNO=B.DEPTNO ;      

–13.列出薪金高于公司平均薪金的所有员工。

SELECT
     B.ENAME
FROM EMP B
INNER JOIN(SELECT
                  AVG(A.SAL) AVG_SAL
            FROM EMP A
            )A1
         ON B.SAL>A1.AVG_SAL ;  
------------------------------
select
       B.ENAME
from emp b 
WHERE B.SAL>(SELECT
                    AVG(A.SAL)
              FROM EMP A 
              );        

–14.列出与“SCOTT”从事相同工作的所有员工。

SELECT
     B.ENAME
FROM EMP B
WHERE B.JOB=(SELECT
                    A.JOB
              FROM EMP A
              WHERE A.ENAME='SCOTT'
              );

–15.列出薪金等于部门30中员工的薪金的非30号部门的员工的姓名和薪金。

SELECT
      A.ENAME
     ,A.SAL 
FROM EMP A
INNER JOIN EMP B
        ON A.SAL=B.SAL
       AND A.DEPTNO<>B.DEPTNO 
       AND B.DEPTNO=30;
-----------------------
SELECT  
      A.SAL
     ,A.ENAME 
FROM EMP A
WHERE EXISTS(SELECT 
                   B.SAL
             FROM EMP B 
             WHERE B.DEPTNO=30 
                  AND A.SAL=B.SAL
             )
     AND A.DEPTNO<>30;  
------------------------
SELECT
      A.SAL
     ,A.ENAME 
FROM EMP A
WHERE A.SAL IN(SELECT B.SAL FROM EMP B WHERE B.DEPTNO=30)
      AND A.DEPTNO<>30;
                  

–16.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT
      A.ENAME
     ,A.SAL 
FROM EMP A 
WHERE A.SAL>(SELECT MAX(B.SAL) FROM EMP B WHERE B.DEPTNO=30);
            

–17.列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT
     A.DEPTNO
    ,COUNT(A.EMPNO)  
    ,ROUND(AVG(A.SAL),2)
    ,ROUND(AVG(SYSDATE-A.HIREDATE)/365)
FROM EMP A
GROUP BY A.DEPTNO;

–18.列出所有员工的姓名、部门名称和工资。

SELECT
      A.ENAME
     ,B.DNAME
     ,A.SAL
FROM EMP A
LEFT JOIN DEPT B
       ON A.DEPTNO=B.DEPTNO;

–19.列出所有部门的详细信息和部门人数。

WITH ABC AS(SELECT
                  A.DEPTNO
                 , COUNT(A.EMPNO) COUNT_M
            FROM EMP A
            GROUP BY  A.DEPTNO
            )
       SELECT
             B.DEPTNO
            ,B.DNAME
            ,B.LOC
            ,NVL(C.COUNT_M ,0)
       FROM DEPT B
       LEFT JOIN ABC C
               ON B.DEPTNO=C.DEPTNO  ;   

–20.列出各种工作的最低工资。

SELECT
      A.JOB
     ,MIN(A.SAL) 
FROM EMP A
GROUP BY A.JOB; 

–21.列出各个部门的MANAGER（经理）的最低薪金。

SELECT
     A.DEPTNO
    ,MIN(A.SAL) 
FROM EMP A
WHERE A.JOB='MANAGER' 
GROUP BY A.DEPTNO;

–22.列出所有员工的年工资,按年薪从低到高排序。

SELECT
     A.ENAME
    ,(A.SAL+NVL(A.COMM,0))*12
FROM EMP A
ORDER BY (A.SAL+NVL(A.COMM,0))*12;

–23.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

SELECT
      A.EMPNO
     ,A.ENAME
     ,A.SAL 
FROM EMP A
WHERE A.SAL>=3000;

–24.查询出所有薪水在’ALLEN’之上的所有人员信息。

SELECT
      A.ENAME
FROM EMP A
WHERE A.SAL>(SELECT B.SAL FROM EMP B WHERE B.ENAME='ALLEN');

–25.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水

SELECT
     A.EMPNO 员工号
    ,A.ENAME 姓名
    ,A.SAL   薪水 
FROM EMP A
WHERE A.SAL>2000;

–26.查询出emp表中所有的工作种类（无重复）

SELECT
       A.JOB
FROM EMP A
GROUP BY  A.JOB;

–27.查询出所有奖金（comm）字段不为空的人员的所有信息。

SELECT
       A.EMPNO,
       A.ENAME,
       A.JOB,
       A.MGR,
       A.HIREDATE,
       A.SAL,
       A.COMM,
       A.DEPTNO
FROM EMP A
WHERE A.COMM IS NOT NULL;

–28.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）

SELECT
      a.ename
FROM EMP A
WHERE A.SAL>=800
   AND A.SAL<=2500;
   ------------------
   SELECT
      a.ename
FROM EMP A
WHERE A.SAL between 800AND 2500;

–29.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）

select
      A.ENAME
from EMP A
WHERE A.EMPNO IN(7521,7900,7782);
---------------------------------------
select
      A.ENAME
from EMP A
WHERE A.EMPNO =7521
     OR A.EMPNO =7900
     OR A.EMPNO =7782;

–30.查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

SELECT
     *
FROM EMP A
WHERE A.ENAME LIKE '%A%'
    AND A.SAL>1000;
--31.查询出名字第三个字母是“M”的所有员工信息。
SELECT
      A.ENAME
FROM EMP A
WHERE A.ENAME LIKE '__M%';

–32.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

SELECT
      *
FROM EMP A
order by A.SAL ,A.HIREDATE DESC;

–33.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。

SELECT
     *
FROM EMP A
ORDER BY SUBSTR(A.ENAME,1,1),A.SAL DESC;

–34.查询出最早工作的那个人的名字、入职时间和薪水。

SELECT
       B.ENAME
      ,B.HIREDATE
      ,B.SAL
FROM EMP B
WHERE B.HIREDATE=(SELECT
                       MIN(A.HIREDATE)
                  FROM EMP A
                  );

–35.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

SELECT
    A.ENAME
   ,A.SAL
   ,NVL(A.COMM,100)
FROM EMP A;

–36.显示出薪水最高人的职位。

SELECT
     A.JOB
FROM EMP A
WHERE A.SAL=(SELECT MAX(SAL) FROM EMP );

–37.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

SELECT
      A.DEPTNO
     ,MAX(A.SAL)
     ,MIN(A.SAL) 
FROM EMP A
WHERE A.DEPTNO<>10
GROUP BY A.DEPTNO;

–38.删除10号部门薪水最高的员工(不需要 commit)。

DELETE FROM EMP A WHERE A.SAL=(SELECT MAX(B.SAL) FROM EMP B WHERE B.DEPTNO=10);
SELECT * FROM EMP 

–39.将薪水最高的员工的薪水降30%。

UPDATE EMP A SET  A.SAL=A.SAL*0.7 WHERE A.SAL=(SELECT MAX(B.SAL) FROM EMP B );

–40.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)

SELECT
     A.ENAME 姓名
    ,A.SAL  工资
    ,CASE WHEN A.SAL >=3000 THEN '3级'
           WHEN A.SAL >2000 THEN '2级'
            WHEN A.SAL <=2000 THEN '1级'
        END 工资级别
FROM EMP A ;