34道SQL作业题

表结构

员工表

image-20210114185257840

部门表

image-20210114185331481

薪资等级表

image-20210114185509154

– 1、取得每个部门最高薪水的人员名称

– STEP1 取得每个部门最高薪水（按照部门编号编组，找出每一组最大值）

SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO;

– STEP2 与人员表连接，连接条件：where t.DEPTNO = e.DEPTNO AND t.MAXSAL = e.SAL

SELECT 
    e.ename,e.sal,e.DEPTNO 
from 
    (SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO) t
join 
    EMP e
on 
    t.DEPTNO = e.DEPTNO AND t.MAXSAL = e.SAL;

– STEP2 与人员表连接，连接条件：where t.DEPTNO = e.DEPTNO AND t.MAXSAL = e.SAL

SELECT 
    e.ename,e.sal,e.DEPTNO 
from 
    (SELECT DEPTNO,MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO) t
join 
    EMP e
on 
    t.DEPTNO = e.DEPTNO AND t.MAXSAL = e.SAL;

– 2、哪些人的薪水在部门的平均薪水之上

– STEP1 找出每个部门平均薪水

SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO;

– STEP2 与人员表连接，连接条件：where t.DEPTNO = e.DEPTNO AND t.AVGSAL < e.SAL

SELECT 
    e.ename,e.sal,e.DEPTNO,t.AVGSAL 
from 
    (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t
join 
    EMP e
on 
    t.DEPTNO = e.DEPTNO AND t.AVGSAL < e.SAL;

– 3、取得部门中（所有人的）平均的薪水等级

– STEP1 取得部门中（所有人的）的薪水等级
– 与等级表连接，连接条件：where e.SAL >= s.LOSAL AND e.SAL <= s.HISAL
– 也可使用连接条件：e.SAL BETWEEN s.LOSAL AND s.HISAL

SELECT 
    s.GRADE,e.*
from
    EMP e join SALGRADE s
on 
    e.SAL >= s.LOSAL AND e.SAL <= s.HISAL;

– STEP2 取得部门中（所有人的）平均的薪水等级

SELECT 
    t.DEPTNO,AVG(t.GRADE) AVGGRADE 
FROM
    (SELECT s.GRADE,e.* from EMP e join SALGRADE s on e.SAL >= s.LOSAL AND e.SAL <= s.HISAL)t 
group by  
    t.DEPTNO;

– STEP2 取得部门中（所有人的）平均的薪水等级

SELECT 
    t.DEPTNO,AVG(t.GRADE) AVGGRADE 
FROM
    (SELECT s.GRADE,e.* from EMP e join SALGRADE s on e.SAL >= s.LOSAL AND e.SAL <= s.HISAL)t 
group by  
    t.DEPTNO;

– STEP2 直接使用group by和分组函数 不使用临时表

SELECT 
    DEPTNO,AVG(GRADE) AVGGRADE 
FROM
    EMP e 
join 
    SALGRADE s 
on 
    e.SAL  BETWEEN s.LOSAL AND s.HISAL
group by  
    DEPTNO;

​

​

– 4、不准用组函数（Max），取得最高薪水

– 方案一 采用SAL排序,取第一行rownum=1

SELECT sal from (SELECT * from emp  order by SAL desc) t where rownum=1;

– 方案二 表的自连接 注意思路

select sal from emp where sal not in (SELECT distinct a.sal from emp a join emp b on a.sal < b.sal);

– 方案三 MAX

SELECT MAX(sal) from emp;

– 5、取得平均薪水最高的部门的部门编号

– 方案一：降序取第一个

SELECT * FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL DESC) m where rownum = 1;

– 方案二：MAX 注意having后面跟着AVG(SAL)，mysql可以直接跟AVGSAL

 SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) = (SELECT MAX(AVGSAL) from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO));

– 6、取得平均薪水最高的部门的部门名称

SELECT DNAME from DEPT where DEPTNO = (SELECT DEPTNO FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL DESC) m where rownum = 1);

– 7、求平均薪水的等级最低的部门的部门名称

– 错误解法

SELECT DNAME from DEPT where DEPTNO = (SELECT DEPTNO FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL ) m where rownum = 1);

– 注意平均薪水最低，平均薪水等级一定最低，但是平均薪水不是最低，平均薪水等级也有可能是最低
– 所以平均薪水等级最低的有可能是多个，不能通过降序取第一个部门编号，而是取最低等级拿等级做筛选
– STEP1 取得所有部门平均薪水的等级

SELECT m.*,t.* FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL ) m JOIN SALGRADE t ON m.AVGSAL BETWEEN t.LOSAL AND t.HISAL;

– STEP2 取得最低的平均薪水的等级

SELECT MIN(GRADE) FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL ) m JOIN SALGRADE t ON m.AVGSAL BETWEEN t.LOSAL AND t.HISAL;

– STEP3 取得平均薪水的等级最低的部门的部门名称

