SQL-实例测试总结

1 练习表

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;

2 练习题目

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

# 1.取得每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
# 2.取得最高薪水人名单
select 
    e.ename,e.deptno,e.sal
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;
+-------+--------+---------+
| ename | deptno | sal     |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

哪些人的薪水在部门的平均薪水之上

# 1.部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.求部门中在平均薪水之上的人
select 
    e.ename,e.deptno,e.sal,t.avgsal
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;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avgsal      |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+

取得部门中（所有人的）平均的薪水等级

# 1.所有人的薪水等级
select e.empno,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+-------+--------+-------+
| empno | deptno | grade |
+-------+--------+-------+
|  7369 |     20 |     1 |
|  7499 |     30 |     3 |
|  7521 |     30 |     2 |
|  7566 |     20 |     4 |
|  7654 |     30 |     2 |
|  7698 |     30 |     4 |
|  7782 |     10 |     4 |
|  7788 |     20 |     4 |
|  7839 |     10 |     5 |
|  7844 |     30 |     3 |
|  7876 |     20 |     1 |
|  7900 |     30 |     1 |
|  7902 |     20 |     4 |
|  7934 |     10 |     2 |
+-------+--------+-------+
# 2.取得部门的平均薪水等级
select 
    t.deptno,avg(t.grade) as avggrade
