数据库相关习题（二）

【数据库内容】

表一：数据库scott结构

 表二：dept表的内容

表三：emp表的内容 

【二】以下练习针对部门员工表，请导入scott.sql（员工信息表）的数据
1. 打印入职时间超过38年的员工信息
select * from emp where extract(year from now())-extract(year from hiredate)>38;
2. 把hiredate列看做是员工的生日,求本月过生日的员工
select ename from emp where extract(month from hiredate)=extract(month from now());
3. 把hiredate列看做是员工的生日,求下月过生日的员工
select ename from emp where extract(month from hiredate)=extract(month from now())+1;
4. 求1980年下半年入职的员工
select ename from emp where extract(year from hiredate)=1980 and extract(month from hiredate)>6;
5. 请用两种的方式查询所有名字长度为4的员工的员工编号,姓名
方式一：select ename from emp where length(ename)=4;//仅适用于英文字符的长度判断
              select ename from emp where char_length(ename)=4;
方式二：select ename from emp where ename like '____';//效率低
6. 显示各种职位的最低工资
select job,min(sal) from emp group by(job);
7. 求1980年各个月入职的的员工个数
select extract(month from hiredate),count(ename) from emp where extract(year from hiredate)=1980 group by extract(month from hiredate);
8. 查询每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
9. 查询每个部门,每种职位的最高工资
select deptno,job,max(sal) from emp group by deptno,job;
10. 查询各部门的总工资和平均工资
select deptno,sum(sal),avg(sal) from emp group by deptno;
11. 查询10号部门,20号部门的平均工资（尝试用多种写法）
方式一：select deptno,avg(sal) from emp group by deptno having deptno in(10,20);//不推荐
              select deptno,avg(sal) from emp group by deptno having (deptno=10) or (deptno=20);//不推荐
方式二：select deptno,avg(sal) from emp group by deptno limit 2;//不推荐，不通用，效率低
方式三：select deptno,avg(sal) from emp where (deptno=10) or (deptno=20) group by deptno ;//推荐
              select deptno,avg(sal) from emp where deptno  in(10,20) group by deptno ;//推荐
方式四：select deptno,avg(sal) from emp where deptno between 10 and 20 group by deptno;
方式五：select deptno,avg(sal),
              case
              when deptno=10 then avg(sal)
              when deptno=20 then avg(sal)
              end from emp group by deptno limit 2;
12. 查询平均工资高于2000元的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
13. 统计公司里经理的人数
select job,count(ename) from emp where job='manager';
14. 查询工资最高的3名员工信息
select * from emp order by sal desc limit 3;
15. 查询工资由高到低第五到第十的员工信息
select * from emp order by sal desc limit 4,6;