MySQL多表查询

MySQL多表查询

新增员工表emp和部门表dept
create table dept (dept1 int ,dept_name varchar(11)) charset=utf8;
create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int) charset=utf8;

 insert into dept values
(101,'财务'),
(102,'销售'),
(103,'IT技术'),
(104,'行政');

 insert into emp values
(1789,'张三',35,'1980/1/1',4000,101),
(1674,'李四',32,'1983/4/1',3500,101),
(1776,'王五',24,'1990/7/1',2000,101),
(1568,'赵六',57,'1970/10/11',7500,102),
(1564,'荣七',64,'1963/10/11',8500,102),
(1879,'牛八',55,'1971/10/20',7300,103);
(1668, '钱九', 64, '1963/5/4', 8000, 102);
(1724, '武十', 22, '2023/5/8', 1500, 103);
(1770, '孙二', 65, '1986/8/12', 9500, 101);
(18400, '苟一', 65, '1986/8/12', 1500, 101);

1.找出销售部门中年纪最大的员工的姓名

 SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售') AND age =
   (SELECT MAX(age) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售'))

2.求财务部门最低工资的员工姓名

SELECT a.name FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务')) a
 WHERE a.incoming = (SELECT MIN(incoming) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务'));

3.列出每个部门收入总和高于9000的部门名称

SELECT d.dept_name FROM dept d,
(SELECT dept2 FROM emp GROUP BY dept2 HAVING SUM(incoming) > 9000) e

4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

 SELECT a.name,d.dept_name FROM dept d,
  (SELECT e.NAME,e.dept2 FROM 
  (SELECT * FROM emp WHERE incoming BETWEEN 7500 AND 8500) e WHERE age = (SELECT MAX(age) FROM emp WHERE incoming BETWEEN 7500 AND 8500)) a
 WHERE d.dept1 = a.dept2 ;

5.找出销售部门收入最低的员工入职时间

SELECT worktime_start FROM 
(SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售')) a
WHERE a.incoming = (SELECT MIN(incoming) FROM emp WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '销售'));

6.财务部门收入超过2000元的员工姓名

SELECT NAME FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = '财务')) a
WHERE a.incoming > 2000;

7.列出每个部门的平均收入及部门名称

SELECT d.dept_name,a.c FROM dept d,
(select dept2,AVG(incoming) AS c FROM emp GROUP BY dept2) a
WHERE d.dept1 = a.dept2;

8.IT技术部入职员工的员工号

SELECT a.sid FROM (SELECT * FROM emp  WHERE dept2 = (SELECT dept1 FROM dept WHERE dept_name = 'IT技术')) a;