mysql训练题

数据表

部门信息表

• dept

字段名	中文描述	类型
DEPTNO	部门编号	INT(2)
DNAME	部门名称	VARCHAR(14)
LOC	位置	VARCHAR(13)

员工信息表

• emp

• DEPTNO 字段是外键，DEPTNO 的值来源于dept 表的主键，起到了约束的 作用

字段名	描述	类型
EMPNO	员工编号	INT(4)
ENAME	员工姓名	VARCHAR(10)
JOB	工作岗位	VARCHAR(9)
MGR	上级经理	INT(4)
HIREDATE	入职日期	DATE
SAL	薪水	DOUBLE(7,2)
COMM	津贴	DOUBLE(7,2)
DEPTNO	部门编号（外键）	INT(2)

薪水等级表

• salgrade

字段名	描述	类型
GRADE	等级	INT
LOSAL	最低薪水	INT
HISAL	最高薪水	INT

案例

1.每个部门最高薪水人名

• (1)取得每个部门的最高薪水，按照部门分组求最大值

SELECT DEPTNO ,MAX(SAL) MAXSAL
FROM EMP 
GROUP BY DEPTNO;

• (2)把上面的查询结果当做一个临时表T,和EMP表进行连接查询,连接的条件(E.DEPTNO = T.DEPTNO AND E.SAL = T.MAXSAL)

SELECT T.DEPTNO,T.MAXSAL ,E.EMPNO,E.ENAME
FROM EMP E
INNER JOIN (SELECT DEPTNO ,MAX(SAL) MAXSAL
            FROM EMP 
            GROUP BY DEPTNO) T ON (E.DEPTNO = T.DEPTNO AND T.MAXSAL = E.SAL)
ORDER BY T.DEPTNO;

• (3)校验查询结果

SELECT *
FROM EMP 
ORDER BY DEPTNO ASC,SAL DESC;

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

• (1)按照部门编号分组,查询平均薪水

SELECT DEPTNO,AVG(SAL) AVGSAL
FROM EMP E
GROUP BY DEPTNO ;

• (2)把上面的查询结果当做一个临时表T,和EMP表进行连接查询,连接的条件(E.DEPTNO = T.DEPTNO AND E.SAL > T.MAXSAL)

SELECT E.EMPNO,E.ENAME,E.SAL, T.AVGSAL,T.DEPTNO
FROM EMP E
INNER JOIN (SELECT DEPTNO,AVG(SAL) AVGSAL
            FROM EMP E
            GROUP BY DEPTNO) T ON (E.DEPTNO = T.DEPTNO AND E.SAL > T.AVGSAL)
ORDER BY T.DEPTNO;

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

• (1)按照部门编号分组,查询平均工资

SELECT DEPTNO,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO ;

• (2)把上面的查询结果当做一个临时表T,和SALGRADE表进行连接,连接的条件(T.AVGSAL BETWEEN S.LOSAL AND S.HISAL)

