Oracle习题练习

select * from emp;

1. 查询sales部门下有哪些职位

select distinct job from emp e join dept d on e.deptno= d.deptno where d.dname='SALES';

2. 列出至少有一个雇员的所有部门信息

select * from dept where deptno in(select deptno from emp);
 select * from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);  

3. 查询共有多少个工种

select count(*) from (select distinct job from emp);
  select count(distinct job) from emp;

4. 检索emp表中工资小于同职位平均工资的员工信息

 select ename,sal,m.avgsal,m.job from emp e,(select job,avg(sal) avgsal from emp group by job) m where e.job=m.job and e.sal<m.avgsal;

5. 查询高于自己部门平均工资的员工信息

select ename,sal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) w where e.deptno=w.deptno and e.sal>w.avgsal;
select ename,sal from emp e where sal>(select avg(sal) from emp   where deptno= e.deptno group by deptno);

6. 查询平均工资比20部门平均工资低的部门的员工信息

select * from emp where deptno=(select deptno from ( select deptno,avg(sal) asal from emp group by deptno) d where asal<(select avg(sal) from emp where deptno=20)); 
 select* from emp where deptno=(select deptno from emp group by deptno HAVING avg(sal)<(select avg(sal) from emp where deptno=20));
    

select * from emp;

1. 显示满10年服务年限的员工的姓名和雇佣日期

select ename,job,hiredate from emp where months_between(sysdate, hiredate)/12>=10;

2. 显示姓名字段的任何位置包含’A’的所有员工的姓名

  select ename from emp where instr(ename,'A')>0;
    select  instr(ename,'A') from emp;

3. 对于每个员工，显示其加入公司的天数

select ename,sysdate-hiredate 天数 from emp;

4. 找出在任意年份2月受聘的所有员工

select ename,hiredate from emp where to_char(hiredate,'mm')='02';

5. 显示所有员工的姓名，用a代替A

 select replace (ename,'A','a') from emp;

6. 显示所有员工姓名的前三个字符

  select substr(ename,1,3) name,job,hiredate from emp;

7. 以首字母大写的方式显示员工的姓名

  select initcap(ename) name,job,hiredate from emp;

8. 显示不带有’R’的员工姓名

   select ename from emp where ename not like '%R%';

9. 找出各月倒数第3天受雇的所有员工

   select ename,last_day(hiredate)-3 from emp;