sql语句应用实例

秒速五厘米 2022-04-05 04:18 276阅读 0赞

### 文章目录 ###

*  数据库数据准备。
 *  DQL语句练习及答案
 *  多表查询练习及答案
 *  子查询练习题及答案

# 数据库数据准备。 #

DROP DATABASE exam;
    CREATE DATABASE exam;
    
    USE exam;
    
    /*创建部门表*/
    CREATE TABLE dept(
    	deptno		INT 	PRIMARY KEY,
    	dname		VARCHAR(50),
    	loc 		VARCHAR(50)
    );
    
    /*创建雇员表*/
    CREATE TABLE emp(
    	empno		INT 	PRIMARY KEY,
    	ename		VARCHAR(50),
    	job		VARCHAR(50),
    	mgr		INT,
    	hiredate	DATE,
    	sal		DECIMAL(7,2),
    	COMM 		DECIMAL(7,2),
    	deptno		INT,
    	CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
    );
    
    /*创建工资等级表*/
    CREATE TABLE salgrade(
    	grade		INT 	PRIMARY KEY,
    	losal		INT,
    	hisal		INT
    );
    
    /*创建学生表*/
    CREATE TABLE stu(
    	sid		INT 	PRIMARY KEY,
    	sname		VARCHAR(50),
    	age		INT,
    	gander		VARCHAR(10),
    	province	VARCHAR(50),
    	tuition		INT
    );
    
    /*插入dept表数据*/
    INSERT INTO dept VALUES (10, '教研部', '北京');
    INSERT INTO dept VALUES (20, '学工部', '上海');
    INSERT INTO dept VALUES (30, '销售部', '广州');
    INSERT INTO dept VALUES (40, '财务部', '武汉');
    
    /*插入emp表数据*/
    INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
    INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
    INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
    INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
    INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
    INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
    INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
    INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
    INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
    INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
    INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
    INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
    INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
    
    
    INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
    
    
    /*插入salgrade表数据*/
    INSERT INTO salgrade VALUES (1, 7000, 12000);
    INSERT INTO salgrade VALUES (2, 12010, 14000);
    INSERT INTO salgrade VALUES (3, 14010, 20000);
    INSERT INTO salgrade VALUES (4, 20010, 30000);
    INSERT INTO salgrade VALUES (5, 30010, 99990);
    
    /*插入stu表数据*/
    INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
    INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
    INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');
    INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');
    INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');
    INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');
    INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');
    INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
    INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');
    INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');
    INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');
    INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');
    INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');
    INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
    INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
    INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
    INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');
    INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');
    INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');
    INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');
    INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');
    INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
    INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
    INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');
    INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');
    INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');
    INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
    INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');
    INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
    INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');
    INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
    INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');
    INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');
    INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
    INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');
    INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
    INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');
    
    /*
    select * from emp;
    select * from dept;
    select * from salgrade;
    */

# DQL语句练习及答案 #