SELECT  T.DEPTNO,T.AVGSAL ,S.GRADE,S.LOSAL,S.HISAL
FROM SALGRADE S
JOIN (SELECT DEPTNO,AVG(SAL) AVGSAL
      FROM EMP 
      GROUP BY DEPTNO) T ON (T.AVGSAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY T.DEPTNO;

4.不准用组函数（MAX），取得最高薪水

• 方式1:按照SAL降序排列,使用LIMIT取得第一条结果

SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT 1;
SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT 0,1; 

• 方式2:
• (1)使用连接,查询比最高工资低的工资

SELECT  DISTINCT A.SAL
FROM EMP A
INNER JOIN EMP B ON (A.SAL < B.SAL);

• (2)把上面的查询结果当做条件,

SELECT SAL
FROM EMP 
WHERE SAL NOT IN ( SELECT  DISTINCT A.SAL
                   FROM EMP A
                   INNER JOIN EMP B ON (A.SAL < B.SAL));

• (3)使用MAX()函数

SELECT MAX(SAL) MAXSAL FROM EMP;

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

• 方式1:按照部分编号分组,查询平均工资,然后按照平均薪水降序排列,使用LIMIT取得第一条结果

SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;

• 方式2:

-- (1)按照部分编号分组,查询平均工资
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO;

-- (2)把上面的查询结果当做一个临时表t,查询最高的平均工资
SELECT MAX(T.AVGSAL) FROM ( SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T;


-- (3)用第二步的查询结果当做条件,过滤第一步的查询结果
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO
HAVING AVGSAL = (SELECT MAX(T.AVGSAL)
                 FROM ( SELECT DEPTNO ,AVG(SAL) AVGSAL
                        FROM EMP 
                        GROUP BY DEPTNO) T);

• 方式3:

-- (1)按照部分编号分组,查询平均工资
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO;

-- (2) 使用LIMIT查询最高的平均工资,也就是查询部门的平均工资,然后按照平均工资降序排列,使用LIMIT取得第一个结果
SELECT AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO ORDER BY AVGSAL DESC LIMIT 1

-- (3)把第二步的查询结果当做查询条件,过滤第一步的查询结果
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO
HAVING AVGSAL = ( SELECT AVG(SAL) AVGSAL
                  FROM EMP 
                  GROUP BY DEPTNO
                  ORDER BY AVGSAL DESC
                  LIMIT 1);

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

• 方式1:

-- (1)取得平均薪水最高的部门的部门编号
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO
ORDER BY AVGSAL DESC
LIMIT 1;

-- (2)把上面的查询结果当做一个临时表T,和DEPT 表进行连接
SELECT T.DEPTNO ,D.DNAME,T.AVGSAL
FROM DEPT D
INNER JOIN ( SELECT DEPTNO ,AVG(SAL) AVGSAL
             FROM EMP 
             GROUP BY DEPTNO
             ORDER BY AVGSAL DESC
             LIMIT 1) T ON (D.DEPTNO = T.DEPTNO);

• 方式2: 把dept和emp进行连接查询,然后按照部门名称进行分组,并且将查询结果按照平均工资降序排列,使用limit取得第一条结果

SELECT D.DNAME,AVG(E.SAL) AVGSAL
FROM EMP E
INNER JOIN DEPT D  ON (E.DEPTNO = D.DEPTNO)
GROUP BY D.DNAME
ORDER BY  AVGSAL DESC
LIMIT 1;

7.求平均薪水的等级最低(高)的部门的部门名称

• (1).查询并且薪水及其部门名称,也就是把EMP表和DEPT表进行连接查询,然后按照部门名称进行分组

SELECT D.DNAME,AVG(E.SAL) AVGSAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
GROUP BY D.DNAME;

• (2)把上面的查询结果当做一个临时表T,和SALGRADE 表进行连接,查询平均工资的级别

SELECT T.DNAME,T.AVGSAL ,S.GRADE
FROM ( SELECT D.DNAME,AVG(E.SAL) AVGSAL
       FROM EMP E
       INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
       GROUP BY D.DNAME) T 
INNER JOIN SALGRADE S ON (T.AVGSAL BETWEEN S.LOSAL AND S.HISAL);

• (3)把上面的查询结果当做一个临时表T,查询最大的工资级别

SELECT MAX(T.GRADE) MAXGRADE
FROM (SELECT T.DNAME,T.AVGSAL ,S.GRADE
      FROM ( SELECT D.DNAME,AVG(E.SAL) AVGSAL
             FROM EMP E
             INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
             GROUP BY D.DNAME) T 
      INNER JOIN SALGRADE S ON (T.AVGSAL BETWEEN S.LOSAL AND S.HISAL)) T;

• (4)把上面的查询结果当做添加,过滤第二步的查询结果

SELECT T.DNAME,T.AVGSAL ,S.GRADE
FROM ( SELECT D.DNAME,AVG(E.SAL) AVGSAL
       FROM EMP E
       INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
       GROUP BY D.DNAME) T 
INNER JOIN SALGRADE S ON (T.AVGSAL BETWEEN S.LOSAL AND S.HISAL)
WHERE S.GRADE = (  SELECT MAX(T.GRADE) MAXGRADE
                   FROM ( SELECT T.DNAME,T.AVGSAL ,S.GRADE
                          FROM ( SELECT D.DNAME,AVG(E.SAL) AVGSAL
                                 FROM EMP E
                                 INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
                                 GROUP BY D.DNAME) T 
                   INNER JOIN SALGRADE S ON (T.AVGSAL BETWEEN S.LOSAL AND S.HISAL)) T  );

8.取得比普通员工的最高薪水还要高的经理人姓名

• ​

-- 思路:
-- (1)查询出所有的经理的编号,也就是出现在MGR中的员工编号
SELECT DISTINCT MGR FROM EMP ;


-- (2)查询员工的编号,也就是EMPNO没有出现在经理编号
/*
SELECT *
FROM EMP 
WHERE EMPNO NOT IN ( SELECT DISTINCT MGR FROM EMP );

上面的语句查询不到结果,这是因为 EMPNO  NOT IN (7788,NULL) 等效于 EMPNO <> 7788 OR EMPNO <> NULL
IN会自动的忽略空值

所以需要我们手动的排除空值,改正上面的语句
*/
SELECT *
FROM EMP 
WHERE EMPNO NOT IN ( SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL );

-- (3)查询所有员工的最高工资,也就是把上面的查询结果单做一个临时表T,
SELECT MAX(T.SAL) MAXSAL
FROM ( SELECT *
       FROM EMP 
       WHERE EMPNO NOT IN ( SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL )) T;

-- (4)把上面的查询结果当做条件
SELECT *
FROM EMP 
WHERE SAL > ( SELECT MAX(T.SAL) MAXSAL
              FROM ( SELECT *
              FROM EMP 
              WHERE EMPNO NOT IN ( SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL )) T);

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

-- (1)查询员工表的信息,按照工资降序排列
SELECT * FROM EMP ORDER BY SAL DESC;

-- (2)使用LIMIT取得前5条记录
SELECT * FROM EMP ORDER BY SAL DESC  LIMIT 0,5; -- 从0开始,取5条记录
SELECT * FROM EMP ORDER BY SAL DESC  LIMIT 5;   -- 默认的就是从0开始的


/*******************************************豪华的分割线*************************************************/
-- 10.取得薪水最高的第六到第十名员工

-- (1)查询员工表的信息,按照工资降序排列
SELECT * FROM EMP ORDER BY SAL DESC;

-- (2)使用LIMIT取得6~10位的数据
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5,5; -- 从5开始,连续的5条记录

11.取得最后(也就是最大的日期)入职的5名员工

-- 日期类型的数据也是有大小的,日期类型数据的大小可以由其字面值来决定,例如"1987"小于"2018" 
-- (1)查询员工信息,按照入职日期降序排列
SELECT * FROM EMP ORDER BY HIREDATE DESC;

-- (2)使用LIMIT取得前5条记录
SELECT * FROM EMP ORDER BY HIREDATE DESC LIMIT 5;

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

-- (1)查询员工的薪水级别(把emp表和salgrade表进行连接)
SELECT E.SAL ,S.GRADE
FROM EMP E 
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

-- (2)使用GRADE进行分组,查询每组的数据总数(count())

SELECT count(*) ,S.GRADE
FROM EMP E 
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
GROUP BY S.GRADE;

14.列出所有员工及领导的名字

/*
-- 使用自连接进行查询
SELECT E.EMPNO "员工编号",E.ENAME "员工姓名",M.EMPNO "经理编号",M.ENAME "经理姓名"
FROM EMP E
INNER JOIN EMP M ON (E.MGR = M.EMPNO);

上面的语句是错误的,因为没有查询出7839这个人的经理;因为内连接只能查询满足一一对应关系的数据
我们要查询出不满足对应关系的7839,应该使用外连接
改正上面的语句
*/
SELECT E.EMPNO "员工编号",E.ENAME "员工姓名",M.EMPNO "经理编号",M.ENAME "经理姓名"
FROM EMP E
LEFT JOIN EMP M ON (E.MGR = M.EMPNO);

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

-- (1)查询员工其经理
SELECT E.EMPNO "员工编号",E.ENAME "员工姓名",M.EMPNO "经理编号",M.ENAME "经理姓名"
FROM EMP E
LEFT JOIN EMP M ON (E.MGR = M.EMPNO);

-- (2)使用受雇日期早于其直接上级的日期作为条件,对上面的结果进行过滤
SELECT E.EMPNO "员工编号",E.ENAME "员工姓名" ,E.DEPTNO "部门编号",M.EMPNO "经理编号",M.ENAME "经理姓名"
FROM EMP E
LEFT JOIN EMP M ON (E.MGR = M.EMPNO) 
WHERE E.HIREDATE < M.HIREDATE;

-- (3)把上面的查询结果和DEPT表进行连接
SELECT T.EMPNO,T.ENAME ,T.DEPTNO ,D.DNAME
FROM DEPT D
INNER JOIN ( SELECT E.EMPNO ,E.ENAME,E.DEPTNO   
             FROM EMP E
             LEFT JOIN EMP M ON (E.MGR = M.EMPNO) 
             WHERE E.HIREDATE < M.HIREDATE) T   ON (D.DEPTNO = T.DEPTNO)
ORDER BY T.DEPTNO;

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

SELECT * FROM EMP;
SELECT * FROM DEPT;

--  使用外连接进行查询
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME
FROM EMP E
RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

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

-- (1)把dept和emp进行连接查询
SELECT E.EMPNO ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

-- (2)对上的查询结果进行分组(按照deptno,dname,loc进行分组),并且查询每组的记录总数
SELECT COUNT(E.EMPNO) TOTAL ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
GROUP BY D.DEPTNO,D.DNAME,D.LOC;

-- (3)使用HAVGING对分组后的数据进行过滤
SELECT COUNT(E.EMPNO) TOTAL ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
GROUP BY D.DEPTNO,D.DNAME,D.LOC
HAVING TOTAL >= 5;

18.列出薪水比“SMITH”多的所有员工信息

-- (1)查询"smith"的工资
SELECT SAL FROM EMP WHERE ENAME = "SMITH";

-- (2)用上面的查询结果当做查询条件,也就是使用一个出现在WHERE后面的子查询
SELECT *
FROM EMP 
WHERE SAL > ( SELECT SAL FROM EMP WHERE ENAME = "SMITH" );

19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数

-- (1)查询所有办事员的姓名及其部门名称,用一个等值连接(EMP,DEPT)
SELECT E.ENAME,E.JOB,D.DEPTNO ,D.DNAME
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
WHERE E.JOB = "CLERK";

-- (2)按照部门编号分组,查询每个部门的员工总数
SELECT DEPTNO ,COUNT(*) TOTALEMP
FROM EMP 
GROUP BY DEPTNO;

-- (3)把上面的两个查询结果当做临时表t1,t2 ,做一个连接查询
SELECT T1.ENAME,T1.JOB,T1.DEPTNO,T1.DNAME ,T2.TOTALEMP
FROM (SELECT E.ENAME,E.JOB,D.DEPTNO ,D.DNAME
     FROM EMP E
     INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) 
     WHERE E.JOB = "CLERK") T1
INNER JOIN (SELECT DEPTNO ,COUNT(*) TOTALEMP
            FROM EMP 
            GROUP BY DEPTNO) T2 ON (T1.DEPTNO = T2.DEPTNO);

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

-- (1)列出最低薪水大于1500的各种工作,按照职务分组查询最低工资,并且过滤大于1500的记录
SELECT JOB ,MIN(SAL) MINSAL
FROM EMP 
GROUP BY JOB
HAVING MINSAL > 1500;

-- (2)在上面查询结果的基础上出现员工总数
SELECT JOB ,MIN(SAL) MINSAL ,COUNT(*) TOTAL
FROM EMP 
GROUP BY JOB
HAVING MINSAL > 1500;

-- (3)校验数据
SELECT *
FROM EMP 
WHERE SAL > 1500 
ORDER BY JOB;

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

-- (1)从部门表中查询"SALES"的部门编号
SELECT DEPTNO FROM DEPT WHERE DNAME = "SALES";

-- (2)用上面的查询结果当做查询条件,也就是使用出现在WHERE中的子查询
SELECT DEPTNO,ENAME
FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = "SALES");

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

