表关系图：
# 创建表 create table employee ( empno int NOT NULL AUTO_INCREMENT, ename varchar(20) NULL， job varchar(20) NULL， mgr int NULL, hiredate date NULL， sal decimal（10,2） NULL, comm decimal(10,2) NULL， deptno int NULL, primary key (empnos) )engine=InnoDB;
例题：
1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
mysql> SELECT -> d.deptno, -> d.dname, -> d.loc, -> COUNT(e.empno) AS dept_count -> FROM -> department d -> JOIN -> employee e ON d.deptno = e.deptno -> GROUP BY -> d.deptno, d.dname, d.loc -> HAVING -> COUNT(e.empno) > 0; +--------+-----------+--------+------------+ | deptno | dname | loc | dept_count | +--------+-----------+--------+------------+ | 20 | 学工部 | 上海 | 5 | | 30 | 销售部 | 广州 | 6 | | 10 | 教研部 | 北京 | 3 | +--------+-----------+--------+------------+ 3 rows in set (0.01 sec)
2.列出所有员工的姓名及其直接上级的姓名。
mysql> SELECT -> e1.ename AS employee_name, -> e2.ename AS manager_name -> FROM -> employee e1 -> LEFT JOIN -> employee e2 ON e1.mgr = e2.empno; +---------------+--------------+ | employee_name | manager_name | +---------------+--------------+ | 甘宁 | 庞统 | | 黛琦丝 | 关羽 | | 殷天正 | 关羽 | | 刘备 | 曾阿牛 | | 谢逊 | 关羽 | | 关羽 | 曾阿牛 | | 张飞 | 曾阿牛 | | 诸葛亮 | 刘备 | | 曾阿牛 | NULL | | 韦一笑 | 关羽 | | 周泰 | 诸葛亮 | | 程普 | 关羽 | | 庞统 | 刘备 | | 黄盖 | 张飞 | +---------------+--------------+ 14 rows in set (0.01 sec)
3.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
mysql> SELECT -> e1.empno, -> e1.ename, -> d.dname -> FROM -> employee e1 -> JOIN -> employee e2 ON e1.mgr = e2.empno -> JOIN -> department d ON e1.deptno = d.deptno -> WHERE -> e1.hiredate < e2.hiredate; +-------+-----------+-----------+ | empno | ename | dname | +-------+-----------+-----------+ | 1001 | 甘宁 | 学工部 | | 1002 | 黛琦丝 | 销售部 | | 1003 | 殷天正 | 销售部 | | 1004 | 刘备 | 学工部 | | 1006 | 关羽 | 销售部 | | 1007 | 张飞 | 教研部 | +-------+-----------+-----------+ 6 rows in set (0.00 sec)
4.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
mysql> SELECT -> d.deptno, -> d.dname, -> e.empno, -> e.ename, -> e.job, -> e.mgr, -> e.hiredate, -> e.sal, -> e.comm -> FROM -> department d -> LEFT JOIN -> employee e ON d.deptno = e.deptno -> ORDER BY -> d.deptno, e.empno; +--------+-----------+-------+-----------+-----------+------+------------+----------+----------+ | deptno | dname | empno | ename | job | mgr | hiredate | sal | comm | +--------+-----------+-------+-----------+-----------+------+------------+----------+----------+ | 10 | 教研部 | 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500.00 | NULL | | 10 | 教研部 | 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000.00 | NULL | | 10 | 教研部 | 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000.00 | NULL | | 20 | 学工部 | 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000.00 | NULL | | 20 | 学工部 | 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750.00 | NULL | | 20 | 学工部 | 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000.00 | NULL | | 20 | 学工部 | 1011 | 周泰 | 文员 | 1008 | 2007-05-23 | 11000.00 | NULL | | 20 | 学工部 | 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000.00 | NULL | | 30 | 销售部 | 1002 | 黛琦丝 | 销售员 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | | 30 | 销售部 | 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | | 30 | 销售部 | 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | | 30 | 销售部 | 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500.00 | NULL | | 30 | 销售部 | 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000.00 | 0.00 | | 30 | 销售部 | 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500.00 | NULL | | 40 | 财务部 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +--------+-----------+-------+-----------+-----------+------+------------+----------+----------+ 15 rows in set (0.00 sec)
5.列出最低薪金大于15000的各种工作及从事此工作的员工人数。
mysql> SELECT job,count(*) as 人数 —> from employee —> where sal > 15000 or sal+comm > 15000 —> group by job; +-----------+--------+ | job | 人数 | +-----------+--------+ | 销售员 | 3 | | 经理 | 3 | | 分析师 | 2 | | 董事长 | 1 | +-----------+--------+ 4 rows in set (0.01 sec)
6.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
mysql> SELECT -> e.ename -> FROM -> employee e -> JOIN -> department d ON e.deptno = d.deptno -> WHERE -> d.dname = '销售部'; +-----------+ | ename | +-----------+ | 黛琦丝 | | 殷天正 | | 谢逊 | | 关羽 | | 韦一笑 | | 程普 | +-----------+ 6 rows in set (0.00 sec)
7.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
mysql> SELECT -> e.empno, -> e.ename, -> d.dname, -> e.mgr, -> e.sal, -> sg.grade -> FROM -> employee e -> JOIN -> department d ON e.deptno = d.deptno -> LEFT JOIN -> salarygrade sg ON e.sal BETWEEN sg.losal AND sg.hisal -> WHERE -> e.sal > (SELECT avg(sal)+sum(comm)/14 FROM employee) -> ORDER BY -> e.empno; +-------+-----------+-----------+------+----------+-------+ | empno | ename | dname | mgr | sal | grade | +-------+-----------+-----------+------+----------+-------+ | 1004 | 刘备 | 学工部 | 1009 | 29750.00 | 4 | | 1006 | 关羽 | 销售部 | 1009 | 28500.00 | 4 | | 1007 | 张飞 | 教研部 | 1009 | 24500.00 | 4 | | 1008 | 诸葛亮 | 学工部 | 1004 | 30000.00 | 4 | | 1009 | 曾阿牛 | 教研部 | NULL | 50000.00 | 5 | | 1013 | 庞统 | 学工部 | 1004 | 30000.00 | 4 | +-------+-----------+-----------+------+----------+-------+ 6 rows in set (0.02 sec)
8.列出与庞统从事相同工作的所有员工及部门名称。
mysql> SELECT a.ename, b.dname -> FROM employee a -> JOIN department b ON a.deptno = b.deptno -> WHERE a.job = (SELECT job FROM employee WHERE ename = '庞统'); +-----------+-----------+ | ename | dname | +-----------+-----------+ | 诸葛亮 | 学工部 | | 庞统 | 学工部 | +-----------+-----------+ 2 rows in set (0.00 sec)
9.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
mysql> select e.ename,e.sal,d.dname from employee e -> inner join department d -> on e.deptno = d.deptno where sal > -> (select sum(sal)+sum(comm) from employee where deptno = 30); Empty set (0.01 sec)
10.查出年份、利润、年度增长比。
mysql> SELECT current_year.year, current_year.zz, -> CASE WHEN previous_year.zz = 0 THEN NULL -> -> ELSE CONCAT(ROUND(( (current_year.zz - previous_year.zz) / previous_year.zz )*100 ,2 ),'%') -> -> END AS growth_rate -> -> FROM -> -> annualprofit current_year -> -> LEFT JOIN -> -> annualprofit previous_year ON current_year.year = previous_year.year + 1 -> -> ORDER BY current_year.year; +------+---------+-------------+ | year | zz | growth_rate | +------+---------+-------------+ | 2010 | 100.00 | NULL | | 2011 | 150.00 | 50% | | 2012 | 250.00 | 66.67% | | 2013 | 800.00 | 220% | | 2014 | 1000.00 | 25% | +------+---------+-------------+ 5 rows in set (0.01 sec)
