MySQL：练习题

2024-06-09 08:57| 来源: 网络整理| 查看: 265

表 DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP (EMPNO int(4) not null , ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE DEFAULT NULL, SAL DOUBLE(7,2), COMM DOUBLE(7,2), primary key (EMPNO), DEPTNO INT(2) ) ; CREATE TABLE SALGRADE ( GRADE INT, LOSAL INT, HISAL INT ); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); commit; INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17' , 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20' , 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22' , 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02' , 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28' , 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01' , 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09' , 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17' , 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08' , 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23' , 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03' , 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23' , 1300, NULL, 10); commit; INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit; 题目

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

// 思路：先查询部门最高工资，再通过最高工资匹配对应人员 SELECT ename,sal,t.deptno FROM emp e JOIN (SELECT deptno,MAX(sal) AS maxsal FROM emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal=t.maxsal

在这里插入图片描述 2、哪些人的薪水在部门的平均薪水之上

// 思路：先查出部门平均薪水，再查薪水大于平均薪水的员工 SELECT ename,sal FROM emp e JOIN (SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal>t.avgsal

在这里插入图片描述 3、取得部门中（所有人的）平均的薪水等级

// 思路：先通过表连接查询每个人的薪水等级，再通过部门分组，查出平均等级，最后进行排序 // 92 SELECT deptno, avg(grade) FROM emp e,salgrade s WHERE sal BETWEEN s.losal AND s.hisal GROUP BY deptno ORDER BY deptno // 99 SELECT deptno, avg(grade) FROM emp e JOIN salgrade s ON sal BETWEEN s.losal AND s.hisal GROUP BY deptno ORDER BY deptno

在这里插入图片描述 4、不准用组函数（Max ），取得最高薪水（给出两种解决方案）

// 第一种，把薪水降序排序，取第一条 SELECT sal FROM emp ORDER BY sal DESC LIMIT 1 // 第二种，表的自连接。先查出比最高薪水小的，再排除 SELECT sal FROM emp WHERE sal not in(SELECT e.sal FROM emp e JOIN emp t ON e.sal(SELECT sal FROM emp WHERE ename not in (SELECT ename FROM emp WHERE empno in (SELECT mgr FROM emp)) ORDER BY sal DESC LIMIT 1)

在这里插入图片描述

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

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 0,5

在这里插入图片描述

10、取得薪水最高的第六到第十名员工

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5

在这里插入图片描述 11、取得最后入职的 5 名员工

SELECT ename,hiredate FROM emp ORDER BY hiredate DESC LIMIT 0,5

在这里插入图片描述

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

SELECT grade, count(*) FROM emp e JOIN salgrade s ON sal BETWEEN s.losal AND s.hisal GROUP BY grade ORDER BY grade

在这里插入图片描述

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

SELECT t.ename AS '员工',IFNULL(e.ename,'没有上级') AS '领导' FROM emp e RIGHT JOIN emp t ON e.empno=t.mgr

在这里插入图片描述 14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

SELECT t.empno,t.ename,d.dname FROM (SELECT t.empno,t.ename,t.deptno FROM emp e JOIN emp t ON e.empno=t.mgr and e.hiredate > t.hiredate) t JOIN dept d ON t.deptno = d.deptno

在这里插入图片描述 15、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

SELECT d.DNAME,e.* FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno

在这里插入图片描述 16、列出至少有 5 个员工的所有部门

SELECT d.DNAME,COUNT(*) FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY e.deptno HAVING COUNT(*)>=5

在这里插入图片描述 17、列出薪金比"SMITH" 多的所有员工信息

SELECT * FROM emp WHERE sal > (SELECT sal as sal FROM emp WHERE ename='SMITH')

在这里插入图片描述 18、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

SELECT ename,dname,d.count FROM (SELECT * FROM emp WHERE job='CLERK') t JOIN (SELECT d.*,t.count FROM dept d JOIN (SELECT deptno,COUNT(*) as 'count' FROM emp GROUP BY deptno) t ON d.deptno=t.deptno) d ON t.deptno=d.deptno

在这里插入图片描述 19、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.

SELECT job,COUNT(*) FROM emp GROUP BY job HAVING MIN(sal) > 1500

在这里插入图片描述 20、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

SELECT ename FROM emp WHERE deptno=(SELECT deptno from dept WHERE dname='SALES')

在这里插入图片描述 21、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

SELECT t.ename AS '员工姓名',t.dname AS '部门姓名',IFNULL(e.ename,'无') AS '上级领导',grade AS '工资等级' FROM (SELECT t1.ename,t1.dname,grade,t1.mgr FROM (SELECT e.empno,e.ename,d.dname,e.mgr,e.sal FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE sal > (SELECT avg(sal) AS avgsal FROM emp)) t1 LEFT JOIN (SELECT grade,e.* FROM emp e JOIN salgrade s ON sal BETWEEN s.losal AND s.hisal) t2 ON t1.empno=t2.empno) t LEFT JOIN emp e ON t.mgr=e.empno 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" 从事相同工作的所有员工及部门名称

select ename,sal from emp where sal in(select distinct sal from emp where deptno = 30) and deptno 30;

在这里插入图片描述

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

SELECT e.ename,e.sal FROM (SELECT ename,sal FROM emp WHERE deptno=30) t LEFT JOIN emp e ON t.sal=e.sal WHERE e.ename not in (t.ename)

在这里插入图片描述 24、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

SELECT ename,sal,dname FROM (SELECT * FROM emp WHERE sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)) t JOIN dept d ON t.deptno=d.deptno

在这里插入图片描述 25、列出在每个部门工作的员工数量, 平均工资和平均服务期限

select d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime from emp e right join dept d on e.deptno = d.deptno group by d.deptno;

在这里插入图片描述

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

SELECT ename,dname,sal FROM emp e JOIN dept d ON e.deptno = d.deptno ORDER BY dname,sal

在这里插入图片描述 27、列出所有部门的详细信息和人数

SELECT d.*,IFNULL(t.count,0) AS '人数' FROM dept d LEFT JOIN (SELECT deptno,count(*) AS count FROM emp GROUP BY deptno) t ON d.deptno=t.deptno

在这里插入图片描述 28、列出各种工作的最低工资及从事此工作的雇员姓名

SELECT t.job,minsal,ename FROM emp e JOIN (SELECT job,MIN(sal) AS minsal FROM emp GROUP BY job) t ON e.job=t.job AND sal=minsal

在这里插入图片描述

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

SELECT deptno,MIN(sal) FROM emp WHERE empno in (SELECT mgr FROM emp) GROUP BY deptno select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;

在这里插入图片描述 30、列出所有员工的年工资, 按年薪从低到高排序

SELECT ename,sal*12+IFNULL(comm,0) AS income FROM emp ORDER BY income

在这里插入图片描述

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

SELECT e.ename AS '员工姓名',t.ename AS '领导姓名' FROM emp e RIGHT JOIN (SELECT empno,ename FROM emp WHERE empno in (SELECT mgr FROM emp) AND sal>3000) t ON t.empno=e.mgr select a.ename '员工',b.ename '领导' from emp a join emp b on a.mgr = b.empno where b.sal > 3000;

在这里插入图片描述 32、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数

SELECT t.deptno,sum(sal),count(empno) FROM (SELECT * FROM dept WHERE dname like '%S%') t LEFT JOIN emp e ON t.deptno=e.deptno GROUP BY t.deptno

在这里插入图片描述 33、给任职日期超过 30 年的员工加薪 10%.

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

【本文地址】

MySQL：练习题

MySQL：练习题

今日新闻

推荐新闻