oracle题目1

==================================================================
使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下

emp员工表 字段内容如下:

empno 员工号
ename 员工姓名
job 工作
mgr 上级编号
hiredate 受雇日期
sal 薪金
comm 佣金
deptno 部门编号


1.选择部门30中的所有员工.
select empno,ename,deptno from emp where deptno=30;
2.列出所有办事员(CLERK)的姓名，编号和部门编号.
select ename,empno,deptno from emp where job='CLERK';
3.找出佣金高于薪金的员工.
select ename,comm,sal from emp where comm>sal;
4.找出佣金高于薪金的60%的员工.
select ename,comm,sal,sal*0.6 from emp where comm>sal*0.6;
5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where deptno=10 and job='MANAGER'or deptno=20 and job='CLERK';
6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp where deptno=10 and job='MANAGER'or deptno=20 and job='CLERK' or job not in ('MANAGER','CLERK') and sal>=2000;
7.找出收取佣金的员工的不同工作.
select distinct(job) from emp where comm is not null;
8.找出不收取佣金或收取的佣金低于100的员工.
select ename,comm from emp where comm is null or comm<100;
9.找出各月倒数第3天受雇的所有员工.
select ename,hiredate from emp where hiredate=last_day(hiredate)-2;
10.找出早于12年前受雇的员工.('1984'-'1986'有一个先把字符串转换成数字的过程！所以结果是-2)
select ename,to_char(hiredate,'yyyy') from emp where to_number(to_char(hiredate,'yyyy'))<to_number(to_char(sysdate,'yyyy'))-12;
11.以首字母大写的方式显示所有员工的姓名.
select initcap(ename) from emp;
12.显示正好为5个字符的员工的姓名.
select ename from emp where length(ename)=5;
13.显示不带有"R"的员工的姓名.
select  ename from emp where  ename not like '%R%';
*select ename from emp where instr(ename,'R',1,1)=0;
14.显示所有员工姓名的前三个字符.
select substr(ename,1,3) from emp;

substr(字段,起始位置(第一位是1),截取长度 )

15.显示所有员工的姓名,用a替换所有"A"
replace(字段,原，后)
* select ename,replace(ename,'A','a') from emp;
16.显示满10年服务年限的员工的姓名和受雇日期.
select ename ,hiredate from emp where (sysdate-hiredate)/365 >=10;

*select ename,to_char(hiredate,'YYYY mm dd')  , to_char(sysdate,'YYYY mm dd')  from emp where (select to_char(sysdate,'YYYY') from dual) to_char(hiredate,'YYYY') >=10
17.显示员工的详细资料,按姓名排序.
select * from emp order by ename;
18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename,to_char(hiredate,'yyyy mm dd') from emp order by hiredate;
19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
* select ename,job,sal from emp order by job desc,sal; 
20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
*select ename,to_char(hiredate,'YYYY-mm') from emp order by to_char(hiredate,'mm'),to_char(hiredate,'YYYY') ;
21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
*select trunc(sal/30) from emp; 
22.找出在(任何年份的)2月受聘的所有员工。
*select ename,hiredate from emp where to_char(hiredate,'mm') ='02';  //效率高
select ename,hiredate ,to_char(hiredate,'mm') from emp  where to_char(hiredate,'mm')=2;
23.对于每个员工,显示其加入公司的天数.
*select ename,trunc(sysdate - hiredate) from emp;
24.显示姓名字段的任何位置包含"A"的所有员工的姓名.

        select ename from  emp where ename like '%A%';
25.以年月日的方式显示所有员工的服务年限. (大概)
select hiredate,sysdate,years,months,
       trunc(sysdate-add_months(hiredate,years*12+months)) days
   from (
         select hiredate,
                trunc(months_between(sysdate, hiredate)/12) years,
                 mod(trunc(months_between( sysdate, hiredate )),12) months
         from emp
        );

==================================================================

使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资 ＝ 薪金 ＋ 佣金