-- (1)列出薪金高于公司平均薪金的所有员工
SELECT E.ENAME ,E.SAL
FROM EMP E 
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP )

-- (2)在上面的查询基础上,查询上级领导(使用自连接) ,员工的工资级别(和SALGRADE 表进行连接)
SELECT E.ENAME "员工姓名",E.SAL "员工工资" ,S.GRADE "工资级别",E.DEPTNO "部门编号" ,M.ENAME "经理姓名" 
FROM EMP E 
INNER JOIN EMP M ON (E.MGR = M.EMPNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP )


-- (3)把KING也查询出来,也就是做自连接时候使用外连接方式,
SELECT E.ENAME "员工姓名",E.SAL "员工工资" ,S.GRADE "工资级别",E.DEPTNO "部门编号" ,M.ENAME "经理姓名" 
FROM EMP E 
LEFT JOIN EMP M ON (E.MGR = M.EMPNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP );

23.列出与“SCOTT”从事相同工作的所有员工及部门名称

-- (1)查询出和SCOTT从事相同职务的员工,使用是一个出现在WHERE后面的子查询
SELECT ENAME,JOB
FROM EMP 
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME ="SCOTT");

-- (2)在上面查询的基础上,连接DEPT表
SELECT E.ENAME,E.JOB , D.DEPTNO,D.DNAME
FROM EMP  E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.JOB = (SELECT JOB FROM EMP WHERE ENAME ="SCOTT");

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

