12、取得每个薪水等级有多少员工
第一步:找出每个员工的薪水等级
mysql> SELECT
-> e.ename,
-> s.grade
-> FROM
-> emp e
-> JOIN
-> salgrade s
-> ON
-> e.sal BETWEEN s.losal AND s.hisal;
+--------+-------+
| ename | grade |
+--------+-------+
| SMITH | 1 |
| JAMES | 1 |
| ADAMS | 1 |
| WARD | 2 |
| MARTIN | 2 |
| MILLER | 2 |
| TURNER | 3 |
| ALLEN | 3 |
| JONES | 4 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| FORD | 4 |
| KING | 5 |
+--------+-------+
第二步:在以上结果的基础上,按照grade分组计数
mysql> 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 |
+-------+----------+
13、列出所有员工及领导的姓名
emp a 员工表
emp b 领导表
进行表的自连接
mysql> SELECT
-> a.ename AS empname,
-> b.ename AS leadername
-> FROM
-> emp a
-> LEFT OUTER JOIN
-> emp b
-> ON
-> a.mgr = b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| 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 |
+---------+------------+
14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
mysql> SELECT
-> a.empno AS empno,
-> a.ename empname,
-> a.hiredate,
-> b.empno AS leaderno,
-> b.ename AS leadername,
-> b.hiredate AS leaderhiredate,
-> d.dname
-> FROM
-> emp a
-> JOIN
-> emp b
-> ON
-> a.mgr = b.EMPNO
-> JOIN
-> dept d
-> ON
-> d.deptno = a.deptno
-> WHERE
-> a.HIREDATE < b.HIREDATE;
+-------+---------+------------+----------+------------+----------------+------------+
| empno | empname | hiredate | leaderno | leadername | leaderhiredate | dname |
+-------+---------+------------+----------+------------+----------------+------------+
| 7782 | CLARK | 1981-06-09 | 7839 | KING | 1981-11-17 | ACCOUNTING |
| 7369 | SMITH | 1980-12-17 | 7902 | FORD | 1981-12-03 | RESEARCH |
| 7566 | JONES | 1981-04-02 | 7839 | KING | 1981-11-17 | RESEARCH |
| 7876 | ADAMS | 1981-05-23 | 7788 | SCOTT | 1987-04-19 | RESEARCH |
| 7499 | ALLEN | 1981-02-20 | 7698 | BLAKE | 1981-05-01 | SALES |
| 7521 | WARD | 1981-02-22 | 7698 | BLAKE | 1981-05-01 | SALES |
| 7698 | BLAKE | 1981-05-01 | 7839 | KING | 1981-11-17 | SALES |
+-------+---------+------------+----------+------------+----------------+------------+
15、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
主要考察外连接。
mysql> SELECT
-> d.dname,
-> e.*
-> FROM
-> dept d
-> LEFT JOIN
-> emp e
-> ON
-> d.deptno = e.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1981-05-23 | 1100.00 | NULL | 20 |
| RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| SALES | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
16、列出至少有5个员工的所有部门
mysql> SELECT
-> deptno,
-> count(ename) AS empcount
-> FROM
-> emp
-> GROUP BY
-> deptno
-> HAVING
-> count(ename) >= 5;
+--------+----------+
| deptno | empcount |
+--------+----------+
| 20 | 5 |
| 30 | 6 |
+--------+----------+
17、列出薪金比"SMITH"多的所有员工信息.
mysql> SELECT
-> *
-> FROM
-> emp
-> WHERE
-> sal > (
-> SELECT
-> sal
-> FROM
-> emp
-> WHERE
-> ename = upper('smith')
-> );
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1981-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
第一步:取得所有clerk的姓名及其部门名称
mysql> SELECT
-> e.ename,
-> d.dname
-> FROM
-> dept d
-> JOIN
-> emp e
-> ON
-> d.deptno = e.deptno
-> WHERE
-> e.job = 'clerk';
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| ADAMS | RESEARCH |
| JAMES | SALES |
+--------+------------+
第二步:取得每个部门的员工数量
mysql> SELECT
-> deptno,
-> count(deptno) AS totalemp
-> FROM
-> emp
-> GROUP BY
-> deptno;
+--------+----------+
| deptno | totalemp |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+----------+
第三步:联合第一步和第二步的结果
mysql> SELECT
-> e.ename,
-> d.dname,
-> t.totalemp
-> FROM
-> dept d
-> JOIN
-> emp e
-> ON
-> d.deptno = e.deptno
-> JOIN (
-> SELECT
-> deptno,
-> count(deptno) AS totalemp
-> FROM
-> emp
-> GROUP BY
-> deptno
-> ) t
-> ON
-> t.deptno = e.deptno
-> WHERE
-> e.job = 'clerk';
+--------+------------+----------+
| ename | dname | totalemp |
+--------+------------+----------+
| MILLER | ACCOUNTING | 3 |
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
+--------+------------+----------+