1．列出至少有一个员工的所有部门。
      select   dname  from  emp,dept where  emp.deptno=dept.deptno  group by dname;
      select   distinct  dname from  emp ,dept where  emp.deptno=dept.deptno;
     说明：      （1）如果   select 中出现的字段一定要出现在group by 中， 即  group   by  里出现的字段  一定要出现的在 select 中，
                 （2）如果 select 中出现的字段不出现在group by 中，则一定要出现在聚合函数中。
                 （3）默认的情况是按group by 中的字段来排序的，如果要用order  by  来自己排序，则order by 后的字段一定是 group  by  后                           的字段，不能是聚合函数  的字段
                 （4）对于想出现的字段，可用max（）或min（）；
2．列出薪金比“SMITH”多的所有员工。
      select  ename , sal  from    emp   where  sal> (select  sal  from  emp  where upper (ename)='SMITH')
       说明：
                 子查询一定要用"( )" 扩起来。 
3．列出所有员工的姓名及其直接上级的姓名。

select a.ename,
(select ename from emp where empno= a.mgr) mgrname from emp a;

自连接
select a.ename,b.ename mgrname from emp a,emp b
where a.mgr = b.empno;


4．列出受雇日期早于其直接上级的所有员工。
      select  e.ename ,m.ename from  emp   e,  emp   m
      where e.mgr=m.empno and ( e.hiredate< m.hiredate);

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
      select   dname   ,emp.*   from  dept , emp
      where  emp.deptno(+)=dept.deptno
      order by  dname;
      说明：
           外连接（使一个都不能少，（+）对面的表里的字段都不能少）       
    
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
      select  distinct  emp.ename,  dept.deptno  from   emp, dept
      where   emp.deptno=dept.deptno and upper(emp.job)='CLERK';


*7．列出最低薪金大于1500的各种工作。
     select   job   from   emp   group  by  job  having  min(sal)>1500;
 
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
     select  ename  from  emp,dept
     where  emp.deptno=dept.deptno and  dname='SALES';
9．列出薪金高于公司平均薪金的所有员工。
       select  ename ,sal  from emp
        where   sal>(select  avg(sal)  from emp );
10．列出与“SCOTT”从事相同工作的所有员工。

假定SCOTT从事一种工作:
select  ename  from  emp
where   job = ( select job  from  emp  where  ename='SCOTT');
假定SCOTT从事多种工作
        select  ename, job  from emp
        where  job   in( (select  job   from  emp where  upper(ename)='SCOTT')); 
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
        select  ename, sal  from emp
        where  sal   in ( select  sal  from  emp  where  deptno=30  );
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
         select  ename, sal  from emp
          where  sal  > ( select  max(sal)  from  emp  where  deptno=30  ); 
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
       select deptno "部门号" ,count(ename) "员工数量" ,avg(sal+nvl(comm,0))   "平均工资" ,trunc(avg(sysdate-hiredate)) "平均服务期限(天)"
       from emp
       group by deptno;
14．列出所有员工的姓名、部门名称和工资。
        select ename  姓名  ,dname  部门名称 ,sal+nvl(comm,0)  工资
        from emp,dept
        where emp.deptno=dept.deptno;
        from emp  e;
15．列出从事同一种工作但属于不同部门的员工的一种组合。
 
        select  distinct  e.deptno,e.job from emp e,emp m
        where e.deptno!=m.deptno and e.job=m.job
        order by  e.job;

16．列出所有部门的详细信息和部门人数。
        select a.*,(select count(*) from emp where deptno = a.deptno)   部门人数    from dept a;

17．列出各种工作的最低工资。
     select job ,min(sal+nvl(comm,0)) "最低工资"
     from emp
     group by job;
18．列出各个部门的MANAGER（经理）的最低薪金。
select min(sal+nvl(comm,0))
from emp
where job='MANAGER'
group by  deptno

//要求显示部门名称
         select dname,min(sal)
         from emp,dept
        where emp.deptno=dept.deptno and  upper(job)='MANAGER'
        group    by  dname

19．列出所有员工的年工资,按年薪从低到高排序。
       select ename,to_char((sal+nvl(comm,0))*12,'9999,9999.00') "年工资"
      from emp
      order by 2;


-----orcle的等连接
SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO;
-----orcla的外连接
SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;
+放在没有匹配行的表一侧，所以dept表的记录完全显示