MySQL练习题员工

-- 1．列出最少有4个员工的所有部门信息。
SELECT dept.*, COUNT(*)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
GROUP BY dept.deptno
HAVING COUNT(*) >= 4

-- 2．列出薪金比“SMITH”多的所有员工。
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='smith')

-- 3．列出所有员工的姓名及其直接上级的姓名。
SELECT l.ename AS lname, r.ename AS rname
FROM emp AS l LEFT JOIN emp AS r ON l.mgr=r.empno

-- 4．列出受雇日期早于其直接上级的所有员工。  时间的比较   直接比较 a<b 表示 a的时间小于b的时间
SELECT l.*
FROM emp AS l INNER JOIN emp AS r ON l.mgr = r.empno
WHERE l.hiredate < r.hiredate

-- 5．列出 部门名称 和 这些部门的员工信息，同时列出那些没有员工的部门。
SELECT *
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno

-- 6．列出所有“CLERK”（办事员）job=clerk  的姓名及其部门名称。
SELECT ename,dname
FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno
WHERE job="clerk"

-- 7．列出最低薪金大于1500的各种工作(job)。
SELECT job, MIN(sal)
FROM emp
GROUP BY job
HAVING MIN(sal) > 1500

-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，
SELECT ename
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno
WHERE dname="sales"

-- 9．列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)

-- 10．列出与“SCOTT”从事相同工作的所有员工。
SELECT * FROM emp WHERE ename!="scott" AND job=(SELECT job FROM emp WHERE ename="scott")

-- 11．列出 薪金等于部门30中员工的薪金的所有员工 的姓名和薪金。
-- 假设30号部门的员工薪资为: 1000, 2000, 3000, 4000，在整个公司内部找到薪资等于以上数字的人
SELECT ename, sal
FROM emp  
WHERE sal=1000 OR sal=2000 OR sal=3000 OR sal=4000
-- 13．列出在每个部门工作的员工数量、平均工资、部门名称。
SELECT COUNT(emp.deptno), AVG(sal),dname
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
GROUP BY dept.dname

-- 14．列出所有员工的姓名、部门名称和工资。
SELECT ename, dname, sal
FROM emp,dept
WHERE emp.deptno=dept.deptno

-- 15．列出所有部门的详细信息和部门人数。
SELECT COUNT(emp.deptno), dept.*
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
GROUP BY dname


-- 16．列出各种工作的最低工资。
SELECT job, MIN(sal)
FROM emp
GROUP BY job

-- 17．列出各个部门的MANAGER（经理）的最低薪金。
SELECT MIN(sal),dept.dname
FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno
WHERE job = "manager"
GROUP BY dname

-- 18．列出所有员工的年工资,按年薪从低到高排序。
SELECT ename,sal*12
FROM emp
ORDER BY sal*12
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
SELECT empno, ename, sal
FROM emp
WHERE sal >= 3000
-- 20.查询出所有薪水在'ALLEN'之上的所有人员信息。
SELECT *
FROM emp
WHERE(SELECT sal FROM emp WHERE ename = "allen")
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
SELECT empno, ename, sal
FROM emp
WHERE deptno = 20 AND sal > 2000
-- 22.查询出emp表中所有的工作种类（无重复）
SELECT DISTINCT job
FROM emp

-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
SELECT *
FROM emp
WHERE comm is not NULL
-- 24.查询出薪水在800到2500之间(闭区间)所有员工的信息。(注: 使用两种方式实现and 以及 between and)
SELECT *
FROM emp
WHERE sal BETWEEN 800 AND 2500

SELECT *
FROM emp
WHERE sal >= 800 AND sal <= 2500
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
SELECT *
FROM emp
WHERE empno = 7521 OR empno = 7900 OR empno = 7782

SELECT *
FROM emp
WHERE empno IN (7521, 7900, 7782)
-- 26.查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
SELECT *
FROM emp
WHERE ename LIKE "%a%" AND sal > 1000
-- 27.查询出名字第三个字母是“M”的所有员工信息。
SELECT *
FROM emp
WHERE ename LIKE "__m%" 
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
SELECT *
FROM emp
ORDER BY sal asc, hiredate desc
-- 29.将所有员工按照名字升序排序，名字相同的按照薪水降序排序。
SELECT *
FROM emp
ORDER BY ename asc, sal desc
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
SELECT ename, hiredate, sal
FROM emp
WHERE hiredate = (SELECT MIN(hiredate)FROM emp)
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
SELECT ename, sal, IFNULL(comm,100)
FROM emp

-- 32.显示出薪水最高人的职位。
SELECT job
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp)

-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
SELECT MAX(sal), MIN(sal), dname
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
WHERE dept.deptno != 10
GROUP BY dept.deptno

-- 34.删除10号部门薪水最高的员工
DELETE FROM emp
WHERE  empno =(SELECT empno FROM 
(SELECT empno FROM  emp WHERE deptno=10 AND sal>=ALL(SELECT sal FROM emp WHERE deptno=10)) AS temp) ;
-- 35.将薪水最高的员工的薪水降30%。
UPDATE emp SET sal=sal*(1-0.3) 
WHERE empno IN (SELECT temp.empno FROM (SELECT empno FROM emp WHERE sal>=all(SELECT sal FROM emp)) AS temp);
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
SELECT ename, sal, CASE WHEN sal>3000 THEN '3级'
    WHEN sal>2000 THEN '2级'
    ELSE '1级'
    END LEVEL
FROM emp