#数据库查询

ALTER TABLE emp ADD updated_salary DECIMAL(10,4); 
UPDATE emp
SET updated_salary =
    CASE
        WHEN job = 'CLERK' THEN sal * 1.1
        WHEN job = 'SALESMAN' THEN sal * 1.15
        WHEN job = 'PRESIDENT' THEN sal * 0.9
        WHEN job = 'MANAGER' THEN sal * 1.05
        WHEN job = 'ANALYST' THEN sal * 1.2
        ELSE sal 
    END;

ALTER TABLE emp ADD newsalgrade NUMBER; 
UPDATE emp
SET newsalgrade = (
  SELECT hisal
  FROM salgrade
  WHERE emp.updated_salary BETWEEN losal AND hisal
)
WHERE updated_salary IS NOT NULL;

SELECT newsalgrade, COUNT(*) AS num_of_employees
FROM emp
GROUP BY newsalgrade;

ALTER TABLE emp ADD oldsalgrade NUMBER; 
UPDATE emp
SET oldsalgrade = (
  SELECT hisal
  FROM salgrade
  WHERE emp.sal BETWEEN losal AND hisal
)
WHERE sal IS NOT NULL;

SELECT oldsalgrade, COUNT(*) AS num_of_employees
FROM emp
GROUP BY oldsalgrade;

SELECT sg.hisal AS "salary_grade",
       COUNT(CASE WHEN e.oldsalgrade = sg.hisal THEN 1 END) AS "num_of_employees_before",
       COUNT(CASE WHEN e.newsalgrade = sg.hisal THEN 1 END) AS "num_of_employees_after",
       COUNT(CASE WHEN e.oldsalgrade = sg.hisal THEN 1 END) - COUNT(CASE WHEN e.newsalgrade = sg.hisal THEN 1 END) AS "num_of_employee_changes"
FROM salgrade sg
LEFT JOIN emp e ON sg.hisal = e.oldsalgrade OR sg.hisal = e.newsalgrade
GROUP BY sg.hisal;

SELECT 
    d.DEPTNO,
    d.DNAME,
    COUNT(CASE WHEN e.OLDSALGRADE <> e.NEWSALGRADE THEN e.EMPNO END) AS "薪水等级发生变化客户数"
FROM 
    emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO
GROUP BY 
    d.DEPTNO, d.DNAME;
 

SELECT 
    d.DEPTNO AS '部门编号',
    d.DNAME AS '部门名称',
    SUM(CASE WHEN e.NEWSALGRADE = 1200 THEN 1 ELSE 0 END) AS "薪水等级1级职员数",
    SUM(CASE WHEN e.NEWSALGRADE = 1400 THEN 1 ELSE 0 END) AS "薪水等级2级职员数",
    SUM(CASE WHEN e.NEWSALGRADE = 2000 THEN 1 ELSE 0 END) AS "薪水等级3级职员数",
    SUM(CASE WHEN e.NEWSALGRADE = 3000 THEN 1 ELSE 0 END) AS "薪水等级4级职员数",
    SUM(CASE WHEN e.NEWSALGRADE = 9999 THEN 1 ELSE 0 END) AS "薪水等级5级职员数",
    COUNT(CASE WHEN e.OLDSALGRADE <> e.NEWSALGRADE THEN e.EMPNO END) AS "薪水等级发生变化客户数"
FROM 
    emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO
GROUP BY 
    d.DEPTNO, d.DNAME
ORDER BY
    d.DEPTNO;
 

SELECT 
    d.DEPTNO AS '部门编号',
    COALESCE(d.DNAME, '未知') AS '部门名称',
    SUM(CASE WHEN e.NEWSALGRADE = 1 THEN 1 ELSE 0 END) AS '薪水等级1级职员数',
    SUM(CASE WHEN e.NEWSALGRADE = 2 THEN 1 ELSE 0 END) AS '薪水等级2级职员数',
    SUM(CASE WHEN e.NEWSALGRADE = 3 THEN 1 ELSE 0 END) AS '薪水等级3级职员数',
    SUM(CASE WHEN e.NEWSALGRADE = 4 THEN 1 ELSE 0 END) AS '薪水等级4级职员数',
    SUM(CASE WHEN e.NEWSALGRADE = 5 THEN 1 ELSE 0 END) AS '薪水等级5级职员数',
    COUNT(CASE WHEN e.OLDSALGRADE <> e.NEWSALGRADE THEN e.EMPNO END) AS '薪水等级发生变化客户数'
FROM 
    emp e LEFT JOIN dept d ON e.DEPTNO = d.DEPTNO
GROUP BY 
    d.DEPTNO, d.DNAME
ORDER BY
    d.DEPTNO;