(四)MySQL従業員部門照会

--    
CREATE TABLE dept(
    deptno INT PRIMARY KEY AUTO_INCREMENT COMMENT '    ', --     
    dname VARCHAR(20) COMMENT '    ',     --     
    loc VARCHAR(20) COMMENT '  '   --   
);
--    
CREATE TABLE emp(
    empno INT PRIMARY KEY AUTO_INCREMENT,--     
    ename VARCHAR(20) COMMENT '    ', --                                            -
    job VARCHAR(20) COMMENT '  ',   --   
    mgr INT COMMENT '      ',    --       
    hiredate date COMMENT '    ，    ', --     ，    
    sal INT COMMENT '  ', --   
    comm INT COMMENT '  ',  --   
    deptno INT NOT NULL COMMENT '    ', --     
    foreign key (deptno) references dept(deptno)
);
INSERT INTO dept VALUES(10,'   ','  ');
INSERT INTO dept VALUES(20,'   ','  ');
INSERT INTO dept VALUES(30,'   ','  ');
INSERT INTO dept VALUES(40,'   ','  ');
INSERT INTO emp VALUES(7369,'  ','  ',7902,'1980-12-17',800,null,20);
INSERT INTO emp VALUES(7499,'  ','   ',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'  ','   ',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'  ','  ',7839,'1981-04-02',2975,null,20);
INSERT INTO emp VALUES(7654,'  ','   ',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'  ','  ',7839,'1981-05-01',2850,null,30);
INSERT INTO emp VALUES(7782,'  ','  ',7839,'1981-06-09',2450,null,10);
INSERT INTO emp VALUES(7788,'  ','   ',7566,'1987-06-13',3000,null,20);
INSERT INTO emp VALUES(7839,'  ','  ',null,'1981-11-17',5000,null,10);
INSERT INTO emp VALUES(7844,'  ','   ',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'   ','  ',7788,'1987-06-13',1100,null,20);
INSERT INTO emp VALUES(7900,'   ','  ',7698,'1981-12-03',950,null,30);
INSERT INTO emp VALUES(7902,'   ','   ',7566,'1981-12-03',3000,null,20);
INSERT INTO emp VALUES(7934,'   ','  ',7782,'1983-01-23',1300,null,10);

-- 1．              。
SELECT d.dname     , COUNT(*)      
FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
GROUP BY e.deptno;

-- 2．     "  "      。
SELECT ename     , sal      
FROM emp 
WHERE sal > (SELECT sal FROM emp WHERE ename = '  ');

-- 3．                  。
SELECT self.ename   , mgr.ename    
FROM emp self
LEFT JOIN emp mgr
ON self.mgr = mgr.empno;

-- 4．                  。
SELECT self.ename   , mgr.ename   , self.hiredate       , mgr.hiredate        
FROM emp self
LEFT JOIN emp mgr
ON self.mgr = mgr.empno
WHERE self.hiredate < mgr.hiredate;

-- 5．                ，             。
SELECT d.dname     , e.ename      
FROM dept d
LEFT JOIN emp e
ON e.deptno = d.deptno;

-- 6．    job “  ”         。
SELECT e.ename     , d.dname     , e.job
FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE e.job = '  ';

-- 7．        1500     。
SELECT DISTINCT job FROM emp WHERE sal > 1500;

-- 8．      "   "         ，             。
SELECT e.ename FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE d.dname = '   ';

-- 9．                 。
SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

-- 10．   "  "           。
SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = '  ');

-- 11．        30                 。
SELECT ename, sal, deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);

-- 12．         30                  。
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

-- 13．              、    。
SELECT dept.deptno,COUNT(emp.empno),AVG(sal)
FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno
GROUP BY dept.deptno;

-- 14．         、       。
SELECT e.ename   , d.dname     , e.sal    FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno;

-- 15．                。
SELECT d.deptno     , d.dname     , d.loc   , tmp.count    FROM dept d 
LEFT JOIN (SELECT deptno, COUNT(deptno) count FROM emp GROUP BY deptno) tmp
ON d.deptno = tmp.deptno;

-- 16．           。
SELECT job, MIN(sal)      FROM emp GROUP BY job;

-- 17．                。
SELECT d.dname     , tmp.sal    FROM dept d
LEFT JOIN (SELECT MIN(sal) sal, deptno FROM emp WHERE job = '  ' GROUP BY deptno) tmp
ON d.deptno = tmp.deptno;

-- 18．          ,         。 
SELECT ename, (sal + IFNULL(comm, 0)) * 12 FROM emp;

-- 19.  emp     3000  （  3000）         、  、  。
SELECT empno, ename, sal FROM emp WHERE sal >= 3000;

-- 20.        '  '         。
SELECT empno, ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '  ');

-- 21.   emp       20，   2000  （   2000）     ，        ，      ，       ：            
SELECT empno     , ename     , sal    FROM emp WHERE sal > 2000 AND deptno = 20;

-- 22.   emp         （   ）
SELECT job FROM emp GROUP BY job;

-- 23.       （comm）             。
SELECT * FROM emp WHERE comm IS NOT NULL;

-- 24.      800 2500  （   ）       。（ ：        and  between and）
SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;
SELECT * FROM emp WHERE sal >= 800 AND sal <= 2500;

-- 25.       7521，7900，7782        。（ ：        ，or  in）
SELECT * FROM emp WHERE empno IN (7521, 7900, 7782);
SELECT * FROM emp WHERE empno = 7521 OR empno = 7900 OR empno = 7782;

-- 26.       “ ”  ，     1000  （   1000）       。
SELECT * FROM emp WHERE ename LIKE '% %' AND sal > 1000;

-- 27.           “ ”       。
SELECT * FROM emp WHERE ename LIKE '__ %';

-- 28.            ，               。
SELECT * FROM emp ORDER BY sal, hiredate DESC;

-- 29.                ，              。 order by convert(name using gbk) asc; 
SELECT ename, SUBSTR(ename, 1, 1), sal FROM emp ORDER BY CONVERT(substring(ename,1,1) USING gbk), sal DESC;

-- 30.              、       。
SELECT * FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);

-- 31.         、  、  ，      ，    100.
SELECT ename, sal, IFNULL(comm, 100) comm FROM emp;

-- 32.           。
SELECT job, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

-- 33.  emp                ，     10      。
SELECT deptno, MAX(sal)     , MIN(sal)      FROM emp WHERE deptno != 10 GROUP BY deptno;

-- 34.  10          。
DELETE FROM emp WHERE sal = (SELECT emp.sal FROM (SELECT MAX(sal) sal FROM emp WHERE deptno = 10) emp);

-- 35.            30%。
UPDATE emp SET sal = sal * 0.7 WHERE sal = (SELECT tmp.sal FROM (SELECT MAX(sal) sal FROM emp) tmp);

-- 36.      ，        (  >=3000  3 ，  >2000  2 ，  <=2000  1 )
--   ：case when ... then ... when ... then ... else ... end
SELECT ename, sal,
    CASE WHEN sal>=3000 THEN '3 '
         WHEN sal>=2000 THEN '2 '
         ELSE '1 '
    END
FROM emp;