/*1. 查询出部门编号为30的所有员工*/
    /*
    分析：
    1). 列：没有说明要查询的列，所以查询所有列
    2). 表：只一张表，emp
    3). 条件：部门编号为30，即deptno=30
    */
    SELECT * FROM emp WHERE deptno=30;
    /**********************************************/
    /*2. 所有销售员的姓名、编号和部门编号。*/
    /*
    分析：
    列：姓名ename、编号empno、部门编号deptno
    表：emp
    条件：所有销售员，即job='销售员'
    */
    SELECT ename,empno,deptno FROM emp WHERE job='销售员'
    /**********************************************/
    /*3. 找出奖金高于工资的员工。*/
    /*
    分析：
    列：所有列
    表：emp
    条件：奖金>工资，即comm>sal
    */
    SELECT * FROM emp WHERE comm>sal;
    /**********************************************/
    /*4. 找出奖金高于工资60%的员工。*/
    /*
    分析：
    列：所有列
    表：emp
    条件：奖金>工资*0.6，即comm>sal*0.6
    */
    SELECT * FROM emp WHERE comm>sal*0.6;
    /**********************************************/
    /*5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/
    /*
    分析：
    列：所有列
    表：emp
    条件：部门编号=10并且job为经理，和部门编号=20并且job为销售员
    */
    SELECT * FROM emp WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员');
    /**********************************************/
    /*6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/
    /*
    分析：
    列：所有列
    表：emp
    条件：deptno=10 and job='经理', depnto=20 and job='销售员', job not in ('销售员','经理') and sal>=20000
    */
    SELECT * FROM emp 
    WHERE 
      (deptno=10 AND job='经理') 
      OR (deptno=20 AND job='销售员') 
      OR job NOT IN ('经理','销售员') AND sal>=20000;
    /**********************************************/
    /*7. 有奖金的工种。*/
    /*
    列：工作(不能重复出现)
    表：emp
    条件：comm is not null
    */
    SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
    /**********************************************/
    /*8. 无奖金或奖金低于1000的员工。*/
    /*
    分析：
    列：所有列
    表：emp
    条件：comm is null 或者 comm < 1000
    */
    SELECT * FROM emp WHERE comm IS NULL OR comm < 1000;
    /**********************************************/
    /*9. 查询名字由三个字组成的员工。*/
    /*
    分析：
    列：所有
    表：emp
    条件：ename like '___'
    */
    SELECT * FROM emp WHERE ename LIKE '___'
    /**********************************************/
    /*10.查询2000年入职的员工。*/
    /*
    分析：
    列：所有
    表：emp
    条件：hiredate like '2000%'
    */
    SELECT * FROM emp WHERE hiredate LIKE '2000%';
    /**********************************************/
    /*11. 查询所有员工详细信息，用编号升序排序*/
    /*
    分析；
    列：所有
    表：emp
    条件：无
    排序：empno asc
    */
    SELECT * FROM emp ORDER BY empno ASC;
    /**********************************************/
    /*12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序*/
    /*
    分析：
    列：所有
    表：emp
    条件：无
    排序：sal desc, hiredate asc
    */
    SELECT * FROM emp ORDER BY sal DESC, hiredate ASC
    /**********************************************/
    /*13. 查询每个部门的平均工资*/
    /*
    分析：
    列：部门编号、平均工资(平均工资就是分组信息)
    表：emp
    条件：无
    分组：每个部门，即使用部门分组，平均工资，使用avg()函数
    */
    SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
    /**********************************************/
    /*14. 求出每个部门的雇员数量。*/
    /*
    分析：
    列：部门编号、人员数量（人员数量即记录数，这是分组信息）
    表：emp
    条件：无
    分组：每个部门是分组信息，人员数量，使用count()函数
    */
    SELECT deptno, COUNT(1) FROM emp GROUP BY deptno;
    /**********************************************/
    /*
    15. 查询每种工作的最高工资、最低工资、人数
    列：部门、最高工资、最低工资、人数（其中最高工资、最低工资、人数，都是分组信息）
    表：emp
    条件：无
    分组：每种工资是分组信息，最高工资使用max(sal)，最低工资使用min(sal)，人数使用count(*)
    */
    SELECT job, MAX(sal), MIN(sal), COUNT(1) FROM emp GROUP BY job;
    /**********************************************/
    /*16. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列*/
    /*
    列：工作名称、工资和(分组信息)
    表：emp
    条件：无
    分组：从事同一工作的工资和，即使用job分组
    分组条件：工资合计>50000，这是分组条件，而不是where条件
    排序：工资合计排序，即sum(sal) asc
    */
    SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC;
    
    SELECT * FROM dept;
    SELECT * FROM emp;

# 多表查询练习及答案 #

