(六)练习Query查询语句

– 31.求入职日期相同的（年月日相同）的员工
SELECT a.hiredate,b.ename from emp a,emp b WHERE a.hiredate=b.hiredate and a.ename!=b.ename;

– 32.查询每个部门的最高工资
SELECT a.deptno,b.dname,IFNULL(max(a.sal),0) from emp a RIGHT JOIN dept b on a.deptno=b.deptno ORDER BY ;

– 33.查询每个部门，每种职位的最高工资
SELECT b.dname,a.job,max(a.sal) FROM emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY b.dname,a.job;

– 34.查询每个部门有多少人
SELECT b.dname,COUNT(ename) from emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY dname;

– 35.查询出king所在部门的部门号\部门名称\部门人数
select a.deptno,dname,count(*) from emp a ,dept b where a.deptno=b.deptno
and a.deptno=(
select deptno from emp where
ename =‘KING’
)
group by a.deptno

– 36.查询出king所在部门的工作年限最大的员工名字
select ename,hiredate from emp where deptno =
(
select deptno from emp where ename=‘KING’
) order by hiredate limit 0,1

– 37.查询出管理员工人数最多的人的名字和他管理的人的名字
select count(*) num ,ms_name from (
select a.ename , b.ename ms_name from emp a ,emp b where a.mgr=b.empno
) a group by ms_name order by num desc limit 0,1

– 38.查询出工资成本最高的部门的部门号和部门名称
select deptno ,dname from dept where deptno = (select deptno from emp group by deptno order by sum(sal) desc limit 0,1)

– 39.查询出工资不超过2500的人数最多的部门名称
select deptno ,dname from dept where deptno =(select deptno from emp where sal<=2500 group by deptno order by count(1) limit 0,1)

– 40.查询出没有下属员工的人的名字和他的职位
select ename ,job from emp where empno not in (select distinct mgr empno from emp where mgr is not null )
select ename ,job from emp a where not exists (select empno from emp b where a.empno=b.mgr)
–41.现在公司要给员工增加工龄工资,规则是:30*工作年限,请按以下格式显示下面结果
部门名称 员工姓名 原工资 增加额度 新工资
select ename,sal, sal+timestampdiff(year,hiredate,now())*30 newSal,
timestampdiff(year,hiredate,now())*30 addNUm from emp
– 42.针对dept和emp表,查询出下面格式的结果.(要求使用外联接,没有员工的部门名也要显示示,员工姓名如果是空值,要求用"不存在代替;如果工资是空值,要求用0代替")
部门名称 员工姓名 工资
select dname,ifnull(ename,‘not exists’)ename ,ifnull(sal,‘not exists’)sal from emp a right join dept b on a.deptno=b.deptno
– 43.查询出工资高于全体平均工资人数最多的部门编号,部门名称和员工姓名,工资.
select a.deptno,dname,ename,sal from emp a left join dept b on
a.deptno=b.deptno
where a.deptno=(
select deptno from emp where sal>(select avg(sal) from emp) group by deptno order by count(1) desc limit 0,1
)