-- (1)查询出30部门的员工工资
SELECT SAL FROM EMP WHERE DEPTNO = 30;

-- (2)把上面的查询结果当做查询条件,也就是使用出现在WHERE后面子查询
SELECT  SAL ,ENAME ,DEPTNO
FROM EMP 
WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND (DEPTNO <> 30);

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

-- 方式1:

-- (1)查询30部门的最高工资
SELECT MAX(SAL) MAXSAL FROM EMP WHERE DEPTNO = 30;

-- (2)用上面的查询结果当做查询条件,并且和DEPT进行连接查询查询出部门名称
SELECT  E.ENAME ,E.SAL ,E.DEPTNO ,D.DNAME
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.SAL > (SELECT MAX(SAL) MAXSAL FROM EMP WHERE DEPTNO = 30);


-- 方式2:
-- 如果子查询返回多行多列,需要使用多行比较操作符,用 > ALL,表示大于子查询的最大值 
SELECT E.ENAME,E.SAL,E.DEPTNO ,D.DNAME
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30)


-- 如果子查询返回多行多列,需要使用多行比较操作符,用 < ALL,表示小于子查询的最小值 
SELECT E.ENAME,E.SAL,E.DEPTNO ,D.DNAME
FROM EMP E 
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30)

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

-- (1)所有所有的部门信息及其员工信息
SELECT E.*,d.*
FROM EMP E
RIGHT  JOIN DEPT D ON (E.DEPTNO= D.DEPTNO);

