数据库相关习题（二）

矫情吗；* 2021-09-16 03:50 412阅读 0赞

【数据库内容】

表一：数据库scott结构

![70][]

表二：dept表的内容

![70 1][]

表三：emp表的内容

![70 2][]

【二】以下练习针对部门员工表，请导入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;

[70]: /images/20210811/b76bfbbebb484554b57ec9a454c6df36.png
[70 1]: /images/20210811/38b738079165423e8ea46220da301eb9.png
[70 2]: /images/20210811/610c105254924b4eaa61b5ecf7b72f47.png