SELECT DNAME from DEPT where DEPTNO = (SELECT DEPTNO FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL ) m JOIN SALGRADE t ON m.AVGSAL BETWEEN t.LOSAL AND t.HISAL where GRADE = (SELECT MIN(GRADE) FROM (SELECT * from  (SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) t ORDER BY AVGSAL ) m JOIN SALGRADE t ON m.AVGSAL BETWEEN t.LOSAL AND t.HISAL));

– 8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

– STEP1 取得普通员工

SELECT distinct MGR FROM EMP WHERE MGR IS not null;

– STEP2 取得普通员工最高薪水 not in 在使用时后面括号中的结果要排除null

SELECT MAX(SAL) MAXSAL from EMP where EMPNO not in (SELECT distinct MGR FROM EMP WHERE MGR IS not null);

– STEP3 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

SELECT ENAME from EMP where EMPNO  in (SELECT distinct MGR FROM EMP WHERE MGR IS not null) and SAL > (SELECT MAX(SAL) MAXSAL from EMP where EMPNO not in (SELECT distinct MGR FROM EMP WHERE MGR IS not null));

– 9、取得薪水最高的前五名员工

SELECT * from (SELECT * from EMP  ORDER BY SAL DESC) t WHERE rownum <6;

– 10、取得薪水最高的第六到第十名员工 mysql使用 limmit 5,5

SELECT * from (SELECT * from (SELECT * from (SELECT * from EMP  ORDER BY SAL DESC) t WHERE rownum <11) tt ORDER BY SAL) ttt  WHERE rownum <6;

– 11、取得最后入职的5名员工

SELECT * from (SELECT * from EMP  ORDER BY HIREDATE DESC)WHERE rownum <6;

– 12、取得每个薪水等级有多少员工

– STEP1 获得员工等级

SELECT s.GRADE,e.* from EMP e join SALGRADE s on e.SAL >= s.LOSAL AND e.SAL <= s.HISAL;

– STEP2 按员工等级分组计数

​ 

SELECT 
    t.GRADE,COUNT(*) 
FROM
    (SELECT s.GRADE,e.* from EMP e join SALGRADE s on e.SAL >= s.LOSAL AND e.SAL <= s.HISAL)t 
group by  
    t.GRADE;

​

– 13、面试题

– 14、列出所有员工及领导的姓名

– 错误解法:使用join后，会缺少king的信息

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

– 正确解法:使用left join后，会保证左边表格的信息的完整性，如果未匹配到信息则以null显示,这样KING的信息正常显示

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

– 15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

– 注意此时使用JOIN ,join可以连用

SELECT 
    A.ENAME 员工,B.ENAME 领导,d.DNAME 部门名称,a.HIREDATE 员工受雇日期,b.HIREDATE 领导受雇日期 
FROM 
    EMP A 
JOIN 
    EMP B 
ON 
    A.MGR = B.EMPNO 
JOIN 
    DEPT d 
ON 
    a.deptno = d.deptno 
where 
    A.HIREDATE < B.HIREDATE;

– 16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

SELECT 
    a.DNAME,b.*
FROM
    DEPT a
left join
    EMP b
on 
    a.DEPTNO = b.DEPTNO;

​

– 17、列出至少有5个员工的所有部门

– SETP1 按DEPTNO分组计数

SELECT DEPTNO,count(*) from emp group by DEPTNO;

– SETP2 获取至少5个员工的所有部门

SELECT DEPTNO,count(*) from emp group by DEPTNO having count(*)>4;

– 18、列出薪金比”SMITH”多的所有员工信息

– SETP1 获取”SMITH”的薪金

SELECT SAL from emp WHERE ENAME = 'SMITH';

– STEP2 列出薪金比”SMITH”多的所有员工信息

SELECT * from emp WHERE SAL > (SELECT SAL from emp WHERE ENAME = 'SMITH');

– 19、列出所有”CLERK”(办事员)的姓名及其部门名称,部门的人数

– 列出所有”CLERK”(办事员)姓名及其部门名称,部门的人数

SELECT 
    ENAME,DNAME,ENUM 
from 
    emp e 
join 
    dept d  
on 
    e.DEPTNO = d.DEPTNO 
JOIN  
    (SELECT DEPTNO,count(*) ENUM from emp group by DEPTNO) t 
ON 
    e.DEPTNO = t.DEPTNO   
WHERE 
    JOB = 'CLERK';

– 20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

– SETP1 获取各种工作的最低薪金

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

– SETP2 列出最低薪金大于1500的各种工作

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

– SETP2 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数 按job分组计数后筛选

SELECT JOB,COUNT(*) MINSAL FROM EMP GROUP BY JOB HAVING JOB IN (SELECT JOB MINSAL FROM EMP GROUP BY JOB HAVING MIN(SAL) >1500);

– 21、列出在部门”SALES”<销售部>工作的员工的姓名,假定不知道销售部的部门编号

SELECT ename FROM EMP e join DEPT d on e.DEPTNO = d.DEPTNO where dname = 'SALES';

