|
背景条件
已知有员工信息表（emp）和部门信息表（dept），具体表的信息如下：
员工信息表emp：
列名类型其他备注empnoDECIMAL(4)主键员工编号enameVARCHAR2(10)员工姓名jobVARCHAR2(9)工种mgrDECIMAL(4)管理者代码hiredateDATE雇佣时间salDECIMAL(7,2)月收入commDECIMAL(7,2)提成deptnoDECIMAL(2)外键所属部门编号
部门信息表dept：
列名类型其他备注deptnoDECIMAL(2)主键部门编号dnameVARCHAR2(14)部门名称locVARCHAR2(13)地理位置
下面我们使用上述两个表，实现如下功能的SQL语句编写：
将自己的信息插入emp表，具体数据要求如示例将所有人的薪水增加25%列出受雇日期早于他上级的所有员工列出超出部门平均员工数的那些部门名称及人数列出所有的部门和部门员工的所有信息，没有员工的部门也要显示列出每个部门中入职时间最早的那些员工的名字及工龄（与现在日期比）列出薪金（收入+提成，下同）比CLARK高的雇员名字、工种、部门名称、薪金列出薪金高于部门平均水平的所有雇员名字、薪金、部门名称列出所有的员工的名字、上级的名字、部门名称
预备工作
实验环境：Navicat 16 for MySQL 在数据库中建立这两个表 如图：
员工信息表中引用了部门信息表的deptno，作为外键，所以应当先建立部门信息表dept  建立员工信息表emp并设置外键   添加一些事例数据进去  
将自己的信息插入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 (
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;
效果
|