1．列出至少有一个员工的所有部门。
select * from dept
where deptno in (select deptno from emp )
2．列出薪金比“SMITH”多的所有员工。
select *
from emp where sal>(select sal from emp where ename = 'smith')
3．列出所有员工的姓名及其直接上级的姓名。
select a.ename ,b.ename
from emp a left outer join emp b
on a.mgr = b.empno;
4．列出受雇日期早于其直接上级的所有员工。
select a.*
from emp a ,emp b
where a.mgr = b.empno and a.hiredate <b.hiredate
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select *
from emp right outer join dept
on emp.deptno = dept.deptno
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select *
from emp right outer join dept
on emp.deptno = dept.deptno and job ='clerk'
7．列出最低薪金大于1500的各种工作。
select min(sal ) minsal,job from emp group by job
having min(sal )>1500
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename
from emp
where deptno = (select deptno from dept where dname ='sales')
9．列出薪金高于公司平均薪金的所有员工。
select * from emp
where sal >(select avg(sal) from emp )
10．列出与“SCOTT”从事相同工作的所有员工。
select * from emp where job = (select job from emp where ename ='scott')
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename ,sal
from emp
where sal in(select sal from emp where deptno = 30)
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename ,sal
from emp
where sal>all(select sal from emp where deptno = 30);
13．列出在每个部门工作的员工数量、平均工资。
select count(1) ,avg(sal ) ,deptno
from emp group by deptno;
14．列出所有员工的姓名、部门名称和工资。
select ename ,dname ,sal
from emp join dept on emp.deptno = dept.deptno;
15．列出所有部门的详细信息和部门人数。
select * from dept ,(select count(0) countdeptno,deptno from emp group by deptno) a
where dept.deptno = a.deptno
16．列出各种工作的最低工资。
select job ,min(sal )
from emp group by job;
17．列出各个部门的MANAGER（经理）的最低薪金。
select min(sal ) ,deptno
from emp
where job ='manager'
group by deptno
18．列出所有员工的年工资,按年薪从低到高排序。
select sal*12 + ifnull(comm,0) yearsal from emp
order by yearsal
19.查出每个部门高于部门平均工资的员工名单
select *
from emp ,(select avg(sal) avgsal,deptno from emp group by deptno ) b
where emp.deptno = b.deptno and sal >avgsal;
20、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序。
select count(1),deptno from
(select emp.*
from emp ,(select avg(sal) avgsal,deptno from emp group by deptno ) b
where emp.deptno = b.deptno and sal >avgsal) c
group by deptno;
21.求每个部门工资不小于3000的人员的平均值；
select avg(sal ) ,deptno
from (select * from emp where sal>=3000) a
group by deptno
22、各部门在各年入职的平均工资
select avg(sal) ,deptno ,DATE_FORMAT(hiredate,'%Y')
from emp group by deptno ,DATE_FORMAT(hiredate,'%Y')
order by deptno ,DATE_FORMAT(hiredate,'%Y')
23.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno,dname,COUNT(1) 部门人数,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资
FROM emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname;
select dept. deptno ,dname ,count(1),max(sal),min(sal),sum(sal),avg(sal)
from emp,dept
where dept.deptno = emp.deptno
group by dept.deptno ,dname
24.查询入职日期比10部门任意一个员工晚的员工
姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where hiredate>
any(select hiredate from emp where deptno=10)
and deptno<>10;
select ename,hiredate
from emp
where hiredate>
(select min(hiredate) from emp where deptno=10)
and deptno<>10;
1.查询在1981到1982年入职的员工信息
select * from emp where hiredate between '01-1月-1981‘ and '31-12月-1982‘
网友评论