练习2

1.从emp表中查询出各个job员工的人数

2.从emp表中查询出员工的最高工资和最低工资的差距

3.从emp表中查询出工资比公司平均工高的所有员工的员工号，姓名，和工资。

4.从emp表中查询出和姓名中包含字母u的员工在相同不猛的员工的员工号，姓名

5.从emp表中查询出管理者是king的员工的姓名和工资。

6.从emp表中查询出和scott相同部门的员工的姓名和雇佣日期

7.从emp表中查询出姓名中含有字母a和e的员工的姓名

8.从emp表中查询出20号部门员工的岗位job

9.查询部门在new york的所有的员工的姓名，员工号，部门名称

10.查询每个部门中工资在2000元以上的员工的姓名，工资及其所在部门的名称，并以工资升序排序，如果工资相等，则按照员工姓名降序排序。

11.查询所有员工的员工编号和员工姓名及其主管编号和主管姓名（没有主管的员工信息也要查询出来）

12.查询所有部门的部门编号，部门名称及其部门中的员工姓名（没有员工的部门信息也要查询出来）


13.根据emp表中的ename字段统计各个部门的员工人数（包括没有员工的部门），并按部门名称降序排列

14.查询每个部门的部门名称和各部门员工中工资最低的工资（无员工的部门也需要查询出来）
注意：结果按部门升序排列

15 查询各部门的平均工资，并显示部门平均工资在1600以上的部门名称和平均工资数

16.查询各部门的最大工资数，并显示部门最大工资在2500元以上的部门名称和最大工资数（包括没有部门的）

17.查询各部门工资大于1800的所有员工的姓名，工资和所在部门的名称，并按照员工姓名升序排列（包括没有部门的）

18.查询各部门员工的入职日期在‘1981-5-1’和‘1982-5-1’之间的所有员工的姓名、入职时间和部门名称（包括没有部门的），并以员工姓名升序排序

19.统计各部门的员工总工资数，需显示部门名称和总工资数（包括无部门员工）

20.查询各部门中入职年份最早的员工并显示其所在部门名称和入职时间（包括无部门员工的最早入职时间）

21.统计现一共有多少个部门已开始投入运行（即有员工的部门）

22.查询本部门人数大于3人的部门名称和人数

23.查询部门所在地不在chicago的部门名称和平均工资

24.查询员工中1981-12-03入职的部门的所有员工信息

25
.查询各部门中的工资岗位都有哪些，显示其部门名称和职位名称（包括无员工的部门），并以部门名称和职位名称排序

26.按职位job统计各工资岗位的工资总和不少于6000的职位和工资总和数

27.查询各部门中雇佣时间最长的职员的所有信息

28.查询各部门中平均薪水最多的部门名

称（无部门的除外）

29.查询入职时间跟james同一个月入职的员工信息

1.select count(distinct ename) from emp group by job;
count(distinct ename)
2
4
3
1
4

2.select max(sal)-min(sal) from emp;
max(sal)-min(sal)
4200.00

3.select empno,ename from emp where sal>(select avg(sal) from emp);
empno ename
7566jones
7698black
7782clark
7788scott
7839king
7902ford

4.select empno,ename from emp where deptno in(select deptno from emp where ename like '%u%');

empno ename
7499allen
7521ward
7654martin
7698black
7844turner
7900james



5.select a.ename,a.sal,b.ename from emp a inner join emp b on a.mgr=b.empno where b.ename='king';
ename sal
jones2975
black2850
clark2450


6.select ename,hiredate from emp where deptno=(select deptno from emp where ename='scott');
ename hiredate
smith1980-12-17
jones1981-04-02
scott1987-04-19
adams1987-05-23
ford1981-12-03



7.select ename from emp where ename like '%a%' and ename like '%e%';
ename
allen
james


8.select job from emp where deptno=20;
job
clerk
manager
analyst
clerk
analyst


9.select ename,empno,dname from emp,dept where emp.deptno=dept.deptno and loc='new york';

ename empno dname
clark7782accountant
king7839accountant
miller7934accountant


10.select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and sal>2000 order by sal asc,ename desc;
ename empno dname
clark2450accountant
black2850sales
jones2975research
scott3000research
ford3000research
king5000accountant


