-- 一般做SQL题，需要注意三个问题
-- 1.有哪些表，表代表什么含义
-- 2.表里有哪些字段
-- 3.表的关联，及关联关系。

create table dept (
	id int(10) primary key,
	dname varchar(10)
);
insert into dept values (1,'事业部');
insert into dept values (2,'销售部');
insert into dept values (3,'技术部');

create table emp (
	id int(10) primary key,
	ename varchar(10),
	job varchar(10),
	mgr varchar(10),
	sal float,
	deptno int(10)
);
insert into emp values (1,'jacky','clerk','tom','1000',1);
insert into emp values (2,'tom','clerk','','2000',1);
insert into emp values (7,'biddy','clerk','','2000',1);
insert into emp values (3,'jenny','sales','pretty','600',2);
insert into emp values (4,'pretty','sales','','800',2);
insert into emp values (5,'buddy','jishu','canndy','1000',3);
insert into emp values (6,'canndy','jishu','','1500',3);
select * from dept;
select * from emp;

-- 8 对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
select ename,mgr from emp where mgr != "";

-- 9 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select * from dept d left join emp e on d.id=e.deptno and e.job='clerk';


-- 之前是方便大家理解：首先生成所有的数据，然后再删除不符合条件的数据。真实情况不需要这样操作
select * from dept d left join emp e on d.id=e.deptno;

-- 10 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

select e1.deptno,e1.ename,e1.sal from emp e1 inner join (
	-- 先生成一张部门平均薪资水平表
	select deptno,avg(sal) as avg_sal from emp group by deptno
) avg_table on e1.deptno=avg_table.deptno and e1.sal > avg_table.avg_sal
order by e1.deptno;


-- 11 对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序

select e1.deptno,count(*) from emp e1 inner join (
	-- 先生成一张部门平均薪资水平表
	select deptno,avg(sal) as avg_sal from emp group by deptno
) avg_table on e1.deptno=avg_table.deptno and e1.sal > avg_table.avg_sal
group by e1.deptno
order by e1.deptno