Oracle学习记录之表查询

jna1bve3l4

　　【以scott用户登录】
　　
　　查询练习（一）
　　
　　--1.选择部门30中的雇员
select * from emp where deptno=30;
　　
　　--2.列出所有办事员的姓名、编号和部门
select empno, ename, deptno from emp;
select empno, ename, (select dname from dept d where d.deptno=e.deptno) as "DNAME" from emp e;
　　
　　--3.找出佣金高于薪金的雇员
select * from emp where comm>sal;
　　
　　--4.找出佣金高于薪金的60%的雇员
select * from emp where comm>sal*0.6;
　　
　　--5.找出部门10中所有经理和部门20中所有办事员的详细资料
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
 
--6.找出部门10中所有经理、部门20中所有办事员以及既不是
--  经理又不是办事员但其薪金大于或等于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 nvl(comm,0)>0;
　　
　　--8.找出不收取佣金或收取的佣金低于100的雇员
--select * from emp where comm is null or (comm is not null and comm<100);--复杂写法
select * from emp where nvl(comm,0)<100;
　　
　　--9.找出各月最后一天受雇的所有雇员.
select * from emp where hiredate=last_day(hiredate);
--select sysdate from dual;
--select last_day(sysdate) from dual;
--select last_day(to_date('2009-02-02','yyyy-mm-dd')) from dual;
--select to_char(sysdate,'yyyy') from dual;
--select to_char(sysdate,'yy') from dual;
--select to_number('300') from dual;
　　
　　--10.找出早于12年之前受雇的雇员
select * from emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>12;
　　
　　--11.显示只有首字母大写的所有雇员的姓名
--select initcap('HeLlo') from dual;
select ename from emp where ename=initcap(ename);
　　
　　--12.显示正好为15个字符的雇员姓名.
select ename from emp where length(ename)=15;
　　
　　--13.显示不带有"R"的雇员姓名
select ename from emp where ename not like '%R%';
　　
　　--14.显示所有雇员的姓名的前三个字符.
select substr(ename,0,3) from emp;
　　
　　--15.显示所有雇员的姓名，用a替换所有"A" 
select replace(ename,'A','a') from emp;
　　
　　--16.显示所有雇员的姓名以及满10年服务年限的日期
select ename, add_months(hiredate,120) from emp;
　　
　　--17.显示雇员的详细资料,按姓名排序
select * from emp order by ename asc;--默认按asc排序，可省略asc
select * from emp order by ename desc;
　　
　　--18.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename from emp order by hiredate;
　　
　　--19.显示雇员姓名，工作和薪金，按工作的降序顺序排序，而工作按薪金排序
select ename,job,sal from emp order by job desc, sal desc;
　　
　　--20.显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,并将最早年份的项目排在最前面
select ename, to_char(hiredate, 'yyyy-mm') as "加入年月" from emp order by to_char(hiredate,'mm') asc, hiredate asc;
　　
　　--21.显示在一个月为30天的情况下所有雇员的日薪金,忽略卢比余数
select round(sal/30,0) from emp;
　　
　　--22.找出在(任何年份的)2月受雇的所有雇员
select * from emp where to_char(hiredate, 'mm')=2;
　　
　　--23.对于每个雇员，显示其加入公司的天数
select ename,round(sysdate-hiredate,0) "加入公司天数" from emp;
　　
　　--24.显示姓名字段的任何位置包含"A"的所有雇员的姓名
select ename from emp where ename like '%A%';
　　
　　--25.以年、月和日显示所有雇员的服务年限. 
select empno,
floor(months_between(sysdate,hiredate)/12) As Year,
floor((months_between(sysdate,hiredate)/12-floor(months_between(sysdate,hiredate)/12))*12) As Month,
ceil((((months_between(sysdate,hiredate)/12-floor(months_between(sysdate,hiredate)/12))*12)-floor((months_between(sysdate,hiredate)/12-floor(months_between(sysdate,hiredate)/12))*12))*30) As Day
from emp order by hiredate;
　　
　　
　　查询练习（二）
　　
　　rownum含义:
1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。
　　select rownum, empno, ename, sal from emp;
select rownum, empno, ename, sal from emp order by sal;
select rownum, empno, ename, sal from (select * from emp order by sal);
　　
　　
　　--1、请从emp表中查询出第三条记录之后的所有记录？
select * from (select rownum as num, empno, ename from emp) where num>3;
　　--2、请从emp表中查询价格大于sal>1200的前五条记录（注：不需要对sal字段排序查询）
select * from (select * from emp where sal>1200) where rownum<=5;
　　--3、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）
select * from (select * from emp where sal>1200 order by sal desc) where rownum<=5;
 