– 22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

– SETP1 获取公司平均薪金

SELECT avg(Sal) from emp;

– SETP2 连接员工，部门，salgrade表

SELECT 
    *
from 
    emp a
left join 
    emp b
on 
    a.mgr = b.EMPNO
join 
    DEPT d
on  
    a.DEPTNO = d.DNAME
join 
    SALGRADE s
on  
    a.sal between s.LOSAL and s.HISAL;

– SETP3 获取薪金高于公司平均薪金的所有员工

SELECT 
    a.ENAME 员工,d.dname 部门,a.ENAME 领导,s.GRADE 等级
from 
    emp a
left join 
    emp b
on 
    a.mgr = b.EMPNO
join 
    DEPT d
on  
    a.DEPTNO = d.DEPTNO
join 
    SALGRADE s
on  
    a.sal between s.LOSAL and s.HISAL
where 
    a.Sal > (SELECT avg(Sal) from emp);

– 23、列出与”SCOTT”从事相同工作的所有员工及部门名称

– SETP1 获取”SCOTT”从事的工作 

SELECT JOB FROM EMP WHERE ENAME = 'SCOTT';

– SETP2 连接人员，部门表

SELECT * FROM EMP e left join dept d on e.DEPTNO = d.DEPTNO;

– SETP3 列出与”SCOTT”从事相同工作的所有员工及部门名称

SELECT * FROM EMP e left join dept d on e.DEPTNO = d.DEPTNO where JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT');

– 24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

– SETP1 获取30部门员工和其他部门员工

SELECT * FROM EMP where DEPTNO != '30';
SELECT * FROM EMP where DEPTNO = '30';

– SETP2 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

SELECT * FROM (SELECT * FROM EMP where DEPTNO != '30') a join (SELECT * FROM EMP where DEPTNO = '30') b on a.Sal = b.Sal;

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

– SETP1 获取30部门员工的最高薪资

SELECT MAX(SAL) FROM EMP where DEPTNO = '30';

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

SELECT ENAME,SAL,DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO WHERE SAL > (SELECT MAX(SAL) FROM EMP where DEPTNO = '30');

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

– SETP1 连接员工，SALGRADE

SELECT 
   *
from 
    emp a
join 
    SALGRADE s
on  
    a.sal between s.LOSAL and s.HISAL;

– SETP2 分组并计数

SELECT 
   a.DEPTNO 部门编号,count(*) 员工数,avg(a.sal) 平均工资,avg(a.COMM) 平均服务期限
from 
    emp a
join 
    SALGRADE s
on  
    a.sal between s.LOSAL and s.HISAL
group by
    a.DEPTNO;

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

select e.ename 员工姓名,d.dname 部门名称,e.sal 工资 from emp e join dept d on e.DEPTNO = d.DEPTNO;

– 28、列出所有部门的详细信息和人数

SELECT d.*,e.num 人数 from DEPT d join (SELECT DEPTNO,count(*) num from emp group by DEPTNO) e on e.DEPTNO = d.DEPTNO;

– 29、列出各种工作的最低工资及从事此工作的雇员姓名

– SETP1 列出各种工作的最低工资

SELECT job,min(sal) minsal from emp group by job;

– SETP2 上表作为临时表与员工表连接

SELECT e.job,minsal,e.ename,e.sal FROM EMP e join (SELECT job,min(sal) minsal from emp group by job)a on e.job = a.job where sal = minsal;

– 30、列出各个部门的MANAGER(领导)的最低薪金

– SETP1 列出各个部门的MANAGER

SELECT distinct b.ename from emp a join emp b on a.MGR = b.EMPNO;

– SETP2 列出各个部门的MANAGER的最低薪金

SELECT min(sal) from emp where ename in (SELECT distinct b.ename from emp a join emp b on a.MGR = b.EMPNO);

– 31、列出所有员工的年工资,按年薪从低到高排序

SELECT ename,sal*12 from emp order by sal;

– 32、求出员工领导的薪水超过3000的员工名称与领导名称

SELECT a.ename,b.ename,b.sal from emp a join emp b on a.MGR = b.EMPNO where b.sal >=3000;

– 33、求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

– SETP1 求出部门名称中,带’S’字符的部门

SELECT deptno from dept where DNAME like '%S%';

– SETP2 求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

SELECT deptno,count(*),sum(sal) from emp where deptno in (SELECT deptno from dept where DNAME like '%S%') group by DEPTNO;

– 34、给任职日期超过30年的员工加薪10%

update emp set sal = sal*1.1 where get_year(to_date(sysdate,'yyyy-MM-dd HH24:mi:ss'),to_date(HIREDATE,'yyyy-MM-dd')) > 30;

– 方案二 表的自连接 注意思路

select sal from emp where sal not in (SELECT distinct a.sal from emp a join emp b on a.sal < b.sal);

– 方案三 MAX

SELECT MAX(sal) from emp;
赏

冷秋