from 
    (select e.empno,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by 
    t.deptno;
+--------+--------------+
| deptno | avggrade     |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

不准用组函数（Max ），取得最高薪水

# 方法一
select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
# 方法二
select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
# 方法三
# 1.自连接，获取除了最高薪水的其他薪水
select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+
# 2.找到最高薪水
select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

取得平均薪水最高的部门的部门编号

# 方法一
# 1.取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.排序找到最高的部门编号
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

# 方法二
# 1.取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.获取最高的平均薪水
select max(t.avgsal) as maxavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t;
+-------------+
| maxavgsal   |
+-------------+
| 2916.666667 |
+-------------+
# 3.找到最高平均薪水的部门
select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
having 
    avgsal = (select max(t.avgsal) as maxavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

取得平均薪水最高的部门的部门名称

# 1.获取部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.获取平均薪水最高的部门名称
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname order by avgsal desc limit 1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

求平均薪水的等级最低的部门的部门名称

# 1.部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno; 
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
# 2.部门平均薪水的等级
select 
    t.deptno,s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     10 |     4 |
|     20 |     4 |
|     30 |     3 |
+--------+-------+
# 3.最低平均薪水等级
select 
    s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal
order by 
    s.grade asc 
limit 1;
+-------+
| grade |
+-------+
|     3 |
+-------+
# 4.部门平均薪水等级最低的部门名称
select 
    d.dname,s.grade
from 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal
join 
    dept d 
on 
    t.deptno = d.deptno
where 
    s.grade = (select 
              s.grade
            from 
              (select deptno,avg(sal) as avgsal from emp group by deptno) t 
            join 
              salgrade s 
            on 
              t.avgsal between s.losal and s.hisal
            order by 
              s.grade asc 
            limit 1);
+-------+-------+
| dname | grade |
+-------+-------+
| SALES |     3 |
+-------+-------+

取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

# 1.取得所有非普通员工编号
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
# 2.取得普通员工的最高工资
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+
# 3.取得比普通员工的最高薪资高的领导人姓名
select ename from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

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

select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

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

select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+

取得最后入职的 5 名员工

select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

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

select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

面试题
列出所有员工及领导的姓名

select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select 
    e1.empno,e1.ename,e1.hiredate,e2.ename,e2.hiredate,d.dname
from 
    emp e1
join 
    emp e2
on
    e1.mgr = e2.empno
join 
    dept d 
on 
    e1.deptno = d.deptno
where 
    e1.hiredate < e2.hiredate;
+-------+-------+------------+-------+------------+------------+
| empno | ename | hiredate   | ename | hiredate   | dname      |
+-------+-------+------------+-------+------------+------------+
|  7369 | SMITH | 1980-12-17 | FORD  | 1981-12-03 | RESEARCH   |
|  7499 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES      |
|  7521 | WARD  | 1981-02-22 | BLAKE | 1981-05-01 | SALES      |
|  7566 | JONES | 1981-04-02 | KING  | 1981-11-17 | RESEARCH   |
|  7698 | BLAKE | 1981-05-01 | KING  | 1981-11-17 | SALES      |
|  7782 | CLARK | 1981-06-09 | KING  | 1981-11-17 | ACCOUNTING |
+-------+-------+------------+-------+------------+------------+

列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

select 
    d.dname,e.ename
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno;
+------------+--------+
| dname      | ename  |
+------------+--------+
| RESEARCH   | SMITH  |
| SALES      | ALLEN  |
| SALES      | WARD   |
| RESEARCH   | JONES  |
| SALES      | MARTIN |
| SALES      | BLAKE  |
| ACCOUNTING | CLARK  |
| RESEARCH   | SCOTT  |
| ACCOUNTING | KING   |
| SALES      | TURNER |
| RESEARCH   | ADAMS  |
| SALES      | JAMES  |
| RESEARCH   | FORD   |
| ACCOUNTING | MILLER |
| OPERATIONS | NULL   |
+------------+--------+

列出至少有 5 个员工的所有部门

select deptno from emp group by deptno having count(*) >= 5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

列出薪金比"SMITH" 多的所有员工信息

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

# 1.列出所有"CLERK"( 办事员) 的姓名及其部门名称
select 
    e.ename,d.dname
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
where 
    job = 'CLERK';
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| MILLER | ACCOUNTING |
+--------+------------+
# 2.部门人数
select deptno,count(*) as countnum from emp group by deptno;
+--------+----------+
| deptno | countnum |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
# 3.列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
select 
    e.ename,d.dname,t.countnum
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
join 
    (select deptno,count(*) as countnum from emp group by deptno) t
on 
    d.deptno = t.deptno
where 
    job = 'CLERK';
+--------+------------+----------+
| ename  | dname      | countnum |
+--------+------------+----------+
| SMITH  | RESEARCH   |        5 |
| ADAMS  | RESEARCH   |        5 |
| JAMES  | SALES      |        6 |
| MILLER | ACCOUNTING |        3 |
+--------+------------+----------+

列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+

列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

# 方法一
select 
    e.ename
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
where 
    d.dname = 'SALES';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
# 方法二
select 
    ename
from 
    emp
where
    deptno = (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

select 
    e.ename,e.deptno,e.mgr,s.grade
from 
    emp e
join 
    dept d 
on 
    e.deptno = d.deptno
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal
where 
    e.sal > (select avg(sal) from emp);
+-------+--------+------+-------+
| ename | deptno | mgr  | grade |
+-------+--------+------+-------+
| JONES |     20 | 7839 |     4 |
| BLAKE |     30 | 7839 |     4 |
| CLARK |     10 | 7839 |     4 |
| SCOTT |     20 | 7566 |     4 |
| KING  |     10 | NULL |     5 |
| FORD  |     20 | 7566 |     4 |
+-------+--------+------+-------+

列出与"SCOTT" 从事相同工作的所有员工及部门名称

select 
    e.ename,d.dname
from 
    emp e
join 
    dept d
on 
    e.deptno = d.deptno
where 
    e.job = (select job from emp where ename = 'SCOTT') and e.ename <> 'SCOTT';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+

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

# 1.部门30中员工的薪金
select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
# 2.薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
select 
    ename,sal 
from 
    emp 
where 
    sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
    
Empty set (0.01 sec)

列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

# 1.部门30中员工的最高薪金
select max(sal) as maxsal from emp where deptno = 30;
+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+
# 2.薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
select 
    e.ename,e.sal,d.dname
from 
    emp e 
join 
    dept d 
on 
    e.deptno = d.deptno
where 
    e.sal > (select max(sal) as maxsal from emp where deptno = 30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

列出在每个部门工作的员工数量, 平均工资和平均服务期限

select 
    d.deptno,count(e.ename) as countnum,ifnull(avg(e.sal),0) as avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno
group by 
    d.deptno;
+--------+----------+-------------+---------+
| deptno | countnum | avgsal      | avgtime |
+--------+----------+-------------+---------+
|     10 |        3 | 2916.666667 | 40.0000 |
|     20 |        5 | 2175.000000 | 37.8000 |
|     30 |        6 | 1566.666667 | 40.0000 |
|     40 |        0 |    0.000000 |  0.0000 |
+--------+----------+-------------+---------+

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

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+

列出所有部门的详细信息和人数

select 
    d.*,count(e.ename) as countnum 
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno
group by
    d.deptno,d.dname,d.loc;
+--------+------------+----------+----------+
| DEPTNO | DNAME      | LOC      | countnum |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |        3 |
|     20 | RESEARCH   | DALLAS   |        5 |
|     30 | SALES      | CHICAGO  |        6 |
|     40 | OPERATIONS | BOSTON   |        0 |
+--------+------------+----------+----------+

列出各种工作的最低工资及从事此工作的雇员姓名

# 1.各种工作的最低工资
select job,min(sal) as minsal from emp group by job;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
# 2.各种工作的最低工资及从事此工作的雇员姓名
select 
    e.ename,t.job,t.minsal 
from 
    emp e 
join 
    (select job,min(sal) as minsal from emp group by job) t 
on 
    e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

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

select 
    deptno,min(sal) as minsal
from 
    emp e
where 
    job = 'MANAGER'
group by 
    deptno;
+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+

列出所有员工的年工资, 按年薪从低到高排序

select 
    ename,(sal + ifnull(comm,0))*12 as yearsal
from 
    emp
order by
    yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

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

select 
    e1.ename,e2.ename
from 
    emp e1
left join 
    emp e2
on 
    e1.mgr = e2.empno
where
    e2.sal > 3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+

求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

select 
    d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.empno) as countnum
from 
    emp e
right join 
    dept d
on 
    e.deptno = d.deptno
where 
    d.dname like '%S%'
group by
    d.deptno;
+------------+----------+----------+
| dname      | sumsal   | countnum |
+------------+----------+----------+
| RESEARCH   | 10875.00 |        5 |
| SALES      |  9400.00 |        6 |
| OPERATIONS |     0.00 |        0 |
+------------+----------+----------+

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,383评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,522评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,852评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,621评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,741评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,929评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,076评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,803评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,265评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,582评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,716评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,395评论 4赞 333
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,039评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,798评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,027评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,488评论 2赞 361
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,612评论 2赞 350

SQL-实例测试总结

1 练习表

2 练习题目

推荐阅读更多精彩内容