文末是sql脚本，执行建表
1、取得每个部门最高薪水的人员名称
求每个部门部门号和最高薪水
SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`;
最终sql语句
SELECT e.`ENAME`,t.* FROM
(SELECT `DEPTNO`,MAX(`SAL`) maxsal FROM `emp` GROUP BY `DEPTNO`) t,emp e
WHERE t.`DEPTNO`=e.`DEPTNO` AND t.maxsal=e.`SAL`;
2、哪些人的薪水在部门的平均薪水之上
求部门的平均薪水
SELECT `DEPTNO`,AVG(`SAL`) avgsal FROM `emp` GROUP BY `DEPTNO`;
最终sql语句
SELECT e.`ENAME`, e.`SAL` ,t.*
FROM emp e,(SELECT `DEPTNO`,AVG(`SAL`) avgsal FROM `emp` GROUP BY `DEPTNO`) t
WHERE e.`DEPTNO`=t.`DEPTNO` AND e.sal>t.avgsal;
3、取得部门中（所有人的）平均的薪水等级
SELECT e.`DEPTNO`,AVG(s.`GRADE`) FROM `emp` e,`salgrade` s
WHERE e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`
GROUP BY e.`DEPTNO`;
4、不准用组函数（Max ），取得最高薪水
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,1;
5、取得平均薪水最高的部门的部门编号
SELECT `DEPTNO` ,AVG(`SAL`) avgsal FROM emp GROUP BY `DEPTNO` ORDER BY avgsal DESC LIMIT 0,1;
6、取得平均薪水最高的部门的部门名称
select d.`DNAME`, t.*
from dept d,(select `DEPTNO` ,avg(`SAL`) avgsal from emp group by `DEPTNO` order by avgsal desc limit 0,1) t
where d.`DEPTNO`=t.deptno;
7、求平均薪水的等级最低的部门的部门名称
select s.`GRADE`,t.* ,d.`DNAME`
from `salgrade` s,(`MGR`SELECT `DEPTNO` ,AVG(`SAL`) avgsal FROM emp GROUP BY `DEPTNO`) t,`dept` d
where t.avgsal between s.`LOSAL` and s.`HISAL` AND d.`DEPTNO`=t.`DEPTNO`
limit 0,1;
8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
求领导id
SELECT DISTINCT `MGR` FROM emp WHERE `MGR` IS NOT NULL
普通员工的最高薪水
SELECT MAX(e.`SAL` ) maxsal
FROM emp e,(SELECT DISTINCT `MGR` FROM emp WHERE `MGR` IS NOT NULL) t
WHERE e.`EMPNO` NOT IN(t.`MGR`);
最终sql语句
SELECT a.`ENAME`,a.sal
FROM emp a
WHERE a.sal>(SELECT MAX(e.`SAL` ) maxsal
FROM emp e
WHERE e.`EMPNO` NOT IN (SELECT DISTINCT `MGR` FROM emp WHERE `MGR` IS NOT NULL));
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 e.`EMPNO`, s.`GRADE` from emp e,`salgrade` s where e.sal between s.`LOSAL` and s.`HISAL`
最终sql语句
select count(*),b.`GRADE` from emp a,
(SELECT e.`EMPNO`, s.`GRADE` FROM emp e,`salgrade` s WHERE e.sal BETWEEN s.`LOSAL` AND s.`HISAL`) b
where a.`EMPNO`=b.`EMPNO`
group by b.`GRADE`;
14、列出所有员工及领导的姓名
select e.`ENAME`,b.`ENAME` from emp e,emp b where e.mgr=b.`EMPNO`;
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
SELECT e.`EMPNO`,e.`ENAME`,d.`DNAME` FROM emp e,emp b ,dept d
WHERE e.mgr=b.`EMPNO` AND e.`HIREDATE`<b.`HIREDATE` AND e.`DEPTNO`=d.`DEPTNO`;
16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
SELECT d.`DNAME`,e.`EMPNO`,e.`ENAME`,e.`JOB`,e.`MGR`,e.`HIREDATE`,e.`SAL`,e.`COMM`
FROM emp e
RIGHT JOIN
dept d
ON e.`DEPTNO`=d.`DEPTNO`;
17、列出至少有 5 个员工的所有部门
SELECT d.`DNAME`,d.`DEPTNO`,COUNT(*) num FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO`
GROUP BY e.`DEPTNO` HAVING num>=5;
18、列出薪金比"SMITH" 多的所有员工信息
select `EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM` from emp
where sal>
(select sal from emp where ename='SMITH')
19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
查出"CLERK"的姓名，部门号，部门名
SELECT e.`ENAME`,d.`DEPTNO`,d.`DNAME` FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO` AND e.job='CLERK'
查出部门人数，部门号
SELECT COUNT(*) num,`DEPTNO` FROM `emp` GROUP BY `DEPTNO`
最终sql
SELECT a.*,b.num FROM
(SELECT e.`ENAME`,d.`DEPTNO`,d.`DNAME` FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO` AND e.job='CLERK') a,
(SELECT COUNT(*) num,`DEPTNO` FROM `emp` GROUP BY `DEPTNO`) b
WHERE a.`DEPTNO`=b.`DEPTNO`;
20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
SELECT COUNT(*),`JOB` FROM emp GROUP BY job HAVING MIN(sal)>1500;
21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.
找出"SALES"的部门编号
SELECT `DEPTNO` FROM dept WHERE `DNAME`='SALES'
最终sql
select `ENAME` from emp,
(SELECT `DEPTNO` FROM dept WHERE `DNAME`='SALES') t
where emp.`DEPTNO`=t.`DEPTNO`;
22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
找出平均薪金
SELECT AVG(sal) FROM emp
最终sql
#要找所有员工，但king没有上级领导，自查询必须左外连接
SELECT e.`ENAME`,d.`DNAME`,a.`ENAME`,s.`GRADE`
FROM emp e JOIN dept d
ON e.`DEPTNO`=d.`DEPTNO`
LEFT JOIN emp a
ON e.`MGR`=a.`EMPNO`
JOIN `salgrade` s
ON e.`SAL` BETWEEN s.`LOSAL` AND s.`HISAL`
WHERE e.sal>(SELECT AVG(sal) FROM emp)
23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
找出"SCOTT"的工作
SELECT job FROM emp WHERE ename='SCOTT'
最终sql
SELECT e.ename,d.dname FROM emp e,dept d
WHERE e.`DEPTNO`=d.`DEPTNO`
AND e.`JOB`=(SELECT job FROM emp WHERE ename='SCOTT')
AND e.ename !='SCOTT';
24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
找出部门 30 中员工的薪金
select sal from emp where `DEPTNO`=30
最终sql
select ename,sal
from emp
where sal in (select sal from emp where `DEPTNO`=30)
and `DEPTNO` !=30
25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
找出部门 30 中的最高薪金
SELECT MAX(sal) FROM emp WHERE `DEPTNO`=30
最终sql
SELECT e.`ENAME`,e.sal,d.dname
FROM emp e,dept d
WHERE e.`DEPTNO`=d.`DEPTNO` AND e.sal>(SELECT MAX(sal) FROM emp WHERE `DEPTNO`=30)
26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
在mysql当中怎么计算两个日期的“年差”，差了多少年？
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
timestampdiff(YEAR, hiredate, now())
间隔类型：
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年
SELECT dept.`DEPTNO`,COUNT(ename),AVG(sal),AVG(TIMESTAMPDIFF(YEAR,`HIREDATE`,NOW())) YEAR
FROM dept LEFT JOIN emp
ON dept.`DEPTNO`=emp.`DEPTNO`
GROUP BY dept.`DEPTNO`
27、 列出所有员工的姓名、部门名称和工资。
SELECT e.ename,d.dname,e.sal
FROM emp e,dept d
WHERE e.`DEPTNO`=d.`DEPTNO`
28、列出所有部门的详细信息和人数
SELECT d.*,COUNT(e.ename)
FROM dept d
LEFT JOIN emp e
ON d.`DEPTNO`=e.`DEPTNO`
GROUP BY d.`DEPTNO`
29、列出各种工作的最低工资及从事此工作的雇员姓名
select e.ename,e.sal,e.job
from emp e,(SELECT MIN(sal) minsal,job FROM emp GROUP BY job) t
where e.sal=t.minsal and e.job=t.job
30、列出各个部门的 MANAGER( 领导) 的最低薪金
SELECT MIN(sal), ename,`DEPTNO`
FROM emp
WHERE `JOB`='MANAGER'
GROUP BY `DEPTNO`
31、列出所有员工的 年工资, 按 年薪从低到高排序
SELECT ename,(sal+IFNULL(comm,0))*12 yearsal FROM emp ORDER BY yearsal
32、求出员工领导的薪水超过3000的员工名称与领导
SELECT e.ename '员工',a.ename '领导'
FROM emp e,emp a
WHERE e.mgr=a.`EMPNO`
AND a.sal>3000
33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
找出 带’S’字符的部门号，部门名
SELECT `DEPTNO`,`DNAME` FROM dept WHERE `DNAME` LIKE '%s%'
最终sql，没有员工的部门也要算上，用左外连接
SELECT IFNULL(SUM(sal),0),COUNT(ename),t.dname
FROM (SELECT `DEPTNO`,`DNAME` FROM dept WHERE `DNAME` LIKE '%s%') t
LEFT JOIN emp e
ON t.`DEPTNO`=e.`DEPTNO`
GROUP BY e.`DEPTNO`
34、给任职日期超过 30 年的员工加薪 10%.
UPDATE emp SET sal = sal * 1.1 WHERE TIMESTAMPDIFF(YEAR, hiredate, NOW()) > 30;
sql脚本
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;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
