SQL题目分析：使用 SQL 分析员工和部门数据

        在企业资源规划（ERP）系统中，员工和部门的数据管理是核心组成部分。本文通过一系列 SQL 查询示例，展示了如何使用 SQL 来分析和查询员工的工资、部门信息以及它们之间的关系。这些查询不仅涵盖了基础的数据检索，还包括了复杂的数据分析，如聚合函数、连接操作和子查询等高级 SQL 技巧的应用。

一、数据库表的创建与数据导入

首先，我们创建了两个表：emp 表用于存储员工信息，包括员工编号、姓名、职位、经理编号、雇佣日期、工资等字段；dept 表用于存储部门信息，包括部门编号、部门名称和位置。通过插入具体的数据，我们为后续的查询提供了数据基础。

数据库表的创建

创建员工表

CREATE TABLE emp(
	empno	INT,
	ename	VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm	decimal(7,2),
	deptno	INT
);

创建部门表

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc			varchar(13)
);

数据导入

导入员工表数据

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-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',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-05-23',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);

导入部门表数据

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');

二、员工和部门数据的查询分析

接下来，我们通过一系列查询来分析员工和部门的数据：

1.查询工资高于特定员工的员工列表

工资高于JONES的员工

使用子查询来首先确定基准工资，然后查询所有工资高于这个基准的员工。

select * from emp where sal > 
(select emp.sal from emp where ename = 'JONES');

2.查询与特定员工同一部门的所有员工

查询与SCOTT同⼀个部门的员工

通过连接操作和子查询，找出与指定员工在同一部门的所有员工。

select * from emp where deptno = 
(select emp.deptno from emp where ename = 'SCOTT') and ename != 'SCOTT';

3.查询工资高于特定部门所有人的员工信息

工资⾼于30号部门所有人的员工信息

利用聚合函数 MAX 来确定特定部门的最高工资，然后查询所有工资高于这个数值的员工。

select * from emp where sal > 
(select max(emp.sal) from emp where deptno = 30);

4.查询工资大于特定部门平均工资的非该部门员工信息

查询工资大于10号部门的平均工资的非10号部门的员工信息

结合 AVG 聚合函数和 HAVING 子句，找出工资高于特定部门平均工资的员工，但排除该部门的员工。

select * from emp where sal > 
(select avg(emp.sal) from emp where deptno = 10) and deptno != 10;

5.查询与特定员工同部门的同事信息

查询与7369同部门的同事信息

通过子查询和连接操作，找出与特定员工在同一部门的所有同事。

select * from emp where deptno = 
(select emp.deptno from emp where empno = 7369) and empno != 7369;

6.查询员工的姓名、工资及其部门的平均工资

结合连接操作和子查询，展示每个员工的姓名、工资以及他们所在部门的平均工资。

select emp.ename 姓名, emp.sal 工资, emp.deptno 部门, dept.avgSal 平均工资
from emp
join (select emp.deptno, avg(emp.sal) avgSal 
    from emp group by emp.deptno) dept
on emp.deptno = dept.deptno;

7.查询每个员工的信息及其部门的平均工资、工资之和、部门人数

这是一个更复杂的查询，它不仅展示了员工的详细信息，还包括了部门的平均工资、工资总和和部门人数。

select emp.*, dept.avgSal 平均工资, dept.sumSal 工资之和, dept.sumDept 部门人数
from emp
join (select emp.deptno, avg(emp.sal) avgSal, 
    sum(emp.sal) sumSal, count(emp.deptno) sumDept
    from emp
    group by emp.deptno) dept
on emp.deptno = dept.deptno;

8.查询平均工资大于特定部门的部门号和平均工资

查询平均工资大于30号部门的平均工资的部门号，和平均工资

使用 HAVING 子句来筛选出平均工资高于特定部门的部门。

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > (select avg(emp.sal) from emp where deptno = 30);

9.查询所有员工信息以及工资总和

通过子查询来计算所有员工的工资总和，并展示每个员工的信息。

select emp.*, (select sum(emp.sal) from emp) 工资总和 from emp;

1.查询所有员工人数不少于特定人数的部门信息

查询所有员工人数不少于3人的部门信息

使用 GROUP BY 和 HAVING 子句来找出员工人数满足特定条件的部门。

select * from dept where deptno in (
    select emp.deptno from emp group by emp.deptno having count(*) >= 3);

11.查询员工编号、姓名、部门编号、工资、本部门工资总和

结合子查询和连接操作，展示每个员工的详细信息以及他们所在部门的工资总和。

select emp.empno 员工编号, emp.ename 姓名, 
       emp.deptno 部门编号, emp.sal 工资, sum_sal.sumSal 本部门工资总和
from emp
join (
    (select emp.deptno, sum(emp.sal) sumSal 
       from emp group by emp.deptno)
    ) sum_sal
on emp.deptno = sum_sal.deptno;

12.查询每个员工的姓名和上级领导的姓名

通过连接操作，展示每个员工及其上级领导的姓名。

select empEname.ename 员工的姓名, empMar.ename 上级领导的姓名
from emp empEname
join emp empMar on empEname.mgr = empMar.empno;

13.查询没有员工的部门

使用 NOT EXISTS 子查询来找出没有任何员工的部门。

select dept.deptno from dept where not exists(
    select 1 from emp where emp.deptno = dept.deptno);

三、总结

        通过本文的 SQL 查询示例，我们可以看到 SQL 在数据分析中的强大功能。这些查询不仅帮助我们理解员工和部门之间的复杂关系，还展示了如何使用 SQL 进行有效的数据检索和分析。掌握这些技能对于任何数据库管理员或数据分析师来说都是极其宝贵的。希望本文能够帮助你更好地理解和应用 SQL，提高你的数据处理能力。