11.select a.empno,a.ename,b.empno,b.ename from emp a left join emp b on a.mgr=b.empno;
empnoenameempno1ename1
7369smith7902ford
7499allen7698black
7521ward7698black
7566jones7839king
7654martin7698black
7698black7839king
7782clark7839king
7788scott7566jones
7839king
7844turner7698black
7876adams7788scott
7900james7698black
7902ford7566jones
7934miller7782clark



12.select dept.deptno,dname,group_concat(ename) from emp right join dept on emp.deptno=dept.deptno group by dname;
deptno dname ename
10accountantclark,king,miller
40operations
20researchsmith,jones,scott,adams,ford
30salesturner,allen,james,ward,martin,black


13.select dname,count(ename) from emp right join dept on emp.deptno=dept.deptno group by dname order by dname desc;
dnamecount(ename)
sales6
research5
operations0
accountant3



14.select dname,min(sal)from emp right join dept on emp.deptno=dept.deptno group by dname order by dept.deptno;
dname min(sal)
accountant1300
research800
sales950
operations



15.select dname,avg(sal) from emp join dept on emp.deptno=dept.deptno group by dname having avg(sal)>1600;
dname avg(sal)
accountant2916.6
66667
research2175


16.select dname,max(sal) from emp,dept where emp.deptno=dept.deptno group by emp.deptno having max(sal)>2500;
dna

me max(sal)
accountant5000
research3000
sales2850

17.select ename,sal,dname from emp left join dept on emp.deptno=dept.deptno where sal>1800 order by ename ;
ename sal dname
black2850sales
clark2450accountant
ford3000research
jones2975research
king5000accountant
scott3000research

18.select ename,hiredate,dname from emp left join dept on emp.deptno=dept.deptno where hiredate between '1981-5-1' and '1982-5-1' order by ename;

ename hiredate dname
black1981-05-01sales
clark1981-06-09accountant
ford1981-12-03research
james1981-12-03sales
king1981-11-17accountant
martin1981-09-28sales
miller1982-01-23accountant
turner1981-09-08sales


19.select dname,sum(sal) from emp left join dept on emp.deptno=dept.deptno group by dname;
dname sum(sal)
accountant8750.00
research10875.00
sales9400.00


20.select dname,hiredate from emp left join dept on emp.deptno=dept.deptno where hiredate=(select min(hiredate) from emp);
dnamehiredate
research1980-12-17


21.select count(distinct deptno) from emp ;
count(distinct deptno)
3

22.select dname,count(ename) from emp join dept on emp.deptno=dept.deptno group by dname having count(ename)>3;
dname count(ename)
research5
sales6



23.select dname,avg(sal) from emp,dept where emp.deptno=dept.deptno and loc!='chicago' group by dname;
dname avg(sal)
accountant2916.666667
research2175



24.select emp.* from emp where deptno in(select deptno from emp where hiredate='1981-12-03');
empnoenamejobmgrhiredatesalcommdeptno
7369smithclerk79021980-12-1780020
7499allensalesman76981981-02-20160030030
7521wardsalesman76981981-02-22125050030
7566jonesmanager78391981-04-02297520
7654martinsalesman76981981-09-281250140030
7698blackmanager78391981-05-01285030
7788scottanalyst75661987-04-19300020
7844turnersalesman76981981-09-08150030
7876adamsclerk77881987-05-23110020
7900jamesclerk76981981-12-0395030
7902fordanalyst75661981-12-03300020


25.select dname,group_concat(job) from emp right join dept on emp.deptno=dept.deptno group by dname order by dname,job;
dname group_concat(job)
accountantmanager,president,clerk
operations
researchclerk,clerk,analyst,manager,analyst
salessalesman,manager,salesman,salesman,salesman,clerk




26.select job,sum(sal)from emp group by job having sum(sal)>=6000;
job sum(sal)
analyst6000.00
manager8275.00


27.select emp.* from emp group by deptno having hiredate=MIN(hiredate)
empnoenamejobmgrhiredatesalcommdeptno
7782clarkmanager78391981-06-09245010
7369smithclerk79021980-12-1780020
7499allensalesman76981981-02-20160030030

28.select dname from emp join dept on emp.deptno=dept.
deptno group by dname order by avg(sal) desc limit 1 ;
dname
accountant


29.select * from emp where month(hiredate)=(select month(hiredate

) from emp where ename='james');
empnoenamejobmgrhiredatesalcommdeptno
7369smithclerk79021980-12-1780020
7900jamesclerk76981981-12-0395030
7902fordanalyst75661981-12-03300020

本文来源：https://www.bwwdw.com/article/ekxi.html