sql话语练习（1）

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 wheremonths_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 wheremonths_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 byto_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)ASY,MOD(floor(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12)AS M,MOD(floor(SYSDATE-HIREDATE),30)ASD from scott.emp;

selectename,hiredate,trunc(months_between(sysdate,hiredate)/12) 年,trunc(mod(months_between(sysdate,hiredate),12)) 月,trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))日 fromemp;

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

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

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

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

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

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

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

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

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

连接查询：

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

关联子查询：

select e.* from emp e where e.hiredate<(select hiredatefrom 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) fromscott.emp);

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

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

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

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

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

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

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

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

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

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

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

select distinct e.ename,e.job,e.deptno from scott.emp e,scott.emp dwhere 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' groupby 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 empe 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 whererownum<=10) t where t.r>=5;