Oracle练习题总结

 

1：找出各月最后一天受雇的所有雇员

 select * from emp wherehiredate=last_day(hiredate)

 

2：找出早于36年之前受雇的雇员

   select * from emp whereadd_months(hiredate,12*36)<sysdate

 

3：显示只有首字母大写的所有雇员的姓名

 select * from emp where ename=initcap(ename);

 

4：显示正好为5个字符的雇员姓名

 select * from emp where ename like '_____';

 

5：显示不带有‘R’的雇员姓名

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

 

6：显示所有雇员的姓名的前3个字符

 select ename,substr(ename,1,3) from emp;

 

7：显示所有雇员的姓名，用‘a’替换所有的‘A’

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

 

 

 

8：显示所有雇员的姓名以及满36年服务年限的日期

selectename,hiredate from emp where add_months(hiredate,12*36)<sysdate

 

9：显示雇员的详细资料，按姓名降序排序

 select * from emp order by ename desc ;

 

10：显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

selectename,hiredate from emp order by hiredate asc;

 

 

11：显示所有雇员的姓名、工作和薪金，按工作降序排序，而工作相同的按薪金升序排序

selectename,job,sal from emp order by job desc,sal asc;

 

12：显示所有雇员的姓名和加入公司的年份和月份

   select ename,to_char(hiredate,'yyyyMM') fromemp;

 

 

 

13：显示在一个月为30天的情况下所有雇员的日薪金，忽略小数

selectename,trunc(sal/30) from emp;

select ename,floor(sal/30)from emp;

 

14：找出在（任何年份的）2月份受雇的所有雇员

selectename,hiredate from emp where to_char(hiredate,'MM')=2

selectename,hiredate from emp where to_char(hiredate,'MM')='02'

 

 

15：对于每个雇员，显示其加入公司的天数

 

selectename,hiredate,trunc(sysdate-hiredate) 入职天数 from emp;

 

16：显示姓名字段的任何位置，包含‘A’的所有雇员的姓名

 

 select * from emp where ename like '%A%';

 

17：以年、月和日显示所有雇员的服务年限

 select ename,

       hiredate,

       trunc(MONTHS_BETWEEN(sysdate,hiredate)/12) 年,

       trunc(MONTHS_BETWEEN(sysdate, hiredate))月,

       trunc(sysdate - hiredate) 日

  from emp;

 

 

连接查询

18: 列出至少有一个雇员的所有部门

       select count(*),deptno from emp group bydeptno having count(*)>=1;

    列出一个员工都没有的部门

      select * from dept where deptno!=all(selectdeptno from emp group by deptno);

 

19：列出薪金比“SMITH”多的所有雇员

      select * from emp where  sal>(select sal from emp whereename='SMITH');

 

22：列出各种工作的最低薪金,并且显示最低薪金大于1500的工作

      select job,min(sal) from emp group by jobhaving min(sal)>1500

 

 

   

23：列出薪金高于公司平均水平的所有雇员

      select * from emp where sal>(selectavg(sal) from emp)

    

24：列出与“SCOTT”从事相同工作的所有雇员  , 并且与scott是同一个部门的

      select * from emp where(job,deptno)=(select job,deptno from emp where ename='SMITH') and  ename!='SMITH';

 

 

25:  列出薪金高于在部门30的所有雇员的薪金

      select * from emp where sal>all(selectsal from emp where deptno=30)

      select * from emp where sal>(selectmax(sal) from emp where deptno=30)

 

27.列出每个部门雇员的数量

      select count(*),deptno from emp group bydeptno

 

30：列出各类别工作的最低工资

      select min(sal),job from emp group by job

 

31：列出各个部门的经理的最低薪金

        select deptno,min(sal) from emp WHEREjob='MANAGER'  group by deptno

 

32：列出按计算列排序的所有雇员的年薪

      select ename,12*(sal+nvl(comm,0)) 年薪 from emporder by 年薪  desc

 

20：列出所有雇员的姓名及其部门名

        select ename,dname from emp e,dept dwhere e.deptno=d.deptno

 

 

21：列出所有入职日期早于其直接上级的所有雇员

      selecte.ename,e.hiredate,s.ename,s.hiredate from emp e,emp s where e.mgr=s.empno ande.hiredate<s.hiredate

 

 

 

28:列出所有雇员的名称，部门名称和薪金

       select ename,dname,sal from emp e,dept d where e.deptno=d.deptno

 

 

29：列出从事同一种工作但不属于同一部门的这些员工

      select e1.ename,e1.job,e1.deptno,e2.ename,e2.job,e2.deptno  from emp e1,emp e2

           where e1.job=e2.job ande1.deptno!=e2.deptno

 

33:列出所有CLERK的姓名及其部门名称

        select ename,dname from emp e,dept dwhere e.deptno=d.deptno and e.job='CLERK'

 

 

34：列出薪金水平处于前四位的雇员

     selectt1.* from (select t.*,rownum rn from (select * from emp order by sal desc) t)t1

     wherern>=1 and rn<=4

 

35：查询出每个部门薪水最高的人的姓名、薪水、部门编号，并且按照部门编号升序排列

       select e.ename,e.sal,e.deptno from emp e,

     (selectmax(sal) ms,deptno from emp group by deptno) t

       where e.sal=t.ms and e.deptno=t.deptno

       

求平均薪水最高的部门的部门编号

select avg(sal),deptno

  from emp

 group by deptno

having avg(sal) =(select max(t.asal)

                     from (select avg(sal)asal, deptno

                             from emp

                            group by deptno) t)

 

求平均薪水最高的部门的部门名字?

select dname

  from dept

 where deptno = (select deptno

                   from emp

                  group by deptno

                 having avg(sal) = (selectmax(t.asal)

                                     from (select avg(sal) asal,deptno

                                            from emp

                                           group by deptno) t))

 

 

求比普通员工的最高薪水还要高的经理人名称

SELECT ename,salfrom emp where sal>

(select max(sal)from emp where job='CLERK')

and job='MANAGER'

 

                                                                            

求部门平均薪水的等级

select t.deptno,t.asal, s.grade

  from (select avg(sal) asal, deptno from empgroup by deptno) t,

       salgrade s

 where t.asal between s.losal and s.hisal