(93条消息) mysql笔试题18道_bubbleJessica的博客-CSDN博客

19.列出最低薪资大于1500的各种工作及从事此工作的全部雇员人数

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

20. 列出在部门“SALES”销售部工作的员工的姓名（假设不知道销售部的部门号）

SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO=(SELECT DEPTNO FROM dept WHERE DNAME='SALES');

21.列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，雇员的工资等

SELECT e.ENAME '员工名字',d.DNAME '部门名称',l.ENAME '上属领导',s.GRADE '等级' FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO LEFT JOIN emp l ON e.MGR=l.EMPNO JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL WHERE e.SAL >(SELECT AVG(SAL) FROM emp);

22.列出与“SCOTT”从事相同工作的所有员工及其部门名称

第一步：查询“SCOTT”从事工作

SELECT JOB FROM emp WHERE ENAME='SCOTT';

第二步：连接条件工作岗位名称且排除SCOTT本人

SELECT e.ENAME,e.JOB,d.DNAME FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE e.JOB=(SELECT JOB FROM emp WHERE ENAME='SCOTT') AND e.ENAME <> 'SCOTT';

23. 列出薪资等于部门30中员工的薪资的其他员工的姓名和薪资（正确答案就是空白）

 第一步：列出部门30中员工的薪资

SELECT DISTINCT SAL FROM emp WHERE DEPTNO=30;

 第二步：列出薪资在上面薪资范围内且部门不等于30员工信息

SELECT ENAME,SAL FROM emp WHERE SAL IN (SELECT DISTINCT SAL FROM emp WHERE DEPTNO=30) AND DEPTNO <> 30;

24.列出薪资高于部门30工作的所有员工的薪资的其他员工姓名和薪资，部门名称 

SELECT e.ENAME,e.SAL,d.DNAME FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE e.SAL >(SELECT MAX(SAL) FROM emp WHERE DEPTNO=30);

25. 列出在每个部门工作的员工数量，平均工资和平均服务期限（重点）

SELECT d.DEPTNO,COUNT(e.ENAME) '员工数量',IFNULL(AVG(e.SAL),0) '平均工资',IFNULL(AVG(TIMESTAMPDIFF(YEAR,e.HIREDATE,NOW())),0) '平均服务期限' FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY d.DEPTNO;

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

SELECT e.ENAME,e.SAL,d.DNAME FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO;

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

SELECT d.DEPTNO,d.DNAME,d.LOC,COUNT(e.ENAME) FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO GROUP BY d.DEPTNO,d.DNAME,d.LOC;

28. 列出各种工作的最低工资及从事此工作的雇员姓名

SELECT JOB,MIN(SAL) AS minSal FROM emp GROUP BY JOB;

SELECT e.ENAME,t.* FROM emp e JOIN (SELECT JOB,MIN(SAL) AS minSal FROM emp GROUP BY JOB) t ON e.job=t.JOB AND e.SAL=t.minSal;

29.列出各个部门的 MANAGER( 领导) 的最低薪金 

SELECT DEPTNO,min(sal) FROM emp WHERE job='MANAGER' GROUP BY DEPTNO;

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

SELECT ENAME,(sal+IFNULL(COMM,0))*12 AS yearsal FROM emp ORDER BY yearsal ASC;

31.求出员工领导的薪水超过3000的员工名称与领导 

SELECT a.ENAME '员工',b.ENAME '领导' FROM emp a JOIN emp b ON a.MGR=b.EMPNO WHERE b.SAL>3000;

32.求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

SELECT d.DEPTNO,d.DNAME,d.LOC,COUNT(e.ENAME),IFNULL(SUM(sal),0) AS sumsal FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO WHERE d.DNAME LIKE '%S%' GROUP BY d.DEPTNO,d.DNAME,d.LOC;

33.给任职日期超过 30 年的员工加薪 10%. 

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;