oracle select 练习

--1、列出至少有一个雇员的所有部门
　　select deptno from emp group by deptno having count(deptno)>1
select e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname having count(e.deptno)>0

　　--2、列出薪金比"SMITH"多的所有雇员
SELECT ename,sal, deptno FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH')
　
　　--3、列出所有雇员的姓名及其直接上级的姓名
SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno)
　　SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno) group by m.ename,e.ename

　　--4、列出入职日期早于其直接上级的所有雇员
SELECT e.ename as 职工, m.ename as 经理 FROM emp e left join emp m on (e.mgr=m.empno) where e.hiredate<m.hiredate
　
　　--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select d.dname,e.ename from dept d left join emp e on (d.deptno=e.deptno)
　　

　　--6、列出所有“CLERK”（办事员）的姓名及其部门名称
select e.ename, d.dname from emp e,dept d where e.deptno=d.deptno and e.job='CLERK'

　　--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
select job, min(sal) from emp group by job having min(sal)>1500

　　--8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
select ename, from emp where dept=(select deptno from dept where dname='SALES')
SELECT ename FROM emp where job='SALESMAN'　
　　--9、列出薪金高于公司平均水平的所有雇员

　　select ename, deptno from emp where sal>(select avg(sal) from emp )

　　--10、列出与“SCOTT”从事相同工作的所有雇员

　　select deptno,ename from emp where job=(select job from emp where ename='SCOTT')

　　--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
SELECT ename,sal from emp where sal in(select sal from emp where deptno=30)
SELECT ename,sal from emp where sal in(select sal from emp where deptno=30) and deptno<>30

　　--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
SELECT ename,sal from emp where sal >all(select sal from emp where deptno=30)

SELECT ename,sal from emp where sal >all(select sal from emp where deptno=30) and deptno<>30　　
　　--13、列出每个部门的信息以及该部门中雇员的数量

　　select d.deptno,d.dname,d.loc ,count(e.deptno)from dept d left join
emp e on(d.deptno=e.deptno) group by d.deptno ,d.dname,d.loc
order by d.deptno asc

　　--14、列出所有雇员的雇员名称、部门名称和薪金

　　select e.ename,d.dname,e.sal from emp e left join dept d on (e.deptno=d.deptno)
select ename,dname,sal from emp natural join dept

　　--15、列出从事同一种工作但属于不同部门的雇员的不同组合

　　select job,deptno from emp group by job,deptno

　　--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员
select d.deptno,d.dname,d.loc ,count(e.deptno) from dept d left join emp e on (d.deptno=e.deptno) group by d.deptno,d.dname,d.loc
having ( select count(e.deptno) from emp e )>=0
　　

　　--17、列出各种类别工作的最低工资
select job,min(sal) from emp group by job
　　

　　--18、列出各个部门的MANAGER（经理）的最低薪金

select job,min(sal) from emp where job='' group by job　
　　--19、列出按年薪排序的所有雇员的年薪

select ename,(sal+nvl(comm,0))*12 as yearpay from emp order by yearpay desc 　

　　--20、列出薪金水平处于第四位的雇员
select sal,ename from emp order by sal desc
　
　--1、选择部门30中的雇员
select ename,sal from emp where deptno=30 order by sal desc   
　

　　--2、列出所有办事员的姓名、编号和部门
select e.ename,e.empno ,d.dname ,e.job from emp e left join dept d on (e.deptno=d.deptno ) group by d.dname, e.ename,e.empno ,e.job having e.job='CLERK'

　　--3、找出佣金高于薪金的雇员

　select * from emp where comm>sal

　　--4、找出佣金高于薪金60%的雇员

select * from emp where (1+0.6)*comm>sal　　

　　--5、找出部门10中所有经理和部门20中的所有办事员的详细资料

select e.ename,e.empno ,d.dname ,e.job from emp e left join dept d on (e.deptno=d.deptno ) where e.deptno in(10,20)
group by d.dname, e.ename,e.empno ,e.job
having e.job='CLERK'
　　
　　--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料


　　--7、找出收取佣金的雇员的不同工作
select deptno,ename, comm from emp group by deptno,ename,comm having comm is not null
　　
　　--8、找出不收取佣金或收取的佣金低于100的雇员

　　select deptno,ename, comm from emp group by deptno,ename,comm having (comm is not null and comm<=100) or comm is null

　　--9、找出各月最后一天受雇的所有雇员

　select deptno,ename from emp where hiredate =last_day(hiredate)

　　--10、找出早于25年之前受雇的雇员
select deptno,ename,sal from emp where round((sysdate-hiredate)/365)>25
　　--11、显示只有首字母大写的所有雇员的姓名

select deptno,ename from emp where substr(ename,1,1)=Upper(substr(ename,1,1))select deptno,ename from emp where length(ename)=6　　

　　--12、显示正好为6个字符的雇员姓名

select deptno,ename from emp where length(ename)=6   　

　　--13、显示不带有'R'的雇员姓名
select ename from emp where ename not like '%R%'

　
　　--14、显示所有雇员的姓名的前三个字符

select substr(ename,1,3) from emp　

　　--15、显示所有雇员的姓名，用a替换所有'A'

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

　　--16、显示所有雇员的姓名以及满10年服务年限的日期

　select ename,hiredate,add_months(hiredate,10*12) from emp

　　--17、显示雇员的详细资料，按姓名排序
select * from emp order by ename asc
　　

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

select empno,ename,job,mgr,hiredate,sal ,comm,deptno ,(sysdate-hiredate)/365 from emp order by (sysdate-hiredate)/365 desc　

　　--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select ename,job,sal from emp order by job desc,sal asc

　　--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename,to_char(hiredate,'YYYY') year1,to_char(hiredate,'MON') month1 from emp order by
year1 asc,month1

　　--21、显示在一个月为30天的情况下所有雇员的日薪金

　　select round(sal/30,2) as daypay from emp

　　--22、找出在（任何年份的）2月受聘的所有雇员

　select * from emp where hiredate like '%-2月%'

　　--23、对于每个雇员，显示其加入公司的天数

　select round((sysdate-hiredate)) from emp

　　--24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名

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

　　--25、以年、月和日显示所有雇员的服务年限
select ename,to_number(to_char(sysdate,'YYYY'))-to_number(to_char(hiredate,'YYYY'))-1,to_number(to_char(sysdate,'mm'))-to_number(to_char(hiredate,'mm')) +11,to_number(to_char(sysdate,'dd'))-to_number(to_char(hiredate,'dd'))+30 from emp