mysql查询练习2
1. 查询20号部门的所有员工信息：
select * from emp where deptno = 20;
2. 查询奖金（COMM）高于工资（SAL）的员工信息：
select * from emp where comm >sal
3. 查询奖金高于工资的20%的员工信息:
select * from emp where comm > sal*0.2;
4. 查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息:
select * from emp where (deptno =10 and job = 'manager') or (deptno =20 and job = 'clerk');
5. 查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息：
select * from emp where job not in ('manager','clerk') and sal>=2000;
6. 查询没有奖金或奖金低于100的员工信息:
select * from emp where comm is null or comm <100;
7. 查询员工工龄大于或等于10年的员工信息:
select * from emp where TIMESTAMPDIFF(year, hiredate,now())>=10
8. 查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
select concat(upper(substring(ename,1,1)) , lower(substring(ename,2))),ename from emp;
9. 查询在2月份入职的所有员工信息:
select * from emp where DATE_FORMAT(hiredate,'%m') = 2;
10.显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
select ename , DATE_FORMAT(hiredate,'%Y'),DATE_FORMAT(hiredate,'%m')
from emp
order by DATE_FORMAT(hiredate,'%m'), DATE_FORMAT(hiredate,'%Y');
11. 查询'JONES'员工及所有其直接、间接下属员工的信息:
select a.ename ,b.ename,c.ename
from emp a
join emp b
on(a.empno = b.mgr)
left outer JOIN emp c
on(b.empno = c.mgr)
where a.ename ='JONES';
12. 查询SCOTT员工及其直接、间接上级员工的信息：
select a.ename ,b.ename,c.ename
from emp a
join emp b
on(a.empno = b.mgr)
left outer JOIN emp c
on(b.empno = c.mgr)
where c.ename ='scott';
13. 试用SQL语言完成下列查询(多表查询)：
14. 查询各个人的详细信息以及其部门人数、部门平均工资:
select dname ,emp.deptno ,loc ,count(1) ,avg(sal)
from dept ,emp
where dept.deptno = emp.deptno
group by dname ,emp.deptno ,loc ;
select emp.*,deptcount,avgsal
from emp ,(select deptno ,count(1) deptcount,avg(sal) avgsal from emp group by deptno) b
where emp.deptno = b.deptno;
15. 查询10号部门员工以及领导的信息:
select a.* ,b.*
from emp a left outer join emp b
on a.mgr = b.empno
where a.deptno = 10
16. 查询工资为某个部门平均工资的员工信息:
select * from emp
where sal in(select avg(sal) from emp group by deptno)
17. 统计各个工种的人数与平均工资:
select job,count(1),avg(sal)
from emp
group by job;
18. 统计每个部门中各个工种的人数与平均工资:
select deptno,job,count(1),avg(sal)
from emp
group by deptno,job;
19. 查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。
select ename ,FROM_DAYS(TIMESTAMPDIFF( day,hiredate,now())) from emp
-20. 查询人数最多的部门信息：
select * from dept
where deptno
in(select deptno from emp group by deptno having count(1) =
(select max(count) from (select count(1) count from emp group by deptno)a))
21. 部门平均薪水最高的部门编号:
select c.deptno from
(select max(avgsal) maxavgsal from
(select deptno,avg(sal)avgsal from emp group by deptno)a) b,(select deptno,avg(sal)avgsal from emp group by deptno)c
where maxavgsal = c.avgsal
22.比普通员工的最高薪水还要高的经理人名称:
select ename
from emp where empno in(select mgr from emp)
and sal >(
select max(sal) from (select * from emp where empno not in(select mgr from emp where mgr is not null))a)
23. 查询所有员工工资都大于1000的部门的信息:
select * from dept
where deptno in(select deptno from (select min(sal)minsal,deptno from emp group by deptno )a where minsal>1000)
24. 查询所有员工工资都大于1000的部门的信息及其员工信息:
select * from dept ,emp
where emp.deptno in(select deptno from (select min(sal)minsal,deptno from emp group by deptno )a where minsal>1000)
and dept.deptno = emp.deptno;
25. 查询所有员工工资都在900~3000之间的部门的信息:
select * from dept where deptno not in (
select deptno from emp where sal <900 or sal>3000 and deptno is not null) and deptno in (select distinct deptno from emp);
26.查询所有工资都在900~3000之间的员工所在部门的员工信息:
select * from emp where deptno not in (
select deptno from emp where sal <900 or sal>3000 and deptno is not null) and deptno in (select distinct deptno from emp);
27. 查询每个员工的领导所在部门的信息：
select * from dept where deptno in(select deptno from emp where empno in (select mgr from emp))
28. 查询工作等级为2级，1985年以后入职的工作地点为DALLAS的员工编号、姓名和工资：
select empno ,ename ,sal
from emp ,salgrade
where hiredate >'1985-12-31' and deptno =(select deptno from dept where loc = 'dallas')
and sal between losal and hisal and grade = 2
29..查询平均工资最低的部门名称
①多表：
select dname
from dept,
(select deptno, avg(sal) deptnoavgsal from emp group by deptno) b,
(select min(deptnoavgsal) mindeptnoavgsal
from (select avg(sal) deptnoavgsal from emp group by deptno)d) c
where dept.deptno = b.deptno
and b.deptnoavgsal = c.mindeptnoavgsal;
②子查询：
select dname
from dept
where deptno =
(select deptno
from emp
group by deptno
having avg(sal) = (select min(avgsal) from (select avg(sal) avgsal from emp group by deptno)a));
select * from emp;
30.查询和Smith同一个领导的其他员工的信息
①多表：
select dname
from dept,
(select deptno, avg(sal) deptnoavgsal from emp group by deptno) b,
(select min(deptnoavgsal) mindeptnoavgsal
from (select avg(sal) deptnoavgsal from emp group by deptno)) c
where dept.deptno = b.deptno
and b.deptnoavgsal = c.mindeptnoavgsal;
②子查询：
select dname
from dept
where deptno =
(select deptno
from emp
group by deptno
having avg(sal) = (select min(avg(sal)) from emp group by deptno))
31.查询比本部门平均工资高的人员信息
SELECT *
FROM EMP, (SELECT AVG(SAL) AVGSAL, DEPTNO FROM EMP GROUP BY DEPTNO) A
WHERE EMP.DEPTNO = A.DEPTNO
AND SAL > AVGSAL;
SELECT *
FROM EMP A
WHERE SAL > (SELECT AVG(SAL) FROM EMP B WHERE A.DEPTNO = B.DEPTNO);
32.查询比Jones工资高的员工信息
①多表：select *
from emp
where sal>
(select sal
from emp
where ename ='jones');
②子查询：select *
from emp a join emp b
on a.sal>b.sal
where b.ename='jones';
33.查询一个比Smith工资高，同时岗位和Jones相同的人的领导的部门的平均工资。
select avg(sal)
from emp
where deptno=(select deptno from emp where empno in(select mgr from emp
where sal>(select sal from emp where ename ='smith')
and
job =(select job from emp where ename='jones')))；
34.查询最高的部门平均工资
select max（avgsal）
from(select avg(sal) avgsal,deptno from emp group by deptno) a;
35，查询比20部门平均工资高的人员信息
select *
from emp
where sal>
(select avg(sal) from emp where deptno =20);
36.查询工资比本部门平均工资高的人
select ename,avg(sal)
from emp,(select avg(sal) avgsal,deptno from emp group by deptno) a
where sal>avgsal and emp.deptno=a.deptno;
37.查询每个岗位工资最高的人员信息
select *
from emp,(select max(sal) max ,job from emp group by job ) a
where sal=a.max
and emp.job=a.job
38.查询每个领导手下工资最低的员工信息
select *
from emp,
(select min(sal) minsal,mgr from emp group by mgr) a
where sal = a.minsal and a.mgr = emp.mgr
39.查询比10部门所有人工资高的人员信息
select *
from emp where sal>
all(select sal from emp where deptno=20);