-- (2)在上面查询的基础上用部门编号分组,查询员工总数
--  注意,count(*)会查询包含null的数据,所以用count(ename)
SELECT D.DEPTNO,COUNT(ENAME) TOTALEMP
FROM EMP E
RIGHT  JOIN DEPT D ON (E.DEPTNO= D.DEPTNO)
GROUP BY D.DEPTNO;

-- (3)在上面的查询结果的基础上,查询平均工资
SELECT D.DEPTNO,COUNT(ENAME) TOTALEMP ,IFNULL( AVG(SAL),0) AVGSAL
FROM EMP E
RIGHT  JOIN DEPT D ON (E.DEPTNO= D.DEPTNO)
GROUP BY D.DEPTNO

-- (4)在上面的查询结果的基础上,查询平均服务年限
-- Mysql中有一个TO_DAYS()函数,把一个日期转换为天数(距离0年的天数)

SELECT  AVG((TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) / 365)
FROM EMP;

SELECT D.DEPTNO,COUNT(ENAME) TOTALEMP ,IFNULL( AVG(SAL),0) AVGSAL,
       IFNULL( AVG((TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) / 365),0 ) SERVERTIME
FROM EMP E
RIGHT  JOIN DEPT D ON (E.DEPTNO= D.DEPTNO)
GROUP BY D.DEPTNO

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

-- 连接EMP,DEPT表,做等值连接
-- SQL1999语句
SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

-- SQL1992语句
SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E,DEPT D 
WHERE E.DEPTNO = D.DEPTNO;

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

