用到的表
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;
emp员工表:
dept部门表:
salgrade工资等级表:
1.取得每个部门最高薪水的人员名称
SELECT e.DEPTNO,e.ENAME,e.SAL
from emp e
JOIN
(select max(e.SAL) as maxsal,e.DEPTNO
from emp as e
GROUP BY DEPTNO) as t
on
t.DEPTNO = e.DEPTNO
where e.SAL=t.maxsal
ORDER BY e.DEPTNO
思路:第一步先查出每个部门的最高薪水,作为中间表t,然后查询员工姓名,员工部门号,薪水,emp e表join t表,连接条件是t.DEPTNO = e.DEPTNO ,筛选条件e.SAL=t.maxsal。
2.哪些人的薪水在部门平均薪水之上
SELECT e.DEPTNO,e.ENAME,e.SAL,t.avgsal
from emp e
JOIN
(select AVG(e.SAL) as avgsal,e.DEPTNO
from emp as e
GROUP BY DEPTNO) as t
on
t.DEPTNO = e.DEPTNO
where e.SAL>t.avgsal
ORDER BY e.SAL
思路:第一步先查出每个部门的平均薪水,作为中间表t,然后查询员工姓名,员工部门号,薪水,部门平均薪水,emp e表join t表,连接条件是t.DEPTNO = e.DEPTNO ,筛选条件e.SAL>t.avgsal。
3.取得部门中(所有人的)平均薪水等级
题目意思:取得每个部门中所有人的平均薪水等级
SELECT avg(t.egrade),t.DEPTNO
from
(SELECT g.GRADE as egrade,e.SAL,e.DEPTNO
from salgrade as g
JOIN emp as e
on e.SAL BETWEEN g.LOSAL AND g.HISAL) as t
GROUP BY t.DEPTNO
思路:先求出每个人的工资等级,再按部门分组求出平均等级。
4.不准用组函数(MAX),取得最高薪水(给出两种解决方案)
第一种方法
select SAL FROM emp ORDER BY SAL DESC LIMIT 1;
第二种方法
select sal from emp where sal not in(select
distinct a.sal
from
emp a
join
emp b
on
a.sal < b.sal);
5.取得平均薪水最高的部门的部门编号
select t.DEPTNO
from
(select avg(sal) as avgsal,e.DEPTNO from emp as e GROUP BY e.DEPTNO) as t
ORDER BY avgsal desc LIMIT 1
思路:先按部门分组查出各组的平均成绩,作为中间表,然后该表按照平均成绩降序取最高的平均成绩的编号。
6.取得平均薪水最高的部门的部门名称
select d.DNAME
FROM dept d
join
(select t.DEPTNO
from
(select avg(sal) as avgsal,e.DEPTNO from emp as e GROUP BY e.DEPTNO) as t
ORDER BY avgsal desc LIMIT 1)as s
on d.DEPTNO=s.DEPTNO
思路:在5的基础上,联合dept表,连接条件d.DEPTNO=s.DEPTNO查出部门名称。
7.求平均薪水的等级最低的部门的部门名称
select DNAME from dept as d
join (select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1)as t
where d.DEPTNO=t.DEPTNO
思路:
第一步,求平均薪水最低的部门ID
select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1;
第二步,求平均薪水的等级最低的部门的部门名称
select DNAME from dept as d
join (select avg(sal) as avgsal ,DEPTNO from emp group by DEPTNO ORDER BY avgsal asc limit 1)as t
where d.DEPTNO=t.DEPTNO
8.取得比普通员工(员工代码没有在mgr上出现的)的最高薪水还要高的经理人姓名
select e.ENAME from emp as e
where e.EMPNO in (select DISTINCT mgr from emp where mgr is not NULL)
and e.SAL>(select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL)
)
思路:
第一步,查询出非普通员工的员工号
select DISTINCT mgr from emp where mgr is not NULL
第二步,查出普通员工的最高工资
select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL);
第三步,查出比普通员工最高工资高的经理人姓名
select e.ENAME from emp as e
where e.EMPNO in (select DISTINCT mgr from emp where mgr is not NULL)
and e.SAL>(select max(sal) as maxsal
from emp as e
where e.EMPNO not in(select DISTINCT mgr from emp where mgr is not NULL)
)
9.取得薪水最高的前五名员工
SELECT SAL ,EMPNO,ENAME from emp ORDER BY SAL DESC LIMIT 0,5;
10.取得薪水最高的第六到第十名员工
SELECT SAL ,EMPNO,ENAME from emp ORDER BY SAL DESC LIMIT 5,5;
11.取得最后入职的5名员工
SELECT * FROM `emp` ORDER BY HIREDATE desc LIMIT 5;
12.取得每个薪水等级有多少员工
select s.GRADE ,count(*)as num from emp as e
join salgrade as s
on e.SAL BETWEEN s.LOSAL and s.HISAL
GROUP BY GRADE
思路:先查出每个员工的工资等级,然后按工资等级分组
14.列出所有员工及领导的名字
select e1.ENAME as yuangong,e2.ENAME as lingdao from emp as e1
left join emp as e2
on e1.mgr=e2.EMPNO
15.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
17.列出至少有5个员工的所有部门
18.列出薪水比“SMITH”多的所有员工信息
19.列出所有“CLERK”(办事员)的姓名及其部门名称,部门人数
20.列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
21.列出在部门“SALES”<销售部>工作的员工的姓名,假定不知道销售部门的部门编号
22.列出薪金高于公司平均薪金的所有员工,所在部门、上级领导、雇员的工资等级
23.列出与“SCOTT”从事相同工作的所有员工及部门名称
24.列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
26.列出在每个部门工作的员工数量、平均工资和平均服务期限
27.列出所有员工的姓名、部门名称和工资
28.列出所有部门的详细信息和人数
29.列出各种工作的最低工资及从事此工作的雇员姓名
30.列出各个部门MANAGER的最低薪金
31.列出所有员工的年工资,按年薪从低到高排序
32.求出员工领导的薪水超过3000的员工名称和领导名称
33.求部门名称中带“S”字符的部门员工的工资合计、部门人数
34.给任职日期超过30年的员工加薪10%