/*
    1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
    2. 列出薪金比关羽高的所有员工。
    3. 列出所有员工的姓名及其直接上级的姓名。
    4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
    5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
    6. 列出所有文员的姓名及其部门名称，部门的人数。
    7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
    8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
    9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
    10.列出与庞统从事相同工作的所有员工及部门名称。
    11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
    12.列出每个部门的员工数量、平均工资。
    16.查出年份、利润、年度增长比
    */
    SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
    
    /*
    1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
    */
    SELECT z.*,d.dname,d.loc
    FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
    WHERE z.deptno=d.deptno;
    
    SELECT z.deptno,z.cnt,d.dname,d.loc
    FROM dept d ,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z
    WHERE z.deptno=d.deptno
    
    /*
    2. 列出薪金比关羽高的所有员工。
    */
    SELECT *
    FROM emp
    WHERE sal > ALL(SELECT sal FROM emp WHERE ename='关羽')
    /*
    3. 列出所有员工的姓名及其直接上级的姓名
    */
    SELECT e.ename, m.ename 
    FROM emp e LEFT OUTER JOIN emp m ON e.mgr=m.empno
    
    /*
    4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
    */
    SELECT e.empno, e.ename, d.dname
    FROM emp e 
    INNER JOIN emp m ON e.mgr=m.empno 
    INNER JOIN dept d ON e.deptno=d.deptno 
    WHERE e.hiredate < m.hiredate;
    /*
    5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
    */
    SELECT d.dname,e.*
    FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno
    /*
    6. 列出所有文员的姓名及其部门名称，部门的人数。
    */
    SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
    
    SELECT e.ename,d.dname,z.cnt
    FROM emp e, dept d, (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z
    WHERE job='文员' AND e.deptno=d.deptno AND d.deptno=z.deptno;
    /*
    7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
    */
    SELECT job,COUNT(*)
    FROM emp
    GROUP BY job
    HAVING MIN(sal)>15000
    /*
    8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
    */
    SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno 
    
    SELECT ename dname
    FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
    WHERE d.dname='销售部'
    
    SELECT e.ename
    FROM emp e
    WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
    /*
    9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
    */
    SELECT * FROM salgrade
    SELECT e.*, d.dname,m.ename,g.grade
    FROM emp e
     LEFT OUTER JOIN dept d ON e.deptno=d.deptno
     LEFT OUTER JOIN emp m ON e.mgr=m.empno
     LEFT OUTER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
    WHERE e.sal >(SELECT AVG(sal) FROM emp)
    /*
    10.列出与庞统从事相同工作的所有员工及部门名称。
    */
    SELECT e.*, d.dname
    FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
    WHERE e.job = (SELECT job FROM emp e WHERE e.ename='庞统')
    /*
    11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
    */
    SELECT e.ename,e.sal,d.dname
    FROM emp e INNER JOIN dept d ON e.deptno=d.deptno 
    WHERE sal> ALL(SELECT sal FROM emp WHERE deptno=30) 
    /*
    12.列出每个部门的员工数量、平均工资。
    */
    SELECT  d.dname,COUNT(*) con, AVG(sal) 
    FROM emp e,dept d
    WHERE e.deptno=d.deptno
    GROUP BY dname
    /*
    16.查出年份、利润、年度增长比
    */
    CREATE TABLE tb_year
    (
     col_year INT,
     col_lirun INT
    )
    INSERT INTO tb_year VALUES(2001,100);
    INSERT INTO tb_year VALUES(2002,120);
    INSERT INTO tb_year VALUES(2003,180);
    INSERT INTO tb_year VALUES(2004,300);
    SELECT * FROM tb_year;
    DROP TABLE tb_year;
    SELECT col_year, col_lirun,

# 子查询练习题及答案 #

/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
    /*
    列：部门编号、部门名称、部门位置、部门人数(分组)
    列：dept、emp(部门人数没有员工表不行)
    条件：没有
    分组条件：人数>1
    
    部门编号、部门名称、部门位置在dept表中都有，只有部门人数需要使用emp表，使用deptno来分组得到。
    我们让dept和（emp的分组查询），这两张表进行连接查询
    */
    SELECT
    z.*,d.dname,d.loc
    FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
    WHERE z.deptno=d.deptno;
    
    /**************************************************/
    
    /*2. 列出薪金比关羽高的所有员工。*/
    /*
    列：所有
    表：emp
    条件：sal>关羽的sal，其中关羽的sal需要子查询
    */
    SELECT *
    FROM emp e
    WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')
    
    /**************************************************/
    
    /*3. 列出所有员工的姓名及其直接上级的姓名。*/
    /*
    列：员工名、领导名
    表：emp、emp
    条件：领导.empno=员工.mgr
    
    emp表中存在自身关联，即empno和mgr的关系。
    我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名，所以不能用内连接，因为曾阿牛是BOSS，没有上级，内连接是查询不到它的。
    */
    SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
    FROM emp e LEFT JOIN emp m
    ON e.mgr=m.empno;
    
    /**************************************************/
    
    /*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
    /*
    列：编号、姓名、部门名称
    表：emp、dept
    条件：hiredate < 领导.hiredate
    
    emp表需要查。部门名称在dept表中，所以也需要查。领导的hiredate需要查，这说明需要两个emp和一个dept连接查询
    即三个表连接查询
    */
    SELECT e.empno, e.ename, d.dname
    FROM emp e LEFT JOIN emp m 
    ON e.mgr=m.empno 
    LEFT JOIN dept d ON e.deptno=d.deptno
    WHERE e.hiredate<m.hiredate;
    
    /**************************************************/
    
    /*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
    /*
    列：员工表所有列、部门名称
    表：emp, dept
    要求列出没有员工的部门，这说明需要以部门表为主表使用外连接
    */
    SELECT e.*, d.dname
    FROM emp e RIGHT JOIN dept d
    ON e.deptno=d.deptno;
    
    /**************************************************/
    
    /*6. 列出所有文员的姓名及其部门名称，部门的人数。*/
    /*
    列：姓名、部门名称、部门人数
    表：emp emp dept
    条件：job=文员
    分组：emp以deptno得到部门人数
    连接：emp连接emp分组，再连接dept
    */
    SELECT e.ename, d.dname, z.cnt
    FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
    WHERE e.deptno=d.deptno AND z.deptno=d.deptno;
    
    /**************************************************/
    
    /*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
    /*
    列：工作，该工作人数
    表：emp
    分组：使用job分组
    分组条件：min(sal)>15000
    */
    SELECT job, COUNT(*)
    FROM emp e
    GROUP BY job
    HAVING MIN(sal) > 15000;
    
    /**************************************************/
    
    /*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
    /*
    列：姓名
    表：emp, dept
    条件：所在部门名称为销售部，这需要通过部门名称查询为部门编号，作为条件
    */
    SELECT e.ename
    FROM emp e
    WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
    
    /**************************************************/
    
    /*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
    /*
    列：员工所有信息(员工表)，部门名称(部门表)，上级领导(员工表)，工资等级(等级表)
    表：emp, dept, emp, salgrade
    条件：sal>平均工资，子查询
    所有员工，说明需要左外
    */
    SELECT e.*, d.dname, m.ename, s.grade
    FROM emp e 
      NATURAL LEFT JOIN dept d
      LEFT JOIN emp m ON m.empno=e.mgr
      LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
    WHERE e.sal > (SELECT AVG(sal) FROM emp);
    
    /**************************************************/
    
    /*10.列出与庞统从事相同工作的所有员工及部门名称。*/
    /*
    列：员工表所有列，部门表名称
    表：emp, dept
    条件：job=庞统的工作，需要子查询，与部门表连接得到部门名称
    */
    SELECT e.*, d.dname
    FROM emp e, dept d
    WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
    
    /**************************************************/
    
    /*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
    /*
    列：姓名、薪金、部门名称(需要连接查询)
    表：emp, dept
    条件：sal > all(30部门薪金)，需要子查询
    */
    
    SELECT e.ename, e.sal, d.dname
    FROM emp e, dept d
    WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)
    
    /**************************************************/
    
    /*12.列出在每个部门工作的员工数量、平均工资。*/
    /*
    列：部门名称, 部门员工数，部门平均工资
    表：emp, dept
    分组：deptno
    */
    SELECT d.dname, e.cnt, e.avgsal
    FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
    WHERE e.deptno=d.deptno;
    
    SELECT * FROM emp;
    SELECT * 
    FROM emp
    LIMIT 4,4;