Mysql学习03

– 查询员工的姓名和年薪((月薪+补贴)*13)（处理空值）
select ename, (sal+ifnull(comm,0))*13 as annsal
from tb_emp order by annsal desc;

– 查询有员工的部门的编号和人数
select dno, count(eno) from tb_emp group by dno with rollup;

– 查询所有部门的名称和人数
select dname, ifnull(total,0) from tb_dept t1 left join
(select dno, count(eno) as total from tb_emp group by dno) t2
on t1.dno=t2.dno;

– 查询月薪最高的员工(Boss除外)的姓名和月薪
select ename, sal from tb_emp
where sal=(
select max(sal) from tb_emp where mgr is not null
);

– 查询薪水超过平均薪水的员工的姓名和月薪
select @a:=(select avg(sal) from tb_emp);

select @a;

select ename, sal, round(sal-@a,2) from tb_emp
where sal>@a;

– 查询薪水超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename, t1.dno, sal from tb_emp t1 inner join
(select dno, avg(sal) as avgsal from tb_emp group by dno) t2
on t1.dno=t2.dno where sal>avgsal;

– 查询