19、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
mysql> SELECT
-> min(sal),
-> job,
-> count(*)
-> FROM
-> emp
-> GROUP BY
-> job
-> HAVING
-> min(sal) > 1500;
+----------+-----------+----------+
| min(sal) | job | count(*) |
+----------+-----------+----------+
| 3000.00 | ANALYST | 2 |
| 2450.00 | MANAGER | 3 |
| 5000.00 | PRESIDENT | 1 |
+----------+-----------+----------+
20、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
mysql> SELECT
-> ename
-> FROM
-> emp
-> WHERE
-> deptno = (
-> SELECT
-> deptno
-> FROM
-> dept
-> WHERE
-> dname = 'SALES'
-> );
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
21、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
mysql> SELECT
-> e.ename as empname, d.dname, s.grade, b.ename as leadername
-> FROM
-> emp e
-> JOIN
-> salgrade s
-> ON
-> e.sal BETWEEN s.losal AND s.hisal
-> join
-> dept d
-> on
-> d.DEPTNO = e.DEPTNO
-> left join
-> emp b
-> on
-> e.mgr = b.empno
-> WHERE
-> e.sal > (SELECT avg(sal) FROM emp);
+---------+------------+-------+------------+
| empname | dname | grade | leadername |
+---------+------------+-------+------------+
| JONES | RESEARCH | 4 | KING |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| FORD | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
+---------+------------+-------+------------+
22、列出与"SCOTT"从事相同工作的所有员工及部门名称.
mysql> SELECT
-> e.ename,
-> e.job,
-> d.dname
-> FROM
-> emp e
-> JOIN
-> dept d
-> ON
-> e.deptno = d.deptno
-> WHERE
-> e.job = (
-> SELECT
-> job
-> FROM
-> emp
-> WHERE
-> ename = 'SCOTT'
-> );
+-------+---------+----------+
| ename | job | dname |
+-------+---------+----------+
| SCOTT | ANALYST | RESEARCH |
| FORD | ANALYST | RESEARCH |
+-------+---------+----------+
23、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
mysql> SELECT
-> ename,
-> sal
-> FROM
-> emp
-> WHERE
-> sal IN (
-> SELECT DISTINCT
-> sal
-> FROM
-> emp
-> WHERE
-> deptno = 30
-> )
-> AND
-> deptno != 30;
Empty set (0.00 sec)
24、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
mysql> SELECT
-> e.ename,
-> e.sal,
-> d.dname
-> FROM
-> emp e
-> JOIN
-> dept d
-> ON
-> e.deptno = d.deptno
-> WHERE
-> sal > (
-> SELECT
-> max(sal)
-> FROM
-> emp
-> WHERE
-> deptno = 30
-> );
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+
25、列出在每个部门工作的员工数量,平均工资和平均服务期限.
mysql> SELECT
-> d.deptno,
-> count(e.ename) as totalemp,
-> ifnull(avg(e.sal), 0) as avgsal,
-> ifnull(avg((to_days(now())-to_days(hiredate))/365), 0) as avgtime
-> FROM
-> dept d
-> LEFT outer JOIN
-> emp e
-> on
-> d.deptno = e.deptno
-> group by
-> d.deptno;
+--------+----------+-------------+-------------+
| deptno | totalemp | avgsal | avgtime |
+--------+----------+-------------+-------------+
| 10 | 3 | 2916.666667 | 35.37625571 |
| 20 | 5 | 2175.000000 | 34.60383562 |
| 30 | 6 | 1566.666667 | 35.67488584 |
| 40 | 0 | 0.000000 | 0.00000000 |
+--------+----------+-------------+-------------+
to_days()函数的运用。