sql语句练习（1）

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

1. 找出各月最后一天受雇的所有雇员

select * from scott.emp where HIREDATE in last_day(HIREDATE);

2. 找出早于 12 年之前受雇的雇员

select * from scott.emp where months_between(sysdate,hiredate)>12*12;

3. 显示只有首字母大写的所有雇员的姓名

select ename from emp1 where ename = initcap(ename);

4. 显示正好为 15 个字符的雇员姓名

select substr(ENAME,0,15) from scott.emp;

5. 显示不带有 ’R’ 的雇员姓名

select ENAME from scott.emp where ENAME not like’%R%’;

6. 显示所有雇员的姓名的前 3 个字符

select substr(ENAME,0,3) from scott.emp;

7. 显示所有雇员的姓名，用 ’a’ 替换所有的 ’A’

select replace(ENAME,’A’,’a’) from scott.emp;

8. 显示所有雇员的姓名以及满 10 年服务年限的日期

select ENAEM ,HIREDATE from scott.emp where months_between(sysdate,HIREDATE)>120;

select emp.*, add_months(hiredate,120)  from emp;

9. 显示雇员的详细资料，按姓名排序

select * from scott.emp order by ENAME ;

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

select ENAME,HIREDATE from scott.emp order by HIREDATE asc;

11. 显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，而工作按薪金排序

select ENAME,JOB,SAL from scott.emp order by JOB desc,sal;

12. 显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在最前面

select ENAME,to_date(HIREDATE,’yy-mm’) from scott.emp order by to_date(HIREDATE,’mm’) , to_date(HIREDATE,’yy’);

13. 显示在一个月为 30 天的情况下所有雇员的日薪金，忽略小数

select ENAME,round(sal/30,0) from scott.emp;

14. 找出在（任何年份的） 2 月份受雇的所有雇员

select * from scott.emp where to_char(HIREDATE,’mm’) =2;

15. 对于每个雇员，显示其加入公司的天数

select ceil(SYSDATE-HIREDATE) as DY from scott.emp;

16. 显示姓名字段的任何位置，包含‘ A ’的所有雇员的姓名

select ENAME from scott.emp where ENAME like’%A%’;

17. 以年、月和日显示所有雇员的服务年限

select ENAME,HIREDATE ,floor(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)AS Y,MOD(floor(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12)AS M,MOD(floor(SYSDATE-HIREDATE),30)AS D from scott.emp;

select ename,hiredate,trunc(months_between(sysdate,hiredate)/12) 年 , trunc(mod(months_between(sysdate,hiredate),12)) 月 , trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) 日 from emp;

18. 列出至少有一个雇员的所有部门

select dname from scott.dept where deptno in(select distinct deptno from scott.emp);

19. 列出薪金比“ SMITH ”多的所有雇员

select ENAME,SAL from scott.emp where SAL>(select SAL from scott.emp where ENAME =’SMITH’);

select e2.* from scott.emp e1,scott.emp e2 where e1.ename='SMITH'and e2.sal > e1.sal;

20. 列出所有雇员的姓名及其部门名

select ENAME, scott.emp.deptno,DNAME FROM scott.emp INNER JOIN scott.dept ON scott.emp.deptno = scott.dept.deptno order by deptno;

21. 列出所有入职日期早于其直接上级的所有雇员

SELECT * FROM scott.emp ygb WHERE hiredate<(SELECT hiredate FROM scott.emp sjb WHERE sjb.empno=ygb.mgr);

连接查询：

select a.* from emp1 a,emp1 b where a.hiredate <b.hiredate and a.mgr = b.empno;

关联子查询：

select e.* from emp e where e.hiredate<(select hiredate from emp e2 where e2.empno=e.mgr);

22. 列出各种类别的最低薪金，并使薪金大于 1500

select job,min(sal) from scott.emp where sal > 1500 group by job;

23. 列出薪金高于公司平均水平的所有雇员

select ename from scott.emp where sal>(select avg(sal) from scott.emp);

24. 列出与“ scott ”从事相同工作的所有雇员

select * from scott.emp where job=(select job from scott.emp where ename='SCOTT');

25. 列出薪金高于部门 30 工作的所有雇员的薪金

select * from scott.emp where sal > any (select sal from scott.emp where deptno = 30);

26. 列出薪金高于在部门 30 工作的最高的薪金

select * from scott.emp where sal>all(select sal from scott.emp where deptno=30);

27. 列出每个部门雇员的数量

select d.deptno,dname,count(ename) from scott.dept d left join scott.emp e on (d.deptno = e.deptno) group by d.deptno,dname;

28. 列出所有雇员的名称，部门名称和薪金

select ename,dname,sal from scott.emp,scott.dept where emp.deptno=dept.deptno;

29. 列出从事同一种工作但不属于同一部门的这些员工

select distinct e.ename,e.job,e.deptno from scott.emp e,scott.emp d where e.deptno != d.deptno and e.job = d.job order by e.job;

30. 列出个类别工作的最低工资

select min(sal) ,deptno from scott.emp group by deptno;

31. 列出各个部门的经理的最低薪金

select ename,deptno,min(sal) from scott.emp where job = 'MANAGER' group by deptno,ename;

32. 列出按计算的字段排序的所有雇员的年薪

select ename,(sal+nvl(comm,0))*12 nx from scott.emp order by nx;

33. 列出所有 CLERK 的姓名及其部门名称

select dname,ename from scott.dept a,scott.emp d where a.deptno = d.deptno and job ='CLERK';

34. 列出薪金水平处于前四位的雇员

select * from (select * from scott.emp order by sal desc) where rownum<5 ;

35 、取出收入在第 5-10 名之间的员工

select * from (select t.*,rownum r from (select e.* from emp e order by sal desc) t where rownum<= 10) t2 where t2.r>=5;

36 、取出表 emp 中第 5-10 位之间的员工

select * from (select e.*,rownum r from emp e where rownum<=10) t where t.r>=5;