(六)练习Query查询语句

刺骨的言语ヽ痛彻心扉 2022-05-27 01:36 225阅读 0赞

– 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  
)