SQL_查询练习

– 查询各部门号和入职日期早于 ward 的人数.
select hiredate from emp where ename =’WARD’

select deptno,count(*) from emp where hiredate < (select hiredate from emp where ename =’WARD’) group by deptno;
– 以emp表为主, 查询各部门人数,部门号,部门名称,部门工资和奖金之和.
select count(*),p.deptno,dname,sum(ifnull(comm,0)+sal)
from emp p LEFT JOIN dept d
on p.deptno = d.deptno
group by p.deptno
;

– 查询 入职日期早于直接领导 且 月工资 低于领导的
– 员工名字 员工工资 员工日期 领导名字 领导工资 领导日期
select yuangong.ename,yuangong.sal,yuangong.hiredate ,lingdao.ename,lingdao.sal,lingdao.hiredate
from emp yuangong ,emp lingdao
where yuangong.mgr = lingdao.empno
and yuangong.hiredate