SQL综合实战

create database if not exists `Firm`;

use Firm;

# 职工表
CREATE TABLE if not exists emp(
	empno INT comment '编号',
	ename VARCHAR(50) comment '姓名',
	job VARCHAR(50) comment '职务',
	mgr INT comment'上级',
	hiredate DATE comment '入职日期',
	sal DECIMAL(7,2) comment '薪资',
	comm DECIMAL(7,2) comment '奖金',
	deptno INT comment '部门编号'
);

# 新增数据
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);

# 部门表
CREATE TABLE dept(
	deptno INT comment '部门编号',
	dname VARCHAR(14) comment '部门名称' ,
	loc VARCHAR(13) comment '坐落城市'
);

# 新增数据
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、查询出高于10号部门的平均工资的员工信息
    # 1.查询10部门的平均工资的员工信息
select avg(sal) from emp where deptno = 10;
    # 2.查询出高于10号部门的平均工资的员工信息
select * from emp
where sal > (select avg(sal) from emp where deptno = 10);

# 2、查询出比10号部门任何员工薪资高的员工信息
    # 1.得到10部门最高薪资的员工信息
select max(sal) from emp where deptno = 10;
    # 2.得出结论 查询出比10号部门任何员工薪资高的员工信息
select * from emp
where sal > (select max(sal) from emp where deptno = 10);

# 3、和10号部门同名同工作的员工信息
    # 1.得到10部门的姓名和工作
select ename ,job from emp where deptno = 10;
    # 2。查询和10号部门同名同工作的员工信息
select * from emp where (ename,job) in (
    (SELECT ename,job FROM emp WHERE deptno = 10)
) and deptno != 10;

# 4、获取员工的名字和部门的名字
    # 1.查询员工姓名和部门信息
select deptno as 部门,ename as 员工姓名 from emp;
    # 2.得出结果
select (select dname from dept where emp.deptno = dept.deptno) ,ename as 员工姓名 from emp;

# 5、查询emp表中经理信息
    # 1.查询emp表中经理信息
select mgr from emp;
    #2.得出结果
SELECT * FROM emp as e
inner join dept d
on e.deptno = d.deptno
where mgr;

# 6、薪资高于10号部门平均工资的所有员工信息
    # 1.查询10号部门所有员工的平均工资信息
select avg(sal) from emp where deptno = 10;
    # 2.查询薪资高于10号部门平均工资的所有员工信息
select * from emp where sal > (select avg(sal) from emp where deptno = 10) ;

# 7、有哪些部门的平均工资高于30号部门的平均工资
    #1.查询30号部门的平均工资
select avg(sal) from emp where deptno = 30;
    #2.查询高于30号部门的平均工资的部门平均工资
select deptno
from emp
group by deptno
having avg(sal) > (
    select avg(sal) from emp where deptno = 30
);

# 8、查询工资 > JONES工资
    #1.查询 JONES 的工资信息
select sal from emp where ename = 'JONES';
    #2.查询工资大于 JONES 的工资信息
select sal
from emp
where sal > (
    select sal from emp where ename = 'JONES'
);

# 9、查询与SCOTT同一个部门的员工
    #1.查询 SCOTT 的所处部门信息
select deptno from emp where ename = 'SCOTT';
    #2.查询与SCOTT同一个部门的员工信息
select ename as 员工 ,deptno as 部门
from emp
where deptno = (
    select deptno from emp where ename = 'SCOTT'
);

# 10、工资高于30号部门所有人的员工信息
    #1.查询30号部门的工资信息
select sal from emp where deptno = 30;
    #2.查询工资大于30号部门的员工信息
select *
from emp
where sal > all(
    select sal from emp where deptno = 30
);

# 11、查询工作和工资与MARTIN完全相同的员工信息
    #1.查询名叫 MARTIN 的员工的工作与工资
select job,sal from emp where ename = 'MARTIN';
    #2.查询查询工作和工资与 MARTIN 完全相同的员工信息
select * from emp where (job,sal) = (
    select job,sal from emp where ename = 'MARTIN'
);

# 12、查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
    #1.将职工表和部门表链接起来
select * from emp,dept where emp.deptno = dept.deptno;
    #2.查询编号为7788的所有信息
select * from emp,dept where emp.deptno = dept.deptno and empno = 7788;
    #3.显示题目所需的信息
select e.ename as 员工名称 ,e.sal as 员工工资 ,e.deptno as 部门名称 ,d.loc as 部门地址
from emp as e
inner join dept d
on e.deptno = d.deptno
where empno = 7788;

# 13、查询出高于本部门平均工资的员工信息
    #1.分组统计每个部门的平均工资
    SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
    #2.得出相应的结果
select *
from emp as e1
where sal > (
    select avg(sal) from emp as e2 where e1.deptno = e2.deptno GROUP BY e2.deptno
);

# 14、列出达拉斯加工作的人中,比纽约平均工资高的人
    #1.容易理解的方法 —— 列出 达拉斯加 工作的人的信息
SELECT * FROM emp WHERE deptno = (
    SELECT deptno FROM dept WHERE loc = ‘DALLAS’
);
    #2.查询纽约的平均工资
select avg(sal)
from emp as e
inner join dept d
on e.deptno = d.deptno
where d.loc = 'NEW YORK';
    #3.得出结果
SELECT *
FROM emp
WHERE deptno = (
    SELECT deptno FROM dept WHERE loc = ‘DALLAS’
) AND sal > (
    SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'NEW YORK'
    )
);
    #4.高效率写法
     # 1.将职工表和部门表连接起来
select * from emp inner join dept;
     #2.查询纽约的平均工资
select avg(sal)
from emp as e
inner join dept d
on e.deptno = d.deptno
where d.loc = 'NEW YORK';
     #3.列出 达拉斯加 工作的人的信息
select * from dept as d inner join emp e
on d.deptno = e.deptno
where d.loc = 'DALLAS';
     #4.得出结果
select *
from emp inner join dept
where sal > (
    select avg(sal)
    from emp as e inner join dept d
    on e.deptno = d.deptno where d.loc = 'NEW YORK'
) AND emp.deptno = dept.deptno and dept.loc = 'DALLAS';

# 15、查询7369员工编号,姓名,经理编号和经理姓名
select e.empno as 员工编号 , e.ename as 姓名 , e.mgr as 经理编号 ,e2.ename
from emp as e inner join emp as e2
on e.mgr = e2.empno
where e.empno = 7369;

# 16、查询出各个部门薪水最高的员工所有信息
    #1.分组统计每个部门员工最高的薪资是多少
SELECT MAX(sal),deptno FROM emp GROUP BY deptno;
    #2.得出结果
select *
from emp as e1
where e1.sal = (
    select max(sal) from emp as e2 where e1.deptno = e2.deptno group by e2.deptno
);