sql语句

【以scott用户登录】



查询练习（一）



[color=green]--1.选择部门30中的雇员[/color]
select * from emp where deptno=30;



[color=green]--2.列出所有办事员的姓名、编号和部门[/color]
select empno, ename, deptno from emp;
select empno, ename, (select dname from dept d where d.deptno=e.deptno) as "DNAME" from emp e;



[color=green]--3.找出佣金高于薪金的雇员[/color]
select * from emp where comm>sal;



[color=green]--4.找出佣金高于薪金的60%的雇员[/color]
select * from emp where comm>sal*0.6;



[color=green]--5.找出部门10中所有经理和部门20中所有办事员的详细资料[/color]
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

[color=green]--6.找出部门10中所有经理、部门20中所有办事员以及既不是
-- 经理又不是办事员但其薪金大于或等于2000的所有雇员的详细资料[/color]select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (job not in('MANAGER','CLERK') and sal>=2000);



[color=green]--7.找出收取佣金的雇员的不同工作. [/color]
select distinct job from emp where nvl(comm,0)>0;



[color=green]--8.找出不收取佣金或收取的佣金低于100的雇员[/color]
--select * from emp where comm is null or (comm is not null and comm<100);--复杂写法
select * from emp where nvl(comm,0)<100;



[color=green]--9.找出各月最后一天受雇的所有雇员.[/color]
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;



[color=green]--10.找出早于12年之前受雇的雇员[/color]
select * from emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>12;



[color=green]--11.显示只有首字母大写的所有雇员的姓名[/color]
--select initcap('HeLlo') from dual;
select ename from emp where ename=initcap(ename);



--[color=green]12.显示正好为15个字符的雇员姓名.[/color]
select ename from emp where length(ename)=15;



[color=green]--13.显示不带有"R"的雇员姓名[/color]
select ename from emp where ename not like '%R%';



[color=green]--14.显示所有雇员的姓名的前三个字符.[/color]
select substr(ename,0,3) from emp;



[color=green]--15.显示所有雇员的姓名，用a替换所有"A" [/color]
select replace(ename,'A','a') from emp;



[color=green]--16.显示所有雇员的姓名以及满10年服务年限的日期[/color]

select ename, add_months(hiredate,120) from emp;



[color=green]--17.显示雇员的详细资料,按姓名排序[/color]
select * from emp order by ename asc;--默认按asc排序，可省略asc
select * from emp order by ename desc;



[color=green]--18.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面[/color]
select ename from emp order by hiredate;



[color=green]--19.显示雇员姓名，工作和薪金，按工作的降序顺序排序，而工作按薪金排序[/color]
select ename,job,sal from emp order by job desc, sal desc;



[color=green]--20.显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,并将最早年份的项目排在最前面[/color]
select ename, to_char(hiredate, 'yyyy-mm') as "加入年月" from emp order by to_char(hiredate,'mm') asc, hiredate asc;



[color=green]--21.显示在一个月为30天的情况下所有雇员的日薪金,忽略卢比余数[/color]
select round(sal/30,0) from emp;



[color=green]--22.找出在(任何年份的)2月受雇的所有雇员[/color]
select * from emp where to_char(hiredate, 'mm')=2;



[color=green]--23.对于每个雇员，显示其加入公司的天数[/color]
select ename,round(sysdate-hiredate,0) "加入公司天数" from emp;



[color=green]--24.显示姓名字段的任何位置包含"A"的所有雇员的姓名[/color]

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



[color=green]--25.以年、月和日显示所有雇员的服务年限.[/color]
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含义:
[color=green]1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。[/color]
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);





[color=green]--1、请从emp表中查询出第三条记录之后的所有记录？[/color]select * from (select rownum as num, empno, ename from emp) where num>3;

[color=green]--2、请从emp表中查询价格大于sal>1200的前五条记录（注：不需要对sal字段排序查询）[/color]
select * from (select * from emp where sal>1200) where rownum<=5;

