MySQL：练习题
题目答案1、取得每个部门最高薪水的人员名称// 思路：先查询部门最高工资，再通过最高工资匹配对应人员SELECT ename,sal,t.deptnoFROM emp eJOIN (SELECT deptno,MAX(sal) AS maxsal FROM emp GROUP BY deptno) tON e.deptno=t.deptno AND e.sal=t.maxsal2、哪些人的薪水在部
·
表
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<t.sal)
5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
// 第一种，把分组后的平均薪水降序排序，取第一条
SELECT deptno
from emp
GROUP BY deptno
ORDER BY AVG(sal) DESC LIMIT 1
// 第二种，子查询，查出最高的平均薪水
SELECT deptno
from emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MAX(t.avgsal) FROM (SELECT deptno,AVG(sal) AS avgsal from emp GROUP BY deptno) t)
6、取得平均薪水最高的部门的部门名称
// 思路：先取得平均薪水最高的部门编号，再通过编号查部门名称
SELECT dname
FROM dept
WHERE deptno=(SELECT deptno from emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1)
7、求平均薪水的等级最低的部门的部门名称
// 思路，平均薪水最低的等级一定最低
SELECT dname
FROM dept
WHERE deptno=(SELECT deptno
FROM salgrade s
JOIN (SELECT deptno,avg(sal) AS avgsal from emp GROUP BY deptno ORDER BY avgsal LIMIT 1) t
ON avgsal BETWEEN losal AND hisal)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
SELECT ename,sal FROM emp
WHERE empno in (SELECT mgr FROM emp)
AND 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;
本社区面向用户介绍CSDN开发云部门内部产品使用和产品迭代功能，产品功能迭代和产品建议更透明和便捷
更多推荐
12
0- 0
已为社区贡献1条内容
所有评论(0)