已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');
CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;
insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',7500,'105');
insert into emp VALUES ('1900','老十',64,'1990/8/1',2000,'106');
drop table dept ;
drop table emp ;
select * from dept;
select * from emp ;
1.列出每个部门的平均收入及部门名称;
select dept_name ,AVG(incoming) FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 GROUP BY dept_name;
2.财务部门的收入总和；
SELECT SUM(incoming) from dept right JOIN emp on dept.dept1=emp.dept2
Where dept_name=”财务”;
3.It技术部入职员工的员工号
SELECT sid from dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="IT技术";
4.财务部门收入超过2000元的员工姓名
emp incoming>2000
dept 财务
select NAME FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="财务" and emp.incoming>2000;
5.找出销售部收入最低的员工的入职时间；
emp
dept
max
SELECT woektime_start FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE dept_name="销售" ORDER BY emp.incoming asc limit 1;
SELECT woektime_start FROM (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) a
WHERE incoming=(SELECT MIN(b.incoming)
from (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) b
WHERE dept_name ="销售") and a.dept_name ="销售"
6.找出年龄小于平均工资的员工的姓名，ID和部门名称 ；
SELECT sid,name,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 where age<(SELECT avg(incoming) FROM emp);
7.列出每个部门收入总和高于9000的部门名称
select dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2GROUP BY dept_name HAVING sum(incoming)>9000 ;
Select s.dept_name from (select dept_name,sum(incoming) from dept join emp on dept.dept1=emp.dept2 GROUP BY dept_name having sum(incoming)>9000)s
8.查出财务部门工资少于3800元的员工姓名
财务 dept
incoming emp
name
<
select name from dept join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming<3800;
9.求财务部门最低工资的员工姓名；
min
dept dept_name
emp incoming min
SELECT name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE dept_name="财务" ORDER BY incoming asc LIMIT 1;
SELECT * FROM (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) a
WHERE incoming=(SELECT MIN(b.incoming) from (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) b WHERE dept_name ="财务") and a.dept_name ="财务"
10.找出销售部门中年纪最大的员工的姓名
1.SELECT name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2 WHERE dept_name="销售" ORDER BY age desc LIMIT 1;
2.SELECT Name FROM (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) a
WHERE age=(SELECT Max(b.age)
from (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) b
WHERE dept_name ="销售") and a.dept_name ="销售"
3.select name from dept join emp on dept.dept1=emp.dept2 where dept_name="销售" and age=(select max(age) from dept join emp on dept.dept1=emp.dept2 where dept_name="销售");
4.select name from dept left join emp on dept.dept1=emp.dept2
where(age,dept_name)IN
(select max(age),dept_name from dept left join emp on dept.dept1=emp.dept2 WHERE dept_name="销售");
11.求收入最低的员工姓名及所属部门名称：
SELECT name,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE incoming=(select min(incoming) from dept LEFT JOIN emp ON dept.dept1=emp.dept2
);
12.求李四的收入及部门名称
SELECT incoming,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE name="李四";
13.求员工收入小于4000元的员工部门编号及其部门名称
SELECT dept2,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE incoming<4000;
14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
SELECT NAME,incoming,dept_name FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
GROUP BY incoming desc ;
Select name,dept_name ,incoming from dept left join emp on dept.dept1=emp.dept2 where(incoming,dept_name)IN
(select max(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept_name) ORDER BY incoming desc ;
表子查询
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
SELECT NAME,incoming,sid FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
where dept_name="财务" ORDER BY incoming desc LIMIT2;
16.查询财务部低于平均收入的员工号与员工姓名：
SELECT sid,name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务"
and incoming<(SELECT avg(incoming) FROM emp);
17.列出部门员工数大于1个的部门名称；
SELECT dept_name FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
GROUP BY dept_name HAVING count(*)>1;
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
SELECT age,dept2 FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
WHERE incoming BETWEEN 3000 and 7500;
SELECT age,dept2 FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
WHERE incoming > 3000 and incoming <=7500;
19.求入职于20世纪70年代的员工所属部门名称；
SELECT dept_name FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
WHERE woektime_start BETWEEN 1970 and 1980;
20.查找张三所在的部门名称；
SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 where name="张三";
21.列出每一个部门中年纪最大的员工姓名，部门名称；
SELECT name,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2
where age=(SELECT max(age) FROM emp);
SELECT name,dept_name FROM dept left join emp on dept.dept1=emp.dept2 where (age ,dept_name)in
(select max(age),dept_name from dept left join emp on dept.dept1=emp.dept2 GROUP BY dept_name);
22.列出每一个部门的员工总收入及部门名称；
SELECT dept_name,SUM(incoming) FROM dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name ;
23.列出部门员工收入大于7000的员工号，部门名称；
SELECT sid,dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE incoming>7000;
24.找出哪个部门还没有员工入职；
SELECT dept_name FROM dept LEFT JOIN emp ON dept.dept1=emp.dept2
WHERE woektime_start is NULL;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select * from emp ORDER BY dept2 desc,woektime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
1.SELECT NAME,sid FROM dept RIGHT JOIN emp ON dept.dept1=emp.dept2
where dept_name="财务" ORDER BY incoming desc LIMIT 1;
2.SELECT Name,sid FROM (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) a
WHERE incoming=(SELECT Max(b.incoming)
from (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) b
WHERE dept_name ="财务") and a.dept_name ="财务"
3.select name.,sid from dept join emp on dept.dept1=emp.dept2 where dept_name="财务" and incoming=(select max(incoming) from dept join emp on dept.dept1=emp.dept2 where dept_name="财务");
4.select name,sid from dept left join emp on dept.dept1=emp.dept2
where(incoming,dept_name)IN
(select max(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 WHERE dept_name="财务");
5.select c.name,sid from (SELECT * FROM dept a INNER JOIN emp b on a.dept1=b.dept2)c where c.dept_name="财务"and incoming=(SELECT max(incoming) FROM dept a INNER JOIN emp b on a.dept1=b.dept2 where dept_name="财务");
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
1.SELECT name,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2
WHERE incoming BETWEEN 7500 and 8500 ORDER BY age desc LIMIT 1;
2.select name,dept_name from dept join emp on dept.dept1=emp.dept2
where incoming>=7500 and incoming <=8500 and age in
(select max(age) from dept join emp on dept.dept1=emp.dept2 where incoming>=7500 and incoming <=8500)；
3.SELECT Name,dept_name FROM (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) a
WHERE incoming between 7500 and 8500 and age=(SELECT Max(b.age)
from (SELECT * FROM dept INNER JOIN emp on dept.dept1=emp.dept2) b where incoming between 7500 and 8500);