#0. 查詢最高工資及其對應員工姓名
select ename, sal from emp
where sal=(select max(sal) from emp);

#如果有多個員工都是最高工資下面的方式將失效
select ename, sal from emp ORDER BY sal desc limit 0, 1;

#補充1：能否不使用聚合函數查出最高工資及其對應員工姓名
select ename, sal from emp
where sal=(select sal from emp order by sal desc limit 0,1);

#補充2：既不用排序也不用聚合函數查出最高工資及其對應員工姓名
select ename, sal from emp
where sal not in 
(select distinct t1.sal from emp as t1
inner join emp as t2 on t1.sal(select avg(sal) from emp);

#5. 查詢薪水超過其所在部門平均薪水的員工的姓名、部門名稱和工資
#where寫法
select ename, dname, t3.sal from
(select eno, t1.dno, sal from emp as t1,
(select dno, avg(sal) as avgSal from emp group by dno) as t2
where t1.dno=t2.dno and sal>avgSal) as t3, emp as t4, dept as t5 
where t3.eno=t4.eno and t5.dno=t3.dno;

#inner join寫法
select ename, dname, t3.sal from
(select eno, t1.dno, sal from emp as t1 inner join
(select dno, avg(sal) as avgSal from emp group by dno) as t2
on t1.dno=t2.dno and sal>avgSal) as t3 inner join 
emp as t4 on t3.eno=t4.eno inner join 
dept as t5 on t5.dno=t3.dno;

#6. 查詢部門中薪水最高的人姓名、工資和所在部門名稱
select ename, dname, t3.sal from
(select eno, t1.dno, sal from emp as t1 inner join
(select dno, max(sal) as maxSal from emp group by dno) as t2
on t1.dno=t2.dno and sal=maxSal) as t3 inner join 
emp as t4 on t3.eno=t4.eno inner join 
dept as t5 on t5.dno=t3.dno;

#7. 哪些人是主管
select * from emp 
where eno in 
(select distinct mgr from emp);

select * from emp 
where eno=any(select distinct mgr from emp);

#補充：哪些人不是主管
select * from emp 
where eno not in (select distinct mgr from emp where mgr is not null);

#8. 求平均薪水最高的部門的名稱和平均工資
select dname as 部門名稱, avgSal as 平均工資 from
(select dno, avgSal
from (select dno, avg(sal) as avgSal from emp
group by dno) t1 
where avgSal=(select max(avgSal) from 
(select dno, avg(sal) as avgSal from emp group by dno) as t2)) as t3
inner join dept as t4 on t3.dno=t4.dno;

#9. 求薪水最高的前3名雇員
select * from emp order by sal desc limit 0,3;

#10.求薪水排在第4-6名雇員
select * from emp order by sal desc limit 3,3;