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表的记录完全显示