--4、请从emp表中查询价格大于sal>1200的后五条记录（注：不需要对sal字段排序查询）
select * from
       (select rownum as num, empno, ename, sal from emp where sal>1200)
               where num>(select count(*) from emp where sal>1200)-5;
　　--5、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）
select * from
       (select rownum as num, empno, ename, sal from (select * from emp where sal>1200 order by sal))
               where num>(select count(*) from emp where sal>1200)-5;
　　--6、要找出一个emp表中按sal从大到小排序的前10条纪录,假如有2条纪录的数量相同,那么就显示11条纪录.
select * from emp order by sal desc;--10和11两条记录的sal大小相同
select * from emp
       where sal>=
             (select sal from
                     (select * from (select rownum as num, empno, ename, sal from (select * from emp order by sal desc))) e
             where e.num=10)
       order by sal desc;
　　--7、从emp表中查询第八条到第十条记录(请用两种以上的方法来作)
-- 第一解题思路：
select * from emp;
select * from (select rownum as num, empno, ename from emp) where num>=8 and num<=10;
-- 第二解题思路：
select * from (select rownum as num, empno, ename from emp) where num between 8 and 10;
-- 第三解题思路：
select * from (select rownum num, empno, ename from emp where rownum<=10) where num>=8;
-- 第四解题思路：
select * from emp where rownum<=10 minus select * from emp where rownum<8;
　　
　　
　　查询练习（三）
　　
　　--1.列出至少有一个雇员的所有部门
select dname from dept where deptno in (select distinct deptno from emp);
　　--2.列出薪金比'SMITH'多的所有雇员.
select * from emp where sal>(select sal from emp where ename='SMITH');
　　--3.列出所有雇员的姓名及其上级的姓名.
select ename, (select ename from emp e2 where e2.empno=e1.mgr) MGR from emp e1;
　　--4.列出入职日期早于其直接上级的所有雇员.
select * from emp e1 where hiredate<(select hiredate from emp e2 where e2.empno=e1.mgr);
　　--5.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;
　　--6.列出所有'CLERK'的姓名及其部门名称.
select ename, (select dname from dept d where d.deptno=e.deptno) "部门" from emp e where e.job='CLERK';
select ename,dname from dept a,emp b where a.deptno=b.deptno and job='CLERK';
　　--7.列出各种类别的最低薪金，要求最低薪金大于1500
select job,min(sal) from emp group by job having min(sal)>1500;
　　--8.列出各种类别"SALES"工作的雇员的姓名，假定不知道销售部的部门编号.
select ename from emp where emp.deptno=(select deptno from dept where dname='SALES');
　　--9.列出薪金高于公司平均水平的所有雇员.
select * from emp where sal>(select avg(sal) from emp);
　　--10.列出与"SCOTT"从事相同工作的所有雇员.
select * 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);
　　--12.列出薪金高于在部门30工作的所有雇员的薪金的雇员的姓名和薪金.
select ename, sal from emp where sal>(select max(sal) from emp where deptno=30);
　　--13.列出在每个部门工作的雇员的数量以及其他信息.
select d.*, (select count(deptno) from emp e where e.deptno=d.deptno) "雇员人数" from dept d;
　　--14.列出所有雇员的雇员名称、部门名称和薪金.
select ename,(select dname from dept d where d.deptno=e.deptno) "部门名称",sal from emp e;
　　--15.列出从事同一种工作但属于不同部门的雇员的不同组合.
select * from emp order by job, deptno;
　　--16.列出分配有雇员的所有部门及其员工的详细信息,即使是分配0个雇员的部门信息也要列出来.
select d.*, e.* from dept d left join emp e on d.deptno=e.deptno;
　　--17.列出各种类别工作的最低工资.
select job, min(sal) from emp group by job;
　　--18.列出所有部门MANAGER的最低薪金.
select min(sal) from emp where job='MANAGER';
　　--19.列出按计算的字段排序的所有雇员的年薪.
select sal*12 from emp order by sal;
　　--20.列出薪金水平处于第四位的雇员.
select * from (select rownum num, e.* from (select * from emp order by sal desc) e) where num=4;　　

　　
　　未完待续...