mysql练习题emp,dept

DROP DATABASE IF EXISTS `emp`;
CREATE DATABASE `emp`;
USE emp;
 CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 SELECT *FROM  dept;
 INSERT INTO dept VALUES (10,"ACCOUNTING","NEW YORK"); 
INSERT INTO dept VALUES (20,"RESEARCH","DALLAS"); 
INSERT INTO dept VALUES (30,"SALES","CHICAGO");  
INSERT INTO dept VALUES (40,"OPERATIONS","BOSTON");
CREATE TABLE `emp` (
    `empno` INT(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` INT(4),  
    `hiredate` DATE,  
    `sal` FLOAT(7,2),  
    `comm` FLOAT(7,2),  
    `deptno` INT(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT *FROM emp;
INSERT INTO EMP VALUES (7369,"SMITH","CLERK",7902,"1980-12-17",800,NULL,20); 
INSERT INTO EMP VALUES (7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30);
INSERT INTO EMP VALUES (7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30); 
INSERT INTO EMP VALUES (7566,"JONES","MANAGER",7839,"1981-04-02",2975,NULL,20); 
INSERT INTO EMP VALUES (7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30); 
INSERT INTO EMP VALUES (7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,NULL,30); 
INSERT INTO EMP VALUES (7782,"CLARK","MANAGER",7839,"1981-06-09",2450,NULL,10); 
INSERT INTO EMP VALUES (7788,"SCOTT","ANALYST",7566,"1987-07-13",3000,NULL,20); 
INSERT INTO EMP VALUES (7839,"KING","PRESIDENT",NULL,"1981-11-07",5000,NULL,10); 
INSERT INTO EMP VALUES (7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30); 
INSERT INTO EMP VALUES (7876,"ADAMS","CLERK",7788,"1987-07-13",1100,NULL,20); 
INSERT INTO EMP VALUES (7900,"JAMES","CLERK",7698,"1981-12-03",950,NULL,30); 
INSERT INTO EMP VALUES (7902,"FORD","ANALYST",7566,"1981-12-03",3000,NULL,20); 
INSERT INTO EMP VALUES (7934,"MILLER","CLERK",7782,"1982-01-23",1300,NULL,10);
SELECT *FROM emp;
CREATE TABLE `salgrade` (  
    `grade` INT, 
    `losal` INT,  
    `hisal` INT
) ENGINE=INNODB DEFAULT CHARSET=utf8; 
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);
SELECT *FROM salgrade;
-- SQL练习训练一sal工资，comm奖金，mgr 经理编号
-- 1、 选择部门30中的雇员
SELECT *FROM emp WHERE deptno="30";
-- 2、 检索emp表中的员工姓名、月收入及部门编号
SELECT ename 姓名,sal 月收入,empno 部门编号 FROM emp;

-- 3、 检索emp表中员工姓名、及雇佣时间（雇佣时间按照yyyy-mm-dd显示）
 SELECT ename 姓名,hiredate 雇佣时间 FROM emp;
-- 4、 检索emp表中的部门编号及工种，并去掉重复行 
SELECT DISTINCT empno 部门编号,job 工种 FROM emp;
-- 5、 检索emp表中的员工姓名及全年的月收入 
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 6、 用姓名显示员工姓名，用年收入显示全年月收入。 
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 7、 检索月收入大于2000的员工姓名及月收入 
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal>2000;
-- 8、 检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间  
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal<=2000 AND sal>=1000;
-- 9、 检索以S开头的员工姓名及月收入
SELECT ename 姓名,sal 月收入 FROM emp WHERE ename LIKE "s%";
-- 10、检索emp表中月收入是800的或是1250的员工姓名及部门编号
SELECT ename 姓名, empno 部门编号 FROM emp WHERE sal="800"OR sal="1250";
-- 11、显示在部门20中岗位是CLERK的所有雇员信息 
SELECT *FROM (SELECT *FROM emp WHERE job="CLERK") s WHERE s.deptno="20";
-- 12、显示工资高于2500或岗位为MANAGER的所有雇员信息 
SELECT *FROM emp WHERE sal>2500 OR job="MANAGER";
-- 13、检索emp表中有奖金的员工姓名、月收入及奖金 
SELECT ename 姓名,sal 月收入,comm 奖金 FROM emp WHERE comm>0;
-- 14、检索emp表中部门编号是30的员工姓名、月收入及提成，并要求其结果按月收入升序、然后按提成降序显示 
SELECT s.ename 姓名,s.sal 月收入,s.mgr 提成 FROM(SELECT *FROM emp WHERE deptno="30" ORDER BY sal ASC) s ORDER BY s.mgr DESC;
-- 15、列出所有办事员的姓名、编号和部门(姓名对不上)
SELECT  a.ename 姓名,a.empno ,b.deptno FROM dept a LEFT JOIN emp b WHERE a.ename=b.dname;
-- 17、找出部门10中所有经理和部门20中的所有办事员的详细资料
SELECT *FROM emp WHERE (deptno="10" AND job="MANAGER") OR (deptno ="20" AND job="CLERK");
-- 18、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
SELECT *FROM emp WHERE (deptno="10" AND job="MANAGER") OR (deptno ="20" AND job="CLERK")OR (job NOT IN("MANAGER","CLERK") AND sal>=2000);

-- 19、找出收取奖金的雇员的不同工作
SELECT DISTINCT job 工作 FROM emp WHERE comm>0;
-- 20、找出不收取奖金或收取的奖金低于100的雇员 
SELECT ename 姓名 FROM emp WHERE comm<100 OR comm IS NULL;
-- 21、找出各月倒数第三天受雇的所有雇员
SELECT ename 各月倒数三天受雇人姓名 FROM emp WHERE DAYOFMONTH(LAST_DAY(hiredate))-DAYOFMONTH(hiredate)<=3;
SELECT LAST_DAY(hiredate) FROM emp;
SELECT DAYOFMONTH(LAST_DAY(hiredate)) FROM emp;

SELECT DAYOFMONTH(hiredate) FROM emp;

-- select DAYOFMONTH(now())


-- 22、获取当前日期所在月的最后一天
SELECT CURDATE();                       -- 获取当前日期
SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)   -- 获取本月第一天
SELECT LAST_DAY(CURDATE());   -- 获取当月最后一天
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ) -- 获取下个月的第一天
SELECT DATEDIFF(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ),DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)) FROM DUAL -- 获取当前月的天数
-- 23、找出早于25年之前受雇的雇员
SELECT *FROM emp WHERE DATE_ADD(NOW(),INTERVAL -25 YEAR)>hiredate;

-- 24、显示正好为6个字符的雇员姓名
SELECT *FROM emp WHERE ename LIKE "%______"; 
SELECT *FROM emp WHERE LENGTH(ename)="6";
-- 25、显示不带有"R"的雇员姓名
SELECT *FROM emp WHERE ename  NOT LIKE "%R%";
-- 26、显示雇员的详细资料，按姓名排序
SELECT *FROM emp ORDER BY ename;
-- 27、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 
 SELECT ename 姓名,hiredate FROM emp ORDER BY hiredate;
-- 28、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
SELECT t.ename 姓名,t.job 工作,t.sal 薪金 FROM(SELECT * FROM emp ORDER BY job DESC) t ORDER BY t.sal ASC;
-- 29、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面 
SELECT t.ename 姓名, YEAR(t.hiredate) 年份, MONTH(t.hiredate)月份  FROM(SELECT *FROM emp ORDER BY DAY(hiredate))t ORDER BY YEAR(t.hiredate);
-- 30、显示在一个月为30天的情况下所有雇员的日薪金
SELECT sal/30 日薪金 FROM emp WHERE  DAY(LAST_DAY(hiredate));
SELECT LAST_DAY(hiredate) FROM emp;
-- 31、找出在（任何年份的）2月受聘的所有雇员
SELECT ename 姓名,MONTH(hiredate) 月份 FROM emp WHERE MONTH(hiredate)=2;
-- 32、对于每个雇员，显示其加入公司的天数
SELECT ename 姓名,TIMESTAMPDIFF(DAY,hiredate,NOW()) 加入公司天数 FROM emp;
-- 33、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名 
SELECT SUBSTRING(ename,1,1)第一个字母,SUBSTRING(ename,2,1)第二个字母,SUBSTRING(ename,3,1)第三个字母,SUBSTRING(ename,4,1)第四个字母,SUBSTRING(ename,5,1)第六个字母,SUBSTRING(ename,6,1)第五个字母,ename 姓名 FROM emp WHERE ename LIKE "%A%";
-- 34、以年、月和日显示所有雇员的服务年限 
SELECT ename 姓名, YEAR(hiredate) 年,MONTH(hiredate)月,DAY(hiredate) 日,TIMESTAMPDIFF(YEAR,hiredate,NOW()) 服务年数 FROM emp;
 
-- 35、选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序.
SELECT t.ename 有奖金员工姓名,t.sal 工资,t.comm 奖金 FROM ( SELECT *FROM emp WHERE comm>0 ORDER BY sal DESC) t ORDER BY t.comm DESC; 
-- 36、选择公司中没有管理者的员工姓名及job 
SELECT ename 姓名,job 工作 FROM emp WHERE job NOT IN("MANAGER","PRESIDENT");
-- 37、选择在1987年雇用的员工的姓名和雇用时间 
SELECT ename 姓名,hiredate 雇用时间 FROM emp WHERE YEAR(hiredate)=1987;
-- 38、选择在20或10号部门工作的员工姓名和部门号 
SELECT ename 姓名,deptno 部门号 FROM  emp WHERE deptno=10 OR deptno=20;
-- 39、选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序. 
SELECT ename 姓名,job 职位,hiredate 雇佣时间 FROM emp WHERE hiredate>"1981-02-01" AND hiredate<"1981-05-01";
-- 40、选择工资不在5000到12000的员工的姓名和工资 
SELECT ename 姓名,sal 工资 FROM emp WHERE NOT sal>"5000" AND sal<"12000";
-- 41、查询员工号为7934的员工的姓名和部门号 
SELECT ename 姓名,deptno 部门号 FROM emp WHERE empno IN("7934");
-- 42、查询工资大于1200的员工姓名和工资
SELECT ename 姓名,sal 工资 FROM emp WHERE sal>1200;

-- 复杂查询
-- 1.  列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT ename 名字,dname 部门名称,(SELECT COUNT(*) FROM emp WHERE deptno=20) 人数 FROM emp,dept WHERE emp.`job`IN(SELECT emp.`JOB` FROM emp WHERE emp.`ENAME` = "SCOTT")AND emp.`deptno`=dept.`deptno`;
SELECT COUNT(*) FROM emp WHERE deptno=20;

-- 2.  列出公司各个工资等级雇员的数量、平均工资。
SELECT salgrade.`GRADE` AS "薪资等级", COUNT(tt.`EMPNO`) AS "员工数" ,AVG(tt.sal)平均工资
FROM salgrade, 
(SELECT emp.`EMPNO`, emp.`SAL`FROM emp) AS tt WHERE tt.`SAL` >= salgrade.`LOSAL` AND tt.`SAL` <= salgrade.`HISAL`
GROUP BY salgrade.`GRADE`;
-- 3.  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
   SELECT MAX(sal) FROM emp WHERE deptno=30;
   SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
   SELECT c.ename 姓名,c.sal 薪金,d.`dname` 部门名称 FROM dept AS d JOIN (SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30)) AS c WHERE d.`deptno`=c.deptno; 
-- 4.  列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT AVG(sal),(SELECT COUNT(*)FROM emp GROUP BY deptno) FROM emp ;
SELECT COUNT(*)FROM emp GROUP BY deptno;
SELECT AVG(sal)FROM emp GROUP BY deptno;
SELECT COUNT(*),AVG(sal),AVG(YEAR(NOW())-YEAR(hiredate))FROM emp GROUP BY deptno;
-- 5.  列出所有员工的姓名、部门名称和工资。
SELECT e.`ename` 姓名,d.`dname` 部门名称,e.sal 工资 FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`;
-- 6.  列出所有部门的详细信息和部门人数。
SELECT d.*,COUNT(*) 部门人数 FROM dept d JOIN emp e ON e.`deptno`=d.`deptno` GROUP BY deptno;
-- 7.  列出各种工作的最低工资及从事此工作的雇员姓名。
SELECT job, MIN(sal) FROM emp  GROUP BY job;
SELECT job, MIN(sal) FROM emp  GROUP BY job;

SELECT ename FROM emp e JOIN (SELECT job, MIN(sal) FROM emp  GROUP BY job) t WHERE e.`job`=t.job AND e.`sal`=MIN(sal); 
-- 8.  列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
SELECT MIN(e.sal),e.`ename` FROM emp e JOIN dept t ON e.`deptno`=t.`deptno` WHERE job IN("MANAGER") GROUP BY t.`deptno`;
SELECT e.`sal`,e.`ename`, d.`dname`,COUNT=(SELECT COUNT(*) FROM emp WHERE job IN("MANAGER")GROUP BY deptno ) FROM emp e JOIN dept d ON e.`deptno`=d.`deptno` WHERE job IN("MANAGER")
-- 9.  列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
 SELECT ename, sal*12 FROM emp ORDER BY sal*12 ASC;
-- 10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
    SELECT mgr FROM emp WHERE ename="SMITH" 
    SELECT e.ename ,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE empno=(SELECT mgr FROM emp WHERE ename="SMITH") AND e.sal>3000;
-- 11. 求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数。
   SELECT deptno FROM dept WHERE dname LIKE "%S%";-- 查询部门
   SELECT SUM(sal),COUNT(*)FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE "%S%") GROUP BY deptno;
-- 12. 给任职日期超过40年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
     SELECT *FROM emp WHERE  YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987;
     SELECT YEAR(NOW())-YEAR(hiredate)FROM emp;
     SELECT IF(emp.deptno=10,sal+sal*0.1,IF(emp.`sal`=20,sal+sal*0.2,IF(emp.`deptno`=30,sal+sal*0.3,"无"))) FROM emp WHERE YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987; 
-- 13. 列出至少有一个员工的所有部门的信息：
     SELECT COUNT(*)FROM emp GROUP BY deptno;
     SELECT   deptno FROM emp  ;
     SELECT *FROM emp LEFT JOIN dept ON dept.`deptno`=emp.`deptno` GROUP BY dept.`dname`  HAVING COUNT(emp.`empno`)>=5;
     SELECT dept.`DNAME` AS "部门名", COUNT(emp.`EMPNO`) AS "员工个数"
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DNAME` HAVING COUNT(emp.`EMPNO`) >= 5;
-- 14. 列出薪水比“SMITH”多的所有员工信息

SELECT *FROM emp WHERE emp.`SAL` > (SELECT emp.`SAL`FROM emp WHERE emp.`ENAME` = "SMITH");


-- 15. 列出所有员工的姓名以及其直接上级的姓名：
SELECT DISTINCT e.ename 员工姓名,d.ename FROM emp e JOIN (SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename)) d; 
SELECT mgr FROM emp WHERE ename=ename;
SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename);

SELECT emp.`ENAME` AS "员工名称", tt.lname AS "领导名称"
FROM emp LEFT JOIN (
    SELECT emp.`EMPNO`, emp.`ENAME` AS "lname"
    FROM emp 
    WHERE empno IN (SELECT mgr FROM emp)) AS tt ON emp.`MGR` = tt.empno;


-- 16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
SELECT t2.eno AS "编号", t2.ename AS "姓名", dept.`DNAME` AS "部门名称", t2.ehiredate AS "员工受雇日期", t2.lhiredate AS "领导受雇日期"
FROM dept
INNER JOIN 
(
    SELECT emp.`EMPNO` AS "eno", emp.`ENAME`, emp.`HIREDATE` AS "ehiredate", t1.lhiredate, emp.`DEPTNO`
    FROM 
    emp LEFT JOIN 
    (
        SELECT emp.`EMPNO` AS "lno", emp.`HIREDATE` AS "lhiredate"
        FROM emp 
        WHERE empno IN 
        (
            SELECT mgr FROM emp
        ) -- 找出所有是领导的员工no
 
    ) AS t1
    ON emp.`MGR` = t1.lno
) AS t2
ON t2.deptno = dept.`DEPTNO`
WHERE t2.ehiredate <  t2.lhiredate OR t2.lhiredate IS NULL;
-- 17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dept.`DNAME` AS "部门名称", emp.`ENAME` AS "员工名称", emp.`JOB` AS "职位", emp.`HIREDATE` AS "入职时间", emp.`SAL` AS "薪水"
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`;
 
 

-- 18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
SELECT emp.`ENAME` AS "姓名", dept.`DNAME` AS "部门名称", tt.emp_count AS "部门人数"
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN 
(
    SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS "emp_count"
    FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
    GROUP BY dept.`DEPTNO` 
 
) AS tt
ON emp.`DEPTNO` = tt.deptno
 
WHERE emp.`JOB` = "CLERK";
-- 19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
SELECT emp.`JOB` AS "工作名称", COUNT(emp.`EMPNO`) AS "雇员人数"
FROM emp 
WHERE emp.`JOB` IN (
    SELECT emp.`JOB`
    FROM emp
    GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
-- 20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
SELECT emp.`ENAME` AS "销售部人员"
FROM emp
WHERE emp.`DEPTNO` = (
    SELECT dept.`DEPTNO`
    FROM dept
    WHERE dept.`DNAME` = "sales"
);
-- 21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
SELECT t1.ename AS "员工名称", dept.`DNAME` AS "部门名称", t2.ename AS "上级名称", t1.grade AS "工资等级", t1.sal AS "工资"
 
FROM 
(
    SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE`
    FROM emp, salgrade
    WHERE emp.`SAL` > (
        SELECT AVG(sal) AS "avg_sal"
        FROM emp
    ) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL`
) AS t1
LEFT JOIN 
(
    SELECT emp.`EMPNO` AS "mgr", emp.`ENAME` -- 注意这里是查出所有领导的empno，作为mgr编号
    FROM emp 
    WHERE emp.`EMPNO` IN 
    (
        SELECT DISTINCT(emp.`MGR`) -- 必须要distinct 不然查出来的mgr有重复，后面上层再查重复值接近笛卡尔积
        FROM emp
        WHERE mgr IS NOT NULL
    )
) AS t2
ON t1.mgr = t2.mgr
LEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;
-- 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
 
-- 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
SELECT emp.`EMPNO` AS "员工编号", emp.`ENAME` AS "员工名称", (emp.`SAL`*12) AS "年薪" 
FROM emp
ORDER BY 年薪 ASC; 
-- 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
-- 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。
SELECT dept.`DNAME` AS "部门名称", COUNT(emp.`EMPNO`) AS "员工数量", ROUND(AVG(emp.`SAL`), 2) AS "平均工资", t2.avg_year AS "平均服务年限"
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
LEFT JOIN 
(
    SELECT t1.deptno, ROUND(AVG(t1.year), 0) AS "avg_year" -- 四舍五入，保留0位小数
    FROM 
    (
        SELECT emp.`DEPTNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS "year"
        FROM emp
    ) AS t1
    GROUP BY t1.deptno
) AS t2 
ON dept.`DEPTNO` = t2.deptno
GROUP BY dept.`DEPTNO`; 
-- 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。
SELECT emp.`ENAME` AS "姓名", dept.`DNAME` AS "部门名称", tt.emp_count AS "部门人数"
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN 
(
    SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS "emp_count"
    FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
    GROUP BY dept.`DEPTNO` 
 
) AS tt
ON emp.`DEPTNO` = tt.deptno
 
WHERE emp.`JOB` = "CLERK";
-- 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 
SELECT emp.`JOB` AS "工作名称", COUNT(emp.`EMPNO`) AS "雇员人数"
FROM emp 
WHERE emp.`JOB` IN (
    SELECT emp.`JOB`
    FROM emp
    GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。     
-- 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT emp.`EMPNO` AS "员工编号", emp.`ENAME` AS "员工名称", dept.`DNAME` AS "部门名称"-- , emp.`DEPTNO`
FROM emp, dept
WHERE emp.`JOB` = (
    SELECT emp.`JOB` -- 查询出SCOTT从事的职业
    FROM emp
    WHERE emp.`ENAME` = "SCOTT"
) AND emp.`ENAME` <> "SCOTT"
AND emp.`DEPTNO` = dept.`DEPTNO`;
-- 32. 查询dept表的结构
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
34. 检索emp表中有提成的员工姓名、月收入及提成。

SELECT *FROM emp WHERE deptno="20"
SELECT *FROM emp;
SELECT *FROM dept;
SELECT *FROM salgrade;

 

原文地址：https://www.cnblogs.com/zcxxcvbn/arcHive/2022/02/23/15929018.html