-- 用外连接,连接emp表和dept表,然后按照部门编号分组,查询员工总数
SELECT D.DEPTNO, D.DNAME,D.LOC ,COUNT(ENAME) TOTALEMP
FROM EMP E 
RIGHT JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
GROUP BY D.DEPTNO,D.DNAME,D.LOC ;

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

-- (1)按照职务分组,查询最低工资
SELECT JOB,MIN(SAL) MINSAL
FROM EMP 
GROUP BY JOB;

-- (2)把上面的查询结果当做临时表t,和EMP表进行连接查询
SELECT E.ENAME,T.JOB,T.MINSAL
FROM EMP E
INNER JOIN (SELECT JOB,MIN(SAL) MINSAL
            FROM EMP 
            GROUP BY JOB) T ON (E.JOB = T.JOB AND E.SAL = T.MINSAL);

30.列出各个部门MANAGER的最低薪金

-- (1)查询职务为"MANAGER"的员工信息
SELECT *
FROM EMP 
WHERE JOB = "MANAGER";

-- (2)在上面查询的基础上,按照部门编号分组,查询最低工资
SELECT DEPTNO,MIN(SAL) MINSAL
FROM EMP 
WHERE JOB = "MANAGER"
GROUP BY DEPTNO;

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

-- 年薪 = (月薪+津贴) * 12
SELECT EMPNO,ENAME, SAL, COMM, (SAL+ IFNULL(COMM,0)) * 12 AS "年薪" 
FROM EMP 
ORDER BY 年薪 ;

SELECT EMPNO,ENAME, SAL, COMM, (SAL+ IFNULL(COMM,0)) * 12 AS "年薪" 
FROM EMP 
ORDER BY 年薪 ASC;

SELECT EMPNO,ENAME, SAL, COMM, (SAL+ IFNULL(COMM,0)) * 12 AS "年薪" 
FROM EMP 
ORDER BY 年薪 DESC;

32.求出员工领导的薪水超过3000的员工名称和领导名称

-- (1)使用自连接查询员工信息及其领导信息
SELECT E.ENAME "员工姓名" ,E.SAL "员工工资" ,M.ENAME "经理姓名" ,M.SAL "经理工资"
FROM EMP E 
INNER JOIN EMP M ON (E.MGR = M.EMPNO);

-- (2)在上面查询的基础上,加上查询条件,领导的工资大于3000
-- 在加入其它的的查询条件的时候,可以使用WHERE 
SELECT E.ENAME "员工姓名" ,E.SAL "员工工资" ,M.ENAME "经理姓名" ,M.SAL "经理工资"
FROM EMP E 
INNER JOIN EMP M ON (E.MGR = M.EMPNO)
WHERE M.SAL > 3000;


-- 在加入其它的的查询条件的时候,可以使用WHERE ,也可以使用AND
SELECT E.ENAME "员工姓名" ,E.SAL "员工工资" ,M.ENAME "经理姓名" ,M.SAL "经理工资"
FROM EMP E 
INNER JOIN EMP M ON (E.MGR = M.EMPNO) AND (M.SAL > 3000);

33.求部门名称中带“S”字符的部门员工的工资合计、部门人数

-- (1)查询出部门名称,工资合计,部门人数
SELECT D.DNAME, IFNULL(SUM(SAL),0) SUMSAL ,COUNT(ENAME) TOTALEMP
FROM EMP E 
RIGHT JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
GROUP BY D.DNAME;

--  在上面的查询基础是上,加上条件:部门名称中带“S”字符
SELECT D.DNAME, IFNULL(SUM(SAL),0) SUMSAL ,COUNT(ENAME) TOTALEMP
FROM EMP E 
RIGHT JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE D.DNAME LIKE "%S%"
GROUP BY D.DNAME;

34.给任职日期超过30年的员工加薪10%

DROP TABLE IF EXISTS EMP_BAK;
CREATE TABLE EMP_BAK AS SELECT * FROM EMP ;
SELECT * FROM EMP_bak;

UPDATE EMP_BAK SET SAL=SAL*1.1  WHERE (( TO_DAYS(NOW()) - TO_DAYS(HIREDATE)) / 365 > 30)