Oracle学习记录之表查询

【以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;

 

 

未完待续...