[color=green]--3、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）[/color]
select * from (select * from emp where sal>1200 order by sal desc) where rownum<=5;

[color=green]--4、请从emp表中查询价格大于sal>1200的后五条记录（注：不需要对sal字段排序查询）[/color]
select * from
(select rownum as num, empno, ename, sal from emp where sal>1200)
where num>(select count(*) from emp where sal>1200)-5;

[color=green]--5、请从emp表中查询价格大于sal>1200的前五条记录（注：需要对sal字段排序查询后再查询前五条）[/color]
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;

[color=green]--6、要找出一个emp表中按sal从大到小排序的前10条纪录,假如有2条纪录的数量相同,那么就显示11条纪录.[/color]
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;

[color=green]--7、从emp表中查询第八条到第十条记录(请用两种以上的方法来作)
-- 第一解题思路：[/color]
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;





查询练习（三）


[color=green]--1.列出至少有一个雇员的所有部门[/color]
select dname from dept where deptno in (select distinct deptno from emp);

[color=green]--2.列出薪金比'SMITH'多的所有雇员.[/color]
select * from emp where sal>(select sal from emp where ename='SMITH');
[color=green]
--3.列出所有雇员的姓名及其上级的姓名.[/color]
select ename, (select ename from emp e2 where e2.empno=e1.mgr) MGR from emp e1;

[color=green]--4.列出入职日期早于其直接上级的所有雇员.[/color]
select * from emp e1 where hiredate<(select hiredate from emp e2 where e2.empno=e1.mgr);

[color=green]--5.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门[/color]
select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;

[color=green]--6.列出所有'CLERK'的姓名及其部门名称.[/color]
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';

[color=green]--7.列出各种类别的最低薪金，要求最低薪金大于1500[/color]select job,min(sal) from emp group by job having min(sal)>1500;

[color=green]--8.列出各种类别"SALES"工作的雇员的姓名，假定不知道销售部的部门编号.[/color]
select ename from emp where emp.deptno=(select deptno from dept where dname='SALES');

[color=green]--9.列出薪金高于公司平均水平的所有雇员.[/color]
select * from emp where sal>(select avg(sal) from emp);
[color=green]
--10.列出与"SCOTT"从事相同工作的所有雇员.[/color]
select * from emp where job=(select job from emp where ename='SCOTT');
[color=green]--11.列出薪金等于在部门30工作的雇员的薪金的雇员的所有姓名和薪金);[/color]

select ename, sal from emp where sal in (select sal from emp where deptno=30);
[color=green]--12.列出薪金高于在部门30工作的所有雇员的薪金的雇员的姓名和薪金.[/color]
select ename, sal from emp where sal>(select max(sal) from emp where deptno=30);

[color=green]--13.列出在每个部门工作的雇员的数量以及其他信息.[/color]

select d.*, (select count(deptno) from emp e where e.deptno=d.deptno) "雇员人数" from dept d;

[color=green]--14.列出所有雇员的雇员名称、部门名称和薪金.[/color]select ename,(
select dname from dept d where d.deptno=e.deptno) "部门名称",sal from emp e;

[color=green]--15.列出从事同一种工作但属于不同部门的雇员的不同组合.[/color]
select * from emp order by job, deptno;

[color=green]--16.列出分配有雇员的所有部门及其员工的详细信息,即使是分配0个雇员的部门信息也要列出来.[/color]

select d.*, e.* from dept d left join emp e on d.deptno=e.deptno;

[color=green]--17.列出各种类别工作的最低工资.[/color]
select job, min(sal) from emp group by job;

[color=green]--18.列出所有部门MANAGER的最低薪金.[/color]
select min(sal) from emp where job='MANAGER';

[color=green]--19.列出按计算的字段排序的所有雇员的年薪.[/color]
select sal*12 from emp order by sal;

[color=green]--20.列出薪金水平处于第四位的雇员.[/color]
select * from (select rownum num, e.* from (select * from emp order by sal desc) e) where num=4;