有关部门信息表与员工信息表的常用SQL应用语句实现汇总

背景条件

已知有员工信息表（emp）和部门信息表（dept），具体表的信息如下：

员工信息表emp：

列名	类型	其他	备注
empno	DECIMAL(4)	主键	员工编号
ename	VARCHAR2(10)		员工姓名
job	VARCHAR2(9)		工种
mgr	DECIMAL(4)		管理者代码
hiredate	DATE		雇佣时间
sal	DECIMAL(7,2)		月收入
comm	DECIMAL(7,2)		提成
deptno	DECIMAL(2)	外键	所属部门编号

部门信息表dept：

列名	类型	其他	备注
deptno	DECIMAL(2)	主键	部门编号
dname	VARCHAR2(14)		部门名称
loc	VARCHAR2(13)		地理位置

下面我们使用上述两个表，实现如下功能的SQL语句编写：

1. 将自己的信息插入emp表，具体数据要求如示例
2. 将所有人的薪水增加25%
3. 列出受雇日期早于他上级的所有员工
4. 列出超出部门平均员工数的那些部门名称及人数
5. 列出所有的部门和部门员工的所有信息，没有员工的部门也要显示
6. 列出每个部门中入职时间最早的那些员工的名字及工龄（与现在日期比）
7. 列出薪金（收入+提成，下同）比CLARK高的雇员名字、工种、部门名称、薪金
8. 列出薪金高于部门平均水平的所有雇员名字、薪金、部门名称
9. 列出所有的员工的名字、上级的名字、部门名称

---

预备工作

实验环境：Navicat 16 for MySQL
在数据库中建立这两个表
如图：

1. 员工信息表中引用了部门信息表的deptno，作为外键，所以应当先建立部门信息表dept
   
2. 建立员工信息表emp并设置外键
   
   
3. 添加一些事例数据进去
   
   

---

将自己的信息插入emp表，具体数据要求如示例

示例：

SQL语句

INSERT INTO emp
VALUES (9999,'somebody','analyst',7566,'2023-06-01',8000,0,20);

---

将所有人的薪水增加25%

SQL语句

update emp set sal = sal*1.25;

效果

---

列出受雇日期早于他上级的所有员工

SQL语句

select a.*
from emp a join emp b
on a.mgr = b.empno join dept d
on a.deptno = d.deptno
where a.hiredate < b.hiredate;

效果

---

列出超出部门平均员工数的那些部门名称及人数

SQL语句

SELECT d.dname, COUNT(*) AS employee_count
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY e.deptno
HAVING COUNT(*) > (
  SELECT AVG(emp_count)
  FROM (
    SELECT COUNT(*) AS emp_count
    FROM emp,dept
    WHERE emp.deptno = dept.deptno
    GROUP BY emp.deptno
  ) AS dept_emp
);

效果

---

列出所有的部门和部门员工的所有信息，没有员工的部门也要显示

SQL语句

select d.dname,e.* 
from emp e right join dept d on e.deptno=d.deptno;

效果

---

列出每个部门中入职时间最早的那些员工的名字及工龄（与现在日期比）

SQL语句

SELECT d.dname, e.ename, DATEDIFF(CURDATE(), e.hiredate) as work_age
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.hiredate IN (
  SELECT MIN(hiredate)
  FROM emp
  GROUP BY deptno
);

效果

---

列出薪金（收入+提成，下同）比CLARK高的雇员名字、工种、部门名称、薪金

SQL语句

SELECT e.ename, e.job, d.dname, (e.sal + IFNULL(e.comm,0)) as total_salary
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE (e.sal + IFNULL(e.comm,0)) > (
  SELECT (sal + IFNULL(comm,0))
  FROM emp
  WHERE ename = 'CLARK'
);

效果

---

列出薪金高于部门平均水平的所有雇员名字、薪金、部门名称

SQL语句

SELECT e.ename, (e.sal + IFNULL(e.comm,0)) as total_salary, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE (e.sal + IFNULL(e.comm,0)) > (
  SELECT AVG(e2.sal + IFNULL(e2.comm,0))
  FROM emp e2
  WHERE e2.deptno = e.deptno
);

效果

---

列出所有的员工的名字、上级的名字、部门名称

SQL语句

select e.ename,m.ename,d.dname 
from emp e,emp m,dept d
where e.mgr = m.empno and e.deptno = d.deptno;

效果

---